X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 82DCAD1B50C for ; Mon, 10 Nov 2003 12:18:17 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 84343-10 for ; Mon, 10 Nov 2003 08:17:49 -0400 (AST) Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by svr1.postgresql.org (Postfix) with ESMTP id 572E3D1B54B for ; Mon, 10 Nov 2003 08:17:45 -0400 (AST) Received: from matrix.gatewaynet.com (localhost.localdomain [127.0.0.1]) by matrix.gatewaynet.com (8.12.8/8.12.8) with ESMTP id hAACIO9n002110; Mon, 10 Nov 2003 14:18:24 +0200 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.8/8.12.8/Submit) with ESMTP id hAACIOK3002106; Mon, 10 Nov 2003 14:18:24 +0200 Date: Mon, 10 Nov 2003 14:18:24 +0200 (EET) From: Achilleus Mantzios To: Kumar Cc: Christoph Haller , Subject: Re: Dynamic Query for System functions - now() In-Reply-To: <03b801c3a781$c9d9cda0$7502a8c0@hdsc.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/84 X-Sequence-Number: 15899 Kumar, why dont you try prepare fooplan2(int4) as select now() + ($1||' month')::interval; execute fooplan2(12); O kyrios Kumar egrapse stis Nov 10, 2003 : > Dear Christoph Haller, > > The code that u sent is not working > test=> EXECUTE 'select now()+ interval\'' ||to_char(3,'9')|| 'month\''; > ERROR: parser: parse error at or near "'select now()+ interval\''" at > character 9 > test=> > > Also I tried just to run the now() function with a dynamic query, I got the > following error. Please correct me. > > test=> select now(); > now > ------------------------------- > 2003-11-10 17:06:36.783779+00 > (1 row) > > test=> execute 'select now()'; > ERROR: parser: parse error at or near "'select now()'" at character 9 > test=> > > > Regards > kumar > > ----- Original Message ----- > From: "Christoph Haller" > To: ""Kumar"" > Cc: > Sent: Monday, November 10, 2003 5:07 PM > Subject: Re: [SQL] Dynamic Query for System functions - now() > > > > > > > > Dear Friends, > > > > > > I am using PostgreSQL 7.3.4 Server on RH Linux 7.2. I am trying to > generate= > > > a dynamic query to fetch the next month interval. > > > > > > select now()+ interval'1 month'; -- This is working fine. > > > > > > I wanna dynamically assign the interval number. i,e --> select now()+ > inter= > > > val'n month'; > > > > > > For this I wanted to write a dynamic query. > > > EXECUTE 'select now()+ interval\'' || 3|| 'month\''; > > > Error > > > ERROR: parser: parse error at or near "'select now()+ interval\''" > at = > > > character 9 > > > > > > Help help me with this. I wanted to use this query inside a PLpgSQL > functio= > > > n. > > > > > > Regards > > > Kumar > > > > > You'd probably want to use the to_char() function like this > > EXECUTE 'select now()+ interval\'' ||to_char(3,'9')|| 'month\''; > > > > The || operator is the string concatination operator. > > > > Regards, Christoph > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- -Achilleus