Thanks Álvaro! I hadn't considered that the sequence could be in a different schema. I was only using the `quote_foo` functions because the existing query uses it, but using format definitely makes it much easier to read. I'm not totally clear on what needs to be cast to a regclass, though. Would you mind elaborating on that? All of the oid comparisons are to other oid types, so I didn't think there was a need to cast. But this is getting a bit outside of my scope of knowledge on this stuff.
Here's the updated query using format and the correct schema:
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'