"Gregory Williamson" <Gregory.Williamson@digitalglobe.com> writes:
> Joshua Drake shaped the aether to say:
>> Shout out to AndrewSN for this one (although I was almost there when he
>> pasted it ;)):
>>
>> SELECT c1.relname AS sequencename, n.nspname AS schema,
>> c2.relname AS tablename, a.attname AS columnname
>> FROM pg_class c1
>> JOIN pg_depend d ON (d.objid=c1.oid)
>> JOIN pg_class c2 ON (d.refobjid=c2.oid)
>> JOIN pg_attribute a ON (a.attrelid=c2.oid AND a.attnum=d.refobjsubid)
>> JOIN pg_namespace n ON (n.oid=c2.relnamespace)
>> WHERE c1.relkind='S'
>> AND d.classid='pg_class'::regclass
>> AND d.refclassid='pg_class'::regclass
>> AND d.refobjsubid > 0
>> AND d.deptype='a';
> A thing of beauty ! Is it portable or tied to certain versions ? (not familiar enough with system tables and changes
thereinto have my own opinion)
Offhand I believe that this would work in every PG version since 7.3.
It would definitely not work before that (7.2 had neither pg_namespace
nor pg_depend).
[pokes at it for a bit...] Actually the deptype='a' bit is not so
robust; we used to use 'i' for serial dependencies. I'd leave that test
out entirely, I think --- it doesn't seem essential, because there
isn't any other reason for a sequence to depend on a table column.
Otherwise the query seems correct.
As for possible future breakage, who can say? There's nothing else here
that I foresee problems for, but I don't have a crystal ball.
regards, tom lane