nooby q: how get a row just inserted?
I am probably breaking the rules here which is why I have a problem, but
here goes: I am trying to build an audit trail skeleton of all my table
inserts. Everything table has a column for the serial ID of an audit
trail table row I will create for each transaction or batch of
transactions if I like. My problem is that the audit trail table as I
conceive it does not have a natural primary key. I do have serial id and
timestamp columns supplied by PG, but being supplied by PG I need to
read back the row to get at their values.
If I were using OIDs on the table I realize the insert returns the oid
created, but (so far) I am not.
Am I going to have to use oids or fake a distinguishing column I can use
to read back an audit trail row just after inserting it?
I was hoping there was some select magic that would let me insert a row
within a select which extracted the PG-allocated serial id column, but I
do not see anything like that.
kt
On 05/30/2009 07:02 PM, Kenneth Tilton wrote:
I am probably breaking the rules here which is why I have a problem, but
here goes: I am trying to build an audit trail skeleton of all my table
inserts. Everything table has a column for the serial ID of an audit
trail table row I will create for each transaction or batch of
transactions if I like. My problem is that the audit trail table as I
conceive it does not have a natural primary key. I do have serial id and
timestamp columns supplied by PG, but being supplied by PG I need to
read back the row to get at their values.If I were using OIDs on the table I realize the insert returns the oid
created, but (so far) I am not.Am I going to have to use oids or fake a distinguishing column I can use
to read back an audit trail row just after inserting it?I was hoping there was some select magic that would let me insert a row
within a select which extracted the PG-allocated serial id column, but I
do not see anything like that.kt
http://www.postgresql.org/docs/8.3/interactive/sql-insert.html
Check RETURNING
yes i would suggest using OID
included in Postgres distro is a sample create table,index named
fti.pl
does this answer your question?
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.
Date: Sat, 30 May 2009 18:02:26 -0400
From: kentilton@gmail.com
Subject: [GENERAL] nooby q: how get a row just inserted?
To: pgsql-general@postgresql.orgI am probably breaking the rules here which is why I have a problem, but
here goes: I am trying to build an audit trail skeleton of all my table
inserts. Everything table has a column for the serial ID of an audit
trail table row I will create for each transaction or batch of
transactions if I like. My problem is that the audit trail table as I
conceive it does not have a natural primary key. I do have serial id and
timestamp columns supplied by PG, but being supplied by PG I need to
read back the row to get at their values.If I were using OIDs on the table I realize the insert returns the oid
created, but (so far) I am not.Am I going to have to use oids or fake a distinguishing column I can use
to read back an audit trail row just after inserting it?I was hoping there was some select magic that would let me insert a row
within a select which extracted the PG-allocated serial id column, but I
do not see anything like that.kt
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
_________________________________________________________________
Hotmail® goes with you.
http://windowslive.com/Tutorial/Hotmail/Mobile?ocid=TXT_TAGLM_WL_HM_Tutorial_Mobile1_052009
Martin Gainty wrote:
yes i would suggest using OID
included in Postgres distro is a sample create table,index named
fti.pldoes this answer your question?
oid would have been fine, but I am going with Rodrigo's suggestion to
simply use the returning option on insert which I somehow missed.
thx, ken
On Sat, May 30, 2009 at 6:52 PM, Kenneth Tilton <kentilton@gmail.com> wrote:
Martin Gainty wrote:
yes i would suggest using OID
included in Postgres distro is a sample create table,index named
fti.pldoes this answer your question?
oid would have been fine, but I am going with Rodrigo's suggestion to simply
use the returning option on insert which I somehow missed.
One of the cool features of returning is that it can return sets.
insert into table values ('abc'),('def'),('ghi') returning id;
1
2
3