CODEWITHSUNDEEP
sqlpostgresqlfor-loop
Yavuz Selim
Yavuz Selim

Reputation: 546

postgresql change all sequences with for loop

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?

Upvotes: 11

Views: 13894

Answers (3)

toph
toph

Reputation: 220

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 $$;

Upvotes: 14

dr17my
dr17my

Reputation: 171

I've got some tables that have not got 'id' column and some tables use special names in camelCase, so they are need to be quoted. Hope this javuzs's solutions upgrade will be heplfull for someone.

DO $$
DECLARE
i TEXT;
BEGIN
  FOR i IN (SELECT tbls.table_name FROM information_schema.tables AS tbls INNER JOIN information_schema.columns AS cols ON tbls.table_name = cols.table_name WHERE tbls.table_catalog='YOUR_DATABASE_NAME' AND tbls.table_schema='public' AND cols.column_name='id') LOOP
      EXECUTE 'SELECT setval(''"' || i || '_id_seq"'', (SELECT MAX(id) FROM ' || quote_ident(i) || '));';
  END LOOP;
END $$;

Upvotes: 9

Yavuz Selim
Yavuz Selim

Reputation: 546

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$$;

Upvotes: 14

Related Questions