On Tue, 16 Nov 2004, Jeff Eckermann wrote:
> --- Jerry III <jerryiii@xxxxxxxxxxx> 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.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
