I'm trying to create a function that alters a sequence
This what I'm doing
create or replace function updatesafe() returns integer AS $$ DECLARE maxseq integer; alterseq varchar(256); thumb integer; newvalue integer; BEGIN newvalue := 10010; maxseq := (select max(safeoperationid) from safeopencloseoperation);
if (maxseq < 500) then return 3000;
else execute 'ALTER sequence safeopencloseoperation_id_seq restart with ' || 'newvalue ' ; return 10000; END IF; END; $$ language plpgsql
It compiles ok but when I call the function it gives me this error
ALTER sequence safeopencloseoperation_id_seq restart with newvalue CONTEXT: PL/pgSQL function "updatesafe" line 17 at execute statement LINE 1: ...equence safeopencloseoperation_id_seq restart with newvalue
However when I change the alter sequence for this
ALTER sequence safeopencloseoperation_id_seq restart with 10000 ;
The function is ok,
It means that we can't use such a utility inside a function? I mean replacing a value for a variable?
