Re: MySQL LAST_INSERT_ID() to Postgres

[Bill <pg@xxxxxxxxxx>]

Scott Marlowe wrote:

On Thu, Aug 28, 2008 at 3:38 PM, Bill <pg@xxxxxxxxxx> wrote:
  

I am new to PostgreSQL but it seems to me that lastval() will only work if
the insert does not produce side effects that call nextval(). Consider the
case where a row is inserted into a table that has an after insert trigger
and the after insert trigger inserts a row into another table which has a
serial primary key. In that case I assume that lastval() will  return the
value from the serial column in the second table.
    

No, setval, currval, and lastval all require as an argument a sequence
name.  So the real issue is you have to know the sequence name to use
them.

The problem with lastval is that it reports the last value that the
sequence gave out whether it was to us or someone else.  this makes it
NOT SAFE for concurrent transactions, but more for maintenance work.

I use returning almost exclusively now.

  

The PostgresSQL 8.3 help file clearly shows that lastval() does not take a sequence as a parameter and the description i is "Return the value most recently returned by nextval in the current session. This function is identical to currval, except that instead of taking the sequence name as an argument it fetches the value of the last sequence that nextval was used on in the current session. It is an error to call lastval if nextval has not yet been called in the current session." Is the help incorrect?

Bill