Last value inserted

[MaRCeLO PeReiRA]

Hi guys,

I am in troubles with a SERIAL field.

I have five tables. A parent table and four child
tables. When I do the INSERT in the parent table, I
have an ID (generated) by the sequence (SERIAL field),
and I have to use this ID to reference all child
tables.

Well, once I do an INSERT in the parent table, how can
I know (for sure) which number id was generated by the
sequence?

Simple example:

------------------------------------------------------
CREATE TABLE parent(id SERIAL, descrip CHAR(50));
------------------------------------------------------

So,

------------------------------------------------------
INSERT INTO parent (descrip) VALUES ('project 1');
------------------------------------------------------

How can I now (for sure) with value was generated by
the sequence to fill the field ID?

(There is lots of users using the software at the same
time, so I am not able to use the last_value()
function on the sequence.)

Best Regards,

Marcelo Pereira
Brazil

_______________ _______________ _______________ __________
Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tom Lane]

Jeff Eckermann <je************ @yahoo.com> writes:

--- Jerry III <je******@hotma il.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.

Jerry's correct, although the concern is more theoretical than real IMHO.
What he's imagining is a situation where you do, say,

INSERT INTO foo ...;
SELECT currval('foo_id _seq');

and there is an ON INSERT trigger on foo that directly or indirectly
does a nextval('foo_id _seq'). Execution of the trigger will then
advance the sequence beyond what was used to generate the inserted row,
and the subsequent currval() will return the wrong answer (or at least
not the answer you wanted). Note there is no race condition here; it's
just one process involved.

The reason I think this is mostly a theoretical issue is that I don't
see any good reason for such a trigger to be doing a nextval on the
table's ID sequence. The trigger is certainly not going to insert
additional rows in foo --- if it did that would lead to infinite
recursion of the trigger. So ISTM this scenario is really not
interesting. If it did happen it would represent a bug in your
application design, no more and no less. For instance, using the same
sequence to feed IDs for multiple tables would be a bug if you had a
trigger that did an insert on one of them as a consequence of an insert
on another.

regards, tom lane

---------------------------(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