I have to reset multiple postgres sequences at the start of every year automatically. I'd like to do it via a scheduled job in Spring Boot. The sequences have a name pattern where there is a prefix and then a number at the end. I'd like to find them based on the name and then reset them to 0. Is it possible? How would that look like? This is what I could come up with
do $$
declare
bn int4;
seq_name varchar(50);
begin
for bn in (SELECT bank_number FROM public.banks b) loop
seq_name := CONCAT('invoice_nummer_seq_tn_', cast(bn as varchar));
alter sequence seq_name restart with 0;
end loop;
end; $$
I am having the error:
SQL Error [42P01]: ERROR: relation "seq_name" does not exist Where: SQL statement "alter sequence seq_name restart with 0" PL/pgSQL function inline_code_block line 8 at SQL statement
CodePudding user response:
The problem you have is that the sequence name is a structural element and as such is not subject to variable substitution, which you are attempting. You need a build the statement dynamically and then use execute statement
. So something like: (see demo)
do $$
declare
bn int4;
stmt text;
base constant text = 'alter sequence invoice_nummer_seq_tn_%s restart with 0';
begin
for bn in (select bank_number from banks b)
loop
stmt = format(base,bn::text);
raise notice E'---Running Statement:---\n\t%\n------',stmt;
execute stmt;
end loop;
end;
$$;