Re: determine sequence name for a serial

On Wed, 2004-10-27 at 21:06 -0700, Robby Russell wrote:
> I am trying to track down a method of determining what a sequence name
> is for a SERIAL is in postgresql.
>
> For example,
>
> CREATE TABLE foo (id SERIAL PRIMARY KEY NOT NULL, bar TEXT);
>
> \d foo
> Table "public.foo"
> Column | Type | Modifiers
> --------+---------+-----------------------------------------------------
> id | integer | not null default nextval('public.foo_id_seq'::text)
> bar | text |
> Indexes:
> "foo_pkey" primary key, btree (id)
>
> Now, I have figured out how to get a list of all the sequences with:
>
> foo=> SELECT relname FROM pg_class WHERE relkind='S' AND relname !~ '^pg_';
> relname
> ------------
> foo_id_seq
> (1 row)
>
> I can find public.foo in pg_tables, but I am not sure how to relate pg_tables and pg_class in order to find the sequence for a specific field in public.foo.
>
> Can anyone point me in the right direction? I am trying to get out of the habit of hard-coding the sequence names in my code.
>
> Now that I think of it, I am lacking 'public.' as well from my query.
>
> Ok, so how would I go about getting the sequence name for a SERIAL field on any given schema.table? I would like to build a function that would return this value if I pass it the schema and table (and fieldname is necessary)
>
> Thanks,

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.

Help. :-)

Thanks again,

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