On 03/31/2011 02:47 PM, Carlo Stonebanks wrote:
Thanks Adrian and Jerry.
Technically, the best way to know which sequence a column is dependent on is
to actually query for it. I have functions which query
information_schema.columns and run a regex_replace to extract the sequence
name from the defaulting nextval() expression. This is better than demanding
that sequence names are predictable, but I wonder if there isn't a better
query to run that doesn't require parsing texts? Obviously PG knows about
the sequence's relation, probably via a dependency that finds it by
rendering the text to regclass to an OID... but this stuff makes me nervous.
So, a query that returns the sequence name (as text, you can cast to
regclass!) associated with a particular column, that would return NUL if
there was none... I don't suppose anyone has written such a query before...?
Yes, I'd write a function around it!
Thanks again,
Carlo
Well in 8.2+ for sequences created by the SERIAL datatype there is the
function:
pg_get_serial_sequence(table_name, column_name)
The benefit of using SERIAL is that it sets up a dependency.
If you specify the sequence via CREATE SEQUENCE then AFAIK you are left
looking up the default and parsing it.
--
Adrian Klaver
adrian.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
