Re: Last value inserted

On Thu, 2004-11-11 at 09:59 -0300, 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


I just asked this same question about a week or two ago and I got a
response from Jonathan Daugherty who helped me with the initial query,
and in PHP I was able to come up with:

http://blog.planetargon.com/index.php?/archives/29_PHP_pg_insert_id_.html

This was on the list a few weeks ago:

> -- get_sequence(schema_name, table_name, column_name)
>
> CREATE OR REPLACE FUNCTION get_sequence (text, text, text) RETURNS
> text AS '
>   SELECT seq.relname::text
>   FROM pg_class src, pg_class seq, pg_namespace, pg_attribute,
> pg_depend
>   WHERE
>     pg_depend.refobjsubid = pg_attribute.attnum AND
>     pg_depend.refobjid = src.oid AND
>     seq.oid = pg_depend.objid AND
>     src.relnamespace = pg_namespace.oid AND
>     pg_attribute.attrelid = src.oid AND
>     pg_namespace.nspname = $1 AND
>     src.relname = $2 AND
>     pg_attribute.attname = $3;
> ' language sql;

hth,

Robby

--
/***************************************
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON  | www.planetargon.com
* Portland, OR  | robby@planetargon.com
* 503.351.4730  | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
*    --- Now supporting PHP5 ---
****************************************/