pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedalter sequence in a function
3+ messages / 3 participants
[nested] [flat]
* alter sequence in a function
@ 2007-07-03 22:48 Julio Leyva <jcleyva@hotmail.com>
2007-07-04 03:49 ` Re: alter sequence in a function Vishal Arora <aroravishal22@hotmail.com>
2007-07-04 12:11 ` Re: alter sequence in a function John DeSoi <desoi@pgedit.com>
0 siblings, 2 replies; 3+ messages in thread
From: Julio Leyva @ 2007-07-03 22:48 UTC (permalink / raw)
To: pgsql-admin
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 ' ; 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=
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: alter sequence in a function
2007-07-03 22:48 alter sequence in a function Julio Leyva <jcleyva@hotmail.com>
@ 2007-07-04 03:49 ` Vishal Arora <aroravishal22@hotmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Vishal Arora @ 2007-07-04 03:49 UTC (permalink / raw)
To: jcleyva@hotmail.com; +Cc: pgsql-admin
>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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: alter sequence in a function
2007-07-03 22:48 alter sequence in a function Julio Leyva <jcleyva@hotmail.com>
@ 2007-07-04 12:11 ` John DeSoi <desoi@pgedit.com>
1 sibling, 0 replies; 3+ messages in thread
From: John DeSoi @ 2007-07-04 12:11 UTC (permalink / raw)
To: Julio Leyva <jcleyva@hotmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-07-04 12:11 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-03 22:48 alter sequence in a function Julio Leyva <jcleyva@hotmail.com>
2007-07-04 03:49 ` Vishal Arora <aroravishal22@hotmail.com>
2007-07-04 12:11 ` John DeSoi <desoi@pgedit.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox