Обсуждение: alter sequence in a function
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
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
>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
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