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

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