Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id A31499FBC5B for ; Wed, 4 Jul 2007 09:11:52 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 98416-04 for ; Wed, 4 Jul 2007 09:11:49 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from a2s5.a2hosting.com (a2s5.a2hosting.com [209.124.50.10]) by postgresql.org (Postfix) with ESMTP id 1F2E19FB6B9 for ; Wed, 4 Jul 2007 09:11:49 -0300 (ADT) Received: from adsl-150-239-252.tys.bellsouth.net ([72.150.239.252] helo=[192.168.1.12]) by a2s5.a2hosting.com with esmtpsa (TLSv1:AES128-SHA:128) (Exim 4.63) (envelope-from ) id 1I63hy-0004Qy-Br; Wed, 04 Jul 2007 08:11:46 -0400 In-Reply-To: References: Mime-Version: 1.0 (Apple Message framework v752.3) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: Cc: Content-Transfer-Encoding: 7bit From: John DeSoi Subject: Re: alter sequence in a function Date: Wed, 4 Jul 2007 08:11:41 -0400 To: Julio Leyva X-Mailer: Apple Mail (2.752.3) X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - a2s5.a2hosting.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - pgedit.com X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/31 X-Sequence-Number: 26012 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