pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
alter 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>
  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-04 03:49  Vishal Arora <aroravishal22@hotmail.com>
  parent: Julio Leyva <jcleyva@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-04 12:11  John DeSoi <desoi@pgedit.com>
  parent: Julio Leyva <jcleyva@hotmail.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