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