Re: Last value inserted

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thursday 11 November 2004 10:23 am, Franco Bruno Borghesi wrote:
> I think the best way would be not to use a SERIAL field, but an INTEGER
> field and a sequence:

a "serial" is just a convenient shortcut to an int with an automatically
created sequence. As proof - just create a table with a serial and dump it
with pg_dump: you'll end up with a table containing an int with a nextval(...
as the default. The only difference is that in case of the "serial" field you
don't name the sequence yourself.

> CREATE SEQUENCE parent_seq;
> CREATE TABLE parent(id INTEGER, descrip CHAR(50));
>
>
> So when you want to insert on the parent table, you obtain the next
> value from the sequence and then you insert in the parent and child
> tables the value you obtained:
>
> newId:=SELECT nextval('parent_seq')
> INSERT INTO parent(id, descrip) VALUES (newId, 'XXXX');
> INSERT INTO child_1(..., ..., parentId) VALUES (..., ..., newId);
> INSERT INTO child_2(..., ..., parentId) VALUES (..., ..., newId);
> INSERT INTO child_3(..., ..., parentId) VALUES (..., ..., newId);

which amounts to the curval in the same connection.

>
> hope it helps.
>
> MaRCeLO PeReiRA wrote:
> >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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

- --
UC

- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFBlFD5jqGXBvRToM4RAmfQAJ9JyQxERqcau1kCnvkrXNmpaGTwzwCgqK6L
7zCpR+uO5pzvDuY/itTYCfs=
=mq0M
-----END PGP SIGNATURE-----