Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 511AC9FB308 for ; Wed, 4 Jul 2007 00:49:26 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 01177-02 for ; Wed, 4 Jul 2007 00:49:22 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from bay0-omc2-s4.bay0.hotmail.com (bay0-omc2-s4.bay0.hotmail.com [65.54.246.140]) by postgresql.org (Postfix) with ESMTP id 688D99FB289 for ; Wed, 4 Jul 2007 00:49:22 -0300 (ADT) Received: from hotmail.com ([207.46.11.98]) by bay0-omc2-s4.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.2668); Tue, 3 Jul 2007 20:49:21 -0700 Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Tue, 3 Jul 2007 20:49:21 -0700 Message-ID: Received: from 207.46.11.123 by by123fd.bay123.hotmail.msn.com with HTTP; Wed, 04 Jul 2007 03:49:19 GMT X-Originating-IP: [59.160.193.34] X-Originating-Email: [aroravishal22@hotmail.com] X-Sender: aroravishal22@hotmail.com In-Reply-To: From: "Vishal Arora" To: jcleyva@hotmail.com Cc: pgsql-admin@postgresql.org Subject: Re: alter sequence in a function Date: Wed, 04 Jul 2007 03:49:19 +0000 Mime-Version: 1.0 Content-Type: text/plain; format=flowed X-OriginalArrivalTime: 04 Jul 2007 03:49:21.0218 (UTC) FILETIME=[4E053A20:01C7BDEE] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.849 tagged_above=0 required=5 tests=AWL=-0.319, BAYES_20=-0.74, DNS_FROM_RFC_ABUSE=0.2, DNS_FROM_RFC_POST=1.708, MSGID_FROM_MTA_HEADER=0 X-Spam-Level: X-Archive-Number: 200707/29 X-Sequence-Number: 26010 >From: Julio Leyva >To: >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