Re: [GENERAL] Last value inserted

[Robby Russell]

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  | [EMAIL PROTECTED]
* 503.351.4730  | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
*    --- Now supporting PHP5 ---
****************************************/

signature.asc
Description: This is a digitally signed message part