yavuzs yavuzs - 9 days ago 6
SQL Question

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?

Answer

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