Re: alter sequence in a function

John DeSoi <desoi@xxxxxxxxxx> · Wed, 4 Jul 2007 08:11:41 -0400

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