Re: determine sequence name for a serial - Mailing list pgsql-general

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
sequencefor 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
mycode.  
>
> 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
builda 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@planetargon.com
* 503.351.4730  | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
* --- Now supporting PHP5 and PHP4 ---
****************************************/