Re: derive the sequence name of a column

Works fine with 7.2.2. Thanks! I still can't figure it out in 7.1.3

There's no pg_namespace there. Any ideas?

Thanks again,

MEL

-----Original Message-----
From: Stephan Szabo [mailto:sszabo(at)megazone(dot)bigpanda(dot)com]
Sent: Thursday, October 30, 2003 11:18 PM
To: Mel Jamero
Cc: 'Bruno Wolff III'; pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] derive the sequence name of a column

On Thu, 30 Oct 2003, Mel Jamero wrote:

> Thank for the reply Bruno but I need more. =)
>
> Sorry, I have to send this again because I haven't figured out how to
> solve this.
>
> Can anyone please tell me exactly how the name of a sequence a field
is
> using (manually created or generated by a serial) could be derived
> programmatically (using libpq or through SQL)?
>
> Thus:
>
> CREATE SEQUENCE an_unknown_sequence_name;
> CREATE TABLE test (
> test_id integer default nextval('an_unknown_sequence_name'),
> useless_redundant_test_id serial
> );
>
> How do I programmatically extract that column 'test_id' in table
'test'
> is using 'an_unknown_sequence_name'

I'd suggest looking in pg_attrdef.

Something like:

select pg_attrdef.* from pg_attrdef, pg_namespace, pg_class,
pg_attribute
where pg_namespace.nspname='public' and
pg_class.relnamespace=pg_namespace.oid and pg_class.relname='test' and
pg_attribute.attrelid=pg_class.oid and pg_attribute.attname='test_id'
and
pg_attrdef.adrelid=pg_class.oid and
pg_attrdef.adnum=pg_attribute.attnum;

(filling in the schema, table name and column name for the constants).