Postgres Pro
Downloads
Home   >   mailing lists

Thread: nooby q: how get a row just inserted?

nooby q: how get a row just inserted?

From
Kenneth Tilton
Date:
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

Re: nooby q: how get a row just inserted?

From
Rodrigo Gonzalez
Date:
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

Re: nooby q: how get a row just inserted?

From
Martin Gainty
Date:
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.org
>
> 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
>
>
> --
> 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. Get it on your BlackBerry or iPhone.

Re: nooby q: how get a row just inserted?

From
Kenneth Tilton
Date:

Martin Gainty wrote:
> yes i would suggest using OID
> included in Postgres distro is a sample create table,index named
> fti.pl
>
> does 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

Re: nooby q: how get a row just inserted?

From
Scott Marlowe
Date:
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.pl
>>
>> does 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