Re: determine sequence name for a serial

On Wed, 2004-10-27 at 22:45 -0700, Jonathan Daugherty wrote:
> # CREATE OR REPLACE FUNCTION get_default_value (text, text, text) RETURNS text AS '
> # SELECT adsrc
> # FROM pg_attrdef, pg_class, pg_namespace, pg_attribute
> # WHERE
> # adrelid = pg_class.oid AND
> # pg_class.relnamespace = pg_namespace.oid AND
> # pg_attribute.attnum = pg_attrdef.adnum AND
> # pg_attribute.attrelid = pg_class.oid AND
> # pg_namespace.nspname = $1 AND
> # pg_class.relname = $2 AND
> # pg_attribute.attname = $3;
> # ' language sql;
>
> As per Tom's mention of pg_depend, here's something that seems to do
> the trick for the time being, assuming the column is a serial:
>
> -- 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;
>

Thanks, this seems to work well. My goal is to actually create a php
function that takes a result and returns the insert_id like
mysql_insert_id() does, but without needing to know the sequence names
and such. I would make a psql function, but I don't always have that
option with some clients existing systems.

-Robby

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