0

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?

1
  • 1
    without DDL: SELECT setval(pg_get_serial_sequence('public.test2', 'column_2'), max(column_2)) FROM public.test
    – user1822
    Oct 10, 2022 at 12:50

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.