Обсуждение: [pgsql-www] Wiki change request

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