-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Fri, 21 Dec 2007 18:14:43 -0700
D"Gregory Williamson" <Gregory.Williamson@digitalglobe.com> wrote:
> I think the OP needs a way to do _all_ of the sequences, which can be
> a little dauning if you have lots of tables. I'm sure there's a way
> but I haven't the time to puzzle it out -- off to SF for a(n)
> (im)moderate celebration. I might hack at this later tonight if I am
> capable.
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.
Sincerely,
Joshua D. Drake
- --
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFHbH6DATb/zqfZUUQRAlY9AJ9UwlaveD91Hw5FXv5YsHyfzfKIVACgrNQH
jwBU/EglIibnw9Nz9mgzg1w=
=7pot
-----END PGP SIGNATURE-----