Re: SQL to extract column metadata

"Scott Ford" <Scott(dot)Ford(at)bullfrogpower(dot)com> writes:
> CREATE TABLE accounts
> (
> account_id integer NOT NULL DEFAULT
> nextval(('accounts_account_id_seq'::text)::regclass),
> account_number character varying(40) NOT NULL
> )

> Is there a way for me to query the database metadata to verify that
> accounts.account_id uses the sequence accounts_account_id_seq as it's
> default next value?

If it's done as above (with a run-time cast from text to regclass),
there isn't any hardwired connection between the column and the sequence
--- for all the database knows, that string value might be different
each time. (On a more practical note, renaming the sequence or changing
the schema search path could change which sequence gets used.) So about
all you could do is look at pg_attrdef.adsrc and try to extract the
string as a string. Messy, and I don't recommend it.

The more modern way to represent a serial default is

DEFAULT nextval('accounts_account_id_seq'::regclass)

which is different because a regclass constant is actually a reference to
the OID of the sequence. It will track renamings of the sequence (even
across schemas), and more to the point for the immediate purpose, the
database "knows" that this is a reference to the sequence --- for
instance it won't let you drop the sequence without removing the default
expression. The way it knows that is that there's an entry in pg_depend
linking the sequence to the default expression.

So the bottom line here is that you can find out the connection by
joining pg_attrdef to pg_depend to pg_class. I'm too lazy to present a
worked-out example, but you can probably find something related in the
source code for pg_dump.

BTW, if you actually declare the column as a serial column, you could
just use pg_get_serial_sequence() for this. That also does a pg_depend
join under the hood, but it's a little different from the one you'd need
to find a column that has a handmade default referencing a sequence.

regards, tom lane