Re: Last value inserted

On Tue, 16 Nov 2004, Jeff Eckermann wrote:

> --- Jerry III <jerryiii@hotmail.com> wrote:
>
> > Which means that sometimes they do not return the
> > correct value - if you
> > have a trigger that inserts another record you will
> > not get the right value.
>
> If you are new to PostgreSQL, as you say, then why are
> you so sure of this?  Perhaps you may profit from
> looking a little more at how currval() works.

He's correct.  One thing that currval will not help with is a
case where more than one row has been inserted by a statement
(whether due to the base statement or triggers).

A somewhat absurd example:

---

create table q1(a serial, b int);

create function f1() returns trigger as 'begin if (random() >
0.5) then insert into q1 default values; end if; return NEW; end;'
language 'plpgsql';

create trigger q1_f1 after insert on q1 for each row execute
procedure f1();

insert into q1(b) values (3);

select currval('q1_a_seq');

select * from q1;

----

I got a currval of 3 which was the last row inserted, but that was from
the trigger, not the row created by my insert so it didn't have the
correct b value.