SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-10-2008, 07:43 AM
| ||||
| ||||
 alter sequence in a function 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_seqrestart 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      |
|
04-10-2008, 07:43 AM
| |||
| |||
| Re: alter sequence in a function >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 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly |
|
04-10-2008, 07:43 AM
| ||||
| ||||
| Re: alter sequence in a function 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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster |
Linear Mode
