Re: determine sequence name for a serial

On Wed, 2004-10-27 at 21:33 -0700, Robby Russell wrote:
>
> I figured out how to get this:
>
> foo=> SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
> pg_class WHERE relname = 'foo');
> adsrc
> ------------------------------------
> nextval('public.foo_id_seq'::text)
> (1 row)
>
> However, this will break as soon as I do this:
>
> foo=> CREATE SCHEMA x;
> CREATE SCHEMA
> foo=> CREATE TABLE x.foo (id SERIAL PRIMARY KEY NOT NULL, x TEXT);
> NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for
> "serial" column "foo.id"
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "foo_pkey" for table "foo"
> CREATE TABLE
> foo=> SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
> pg_class WHERE relname = 'foo');
> ERROR: more than one row returned by a subquery used as an expression
>
> So, it was a nice attempt, but I am back to the need to of determining
> the sequence name using a schema and a table.
>

Also, I am trying to avoid assuming that the sequence name will be:
schema.table_id_seq

The goal is to determine the sequence name for any schema.table that has
a SERIAL sequence (because you can create a sequence with a different
name) ... and if the column name isn't 'id'

for example:
foo=> SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
pg_class WHERE relname = 'bar');
adsrc
-------------------------------------
nextval('public.bar_nid_seq'::text)
(1 row)

The schema.table_id_seq wouldn't work under this scenario.

any thoughts or pointers?

Thanks,

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 ---
****************************************/