postgresql change all sequences with for loop

Question

I have sequences like table_name_sq in postgresql for all tables. For example;

seqtest-> seqtest_sq
seqtest2-> seqtest2_sq

I need to change all sequences in database. (I cannot run query for every tables manually)

I can get tables and make sequence string

select table_name || '_sq' as sequence_name from information_schema.tables where table_catalog='test' and table_schema='public'

I can change sequence value for specified table

select setval('seqtest_sq',(select max(id) from seqtest)+1)

But I cannot merge these two. I think should use for loop but that I could not make that work.

CODE IS:

DO $$
DECLARE 
i RECORD;
BEGIN
    FOR i IN (select table_name from information_schema.tables) LOOP
         EXECUTE 'SELECT count(*) FROM ' || i;
    END LOOP;
END$$;

OUTPUT is:

ERROR:  syntax error at or near ")"
LINE 1: SELECT count(*) FROM (seqtest)
                                     ^
QUERY:  SELECT count(*) FROM (seqtest)
CONTEXT:  PL/pgSQL function inline_code_block line 6 at EXECUTE
********** Error **********

ERROR: syntax error at or near ")"
SQL state: 42601
Context: PL/pgSQL function inline_code_block line 6 at EXECUTE

Also I printed table names with for loop but table names come with parentheses.

HERE is CODE

DO $$
DECLARE 
i RECORD;
BEGIN
    FOR i IN (select table_name from information_schema.tables where table_catalog='test' and table_schema='public') LOOP
    raise notice 'Value: %',i;
    END LOOP;
END$$;

HERE is OUTPUT:

NOTICE:  Value: (seqtest)
NOTICE:  Value: (seqtest2)

I think it is enough for me to get rid of this parentheses.

Would you help me to build a proper loop or find a easy way to achive this?

Answer 1

Here is the script I use.

DO $$
DECLARE
i TEXT;
BEGIN
  FOR i IN (
    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
  ) LOOP
      EXECUTE i;
  END LOOP;
END $$;

Answer 2

Here is the solution with help of @Nick Barnes and @a_horse_with_no_name

If someone needs a idea of how to fix sequences can use this script.

DO $$
DECLARE 
i TEXT;
BEGIN
    FOR i IN (select table_name from information_schema.tables where table_catalog='YOUR_DATABASE_NAME' and table_schema='public') LOOP
    EXECUTE 'Select setval('''||i||'_sq'', (SELECT max(id) as a FROM ' || i ||')+1);';
    END LOOP;
END$$;