How to use procedure variable in "alter column restart value" command?

Question

I have two tables - table1 and table2, both have integer autoincrement column. I want to write procedure witch select max value from table1.column, makes plus 1 and set it to default autoincrement value to table2.column.

create or replace procedure testProc() language plpgsql as $$
 declare 
 number_start_value integer;
 begin
 select (max(column_2) + 1) into number_start_value from public.test;
 alter table public.test2 alter column xyz restart with number_start_value;
 end;
 $$

But appears syntax error in alter table command - 'number' expected, got 'number_start_value'.

How i can solve this error or achieve the goal by another method?

• user1822: without DDL: SELECT setval(pg_get_serial_sequence('public.test2', 'column_2'), max(column_2)) FROM public.test

Answer 1

Parameterized SQL statements can only be INSERT, UPDATE DELETE, SELECT and VALUES. For ALTER TABLE, you need to use dynamic SQL:

EXECUTE format(
 'alter table public.test2 alter column xyz restart with %s',
 number_start_value
 );