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?

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