Re: alter sequence in a function

On Jul 3, 2007, at 6:48 PM, Julio Leyva wrote:

> 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

You are appending the literal string "newvalue" not the string
"100010". Change newvalue to text and cast it from an integer, if
necessary. Then you want:

execute 'ALTER sequence safeopencloseoperation_id_seq restart with
' || newvalue ;

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL