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 2BC30D1B523 for ; Mon, 10 Nov 2003 13:56:35 +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 12997-04 for ; Mon, 10 Nov 2003 09:56:07 -0400 (AST) Received: from smtp2.mts.net (smtp2.mts.net [205.200.16.113]) by svr1.postgresql.org (Postfix) with ESMTP id 9856ED1B53E for ; Mon, 10 Nov 2003 09:56:03 -0400 (AST) Received: from cleartag (wnpgmb01bbw-ac01-9-183.dial.mts.net [142.161.9.183]) by smtp2.mts.net (8.12.8-20030918/8.11.3) with SMTP id hAADu4jx017200; Mon, 10 Nov 2003 07:56:05 -0600 (CST) Message-ID: <006401c3a792$7182b0c0$6400a8c0@cleartag> From: "George Weaver" To: "Kumar" , "Christoph Haller" Cc: References: <200311101037.LAA29518@rodos> <03b801c3a781$c9d9cda0$7502a8c0@hdsc.com> Subject: Re: Dynamic Query for System functions - now() Date: Mon, 10 Nov 2003 07:55:49 -0600 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.6 tagged_above=0.0 required=5.0 tests=BAYES_30, QUOTED_EMAIL_TEXT, REFERENCES, WEIRD_QUOTING X-Spam-Level: X-Archive-Number: 200311/87 X-Sequence-Number: 15902 Kumar, What about this: EXECUTE 'select now()+ interval \' || to_char(3,\'9\') || \'month\' '; George ----- Original Message ----- From: "Kumar" To: "Christoph Haller" Cc: Sent: Monday, November 10, 2003 5:57 AM Subject: Re: [SQL] Dynamic Query for System functions - now() > 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 >