Postgres Pro
Downloads
Home   >   mailing lists

Thread: alter sequence in a function

alter sequence in a function

From
Julio Leyva
Date:
Hi all

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?

Thanks for any suggestion





Re: alter sequence in a function

From
"Vishal Arora"
Date:


>From: Julio Leyva <jcleyva@hotmail.com>
>To: <pgsql-admin@postgresql.org>
>Subject: [ADMIN] alter sequence in a function
>Date: Tue, 3 Jul 2007 22:48:30 +0000
>
>
>Hi allI'm trying to create a function that alters a sequenceThis what I'm
>doingcreate or replace function updatesafe()  returns integer AS
>$$DECLAREmaxseq integer;alterseq varchar(256);thumb integer;newvalue
>integer;BEGINnewvalue := 10010;  maxseq := (select max(safeoperationid)
>from safeopencloseoperation);    if (maxseq < 500) then      return 3000;
>else    execute 'ALTER sequence safeopencloseoperation_id_seq restart with
>' || 'newvalue ' ;

Remove the single quotes (' ') from newvalue in the execute, it is taking
newvalue as a string instead of treating it as a variable.

return 10000; END IF;END;$$ language plpgsqlIt compiles ok but when I call
the functionit gives me this error ALTER sequence
safeopencloseoperation_id_seq restart with newvalueCONTEXT:  PL/pgSQL
function "updatesafe" line 17 at execute statementLINE 1: ...equence
safeopencloseoperation_id_seq restart with newvalueHowever when I change the
alter sequence for thisALTER 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?Thanks for any
suggestion

_________________________________________________________________
http://newlivehotmail.com

Re: alter sequence in a function

From
John DeSoi
Date:
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