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';
>
> sequencename | schema | tablename | columnname
> - --------------+--------+-----------+------------
> foo_id_seq | public | foo | id
> (1 row)
>
> - From there, scripting should be easy.
A thing of beauty ! Is it portable or tied to certain versions ? (not familiar enough with system tables and changes therein to have my own opinion)
Greg W.