From: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
---|---|
To: | Joe Krill <joe(at)joekrill(dot)com> |
Cc: | pgsql-www(at)postgresql(dot)org |
Subject: | Re: Wiki editor request: Fixing Sequences |
Date: | 2018-03-27 20:16:55 |
Message-ID: | 20180327201655.7n46zx3djpauwir5@alvherre.pgsql |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-www |
Joe Krill wrote:
> Hello,
>
> My wiki account name is JoeK
>
> I was hoping to share an updated Fixing Sequences query (
> https://wiki.postgresql.org/wiki/Fixing_Sequences) We tried to use the
> query as it currently stands and ran into a handful of problems.
You're an editor now. I think your proposed query will fail if you have
a default sequence in a different schema than the table it's default
for; watch out for that case. Also, I propose that instead of || and
quote_foo(), the function "format" and cast of the pg_class.oid column to
regclass result in easier-to-read code.
Thanks
> SELECT 'SELECT SETVAL(' ||
> quote_literal(quote_ident(SCHEMAS.nspname) || '.' ||
> quote_ident(SEQUENCES.relname)) ||
> ', COALESCE(MAX(' ||quote_ident(COLUMNS.attname)|| '), 1) ) FROM ' ||
> quote_ident(SCHEMAS.nspname)|| '.'||quote_ident(TABLES.relname)|| ';'
> FROM pg_class SEQUENCES
> INNER JOIN pg_namespace SCHEMAS ON SCHEMAS.oid = SEQUENCES.relnamespace
> INNER JOIN pg_depend ON pg_depend.objid = SEQUENCES.oid
> INNER JOIN pg_attribute COLUMNS ON COLUMNS.attrelid = pg_depend.refobjid
> AND COLUMNS.attnum = pg_depend.refobjsubid
> INNER JOIN pg_class TABLES ON TABLES.oid = COLUMNS.attrelid
> WHERE SEQUENCES.relkind = 'S'
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Krill | 2018-03-27 21:14:04 | Re: Wiki editor request: Fixing Sequences |
Previous Message | Joe Krill | 2018-03-27 17:13:59 | Wiki editor request: Fixing Sequences |