Resetting multiple postgres sequence based on name pattern

Question

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

Answer 1

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