From: | Walter Meng <wme(at)ingenioustechnologies(dot)com> |
---|---|
To: | "pgsql-www(at)postgresql(dot)org" <pgsql-www(at)postgresql(dot)org> |
Subject: | Wiki change request |
Date: | 2017-02-20 15:18:33 |
Message-ID: | VI1PR02MB13602FB76BF3CBE00141A2ADA15E0@VI1PR02MB1360.eurprd02.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-www |
Dear PostgreSQL Web Team,
Many thanks for the lots of good information on PG Wiki. Today I used the sequences fixing script provided on the page
https://wiki.postgresql.org/wiki/Fixing_Sequences
and would suggest one small improvement to the following SQL query:
SELECT 'SELECT SETVAL(' ||
quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
pg_depend AS D,
pg_class AS T,
pg_attribute AS C,
pg_tables AS PGT
WHERE S.relkind = 'S'
AND S.oid = D.objid
AND D.refobjid = T.oid
AND D.refobjid = C.attrelid
AND D.refobjsubid = C.attnum
AND T.relname = PGT.tablename
ORDER BY S.relname;
If there are tables with same name in two or more different namespaces, the script produces a Cartesian product for the combination of these tables and related columns. I could solve this issue by usage of "pg_namespace" instead of "pg_tables" and joining them by oid instead of the name:
SELECT 'SELECT SETVAL(' ||
quote_literal(quote_ident(PGT.nspname) || '.' || quote_ident(S.relname)) ||
', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
quote_ident(PGT.nspname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
pg_depend AS D,
pg_class AS T,
pg_attribute AS C,
pg_namespace PGT
WHERE S.relkind = 'S'
AND S.oid = D.objid
AND D.refobjid = T.oid
AND D.refobjid = C.attrelid
AND D.refobjsubid = C.attnum
and T.relnamespace=PGT.oid
ORDER BY S.relname;
Since I was not allowed to edit the page, I'd like just to send it directly to you and ask to add the code to the page (if you find it OK ;) ) I tested it on Postgresql 9.4.
Kind regards
Walter Meng
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Gustafsson | 2017-02-20 19:21:29 | A few more http links |
Previous Message | Daniel Gustafsson | 2017-02-20 14:38:18 | Re: Attribute minimization fix for sitesearch |