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 D19ADD1B544 for ; Mon, 10 Nov 2003 12:00:13 +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 91452-03 for ; Mon, 10 Nov 2003 07:59:45 -0400 (AST) Received: from smtp004.mail.ukl.yahoo.com (smtp004.mail.ukl.yahoo.com [217.12.11.35]) by svr1.postgresql.org (Postfix) with SMTP id 1BDFCD1B50C for ; Mon, 10 Nov 2003 07:59:41 -0400 (AST) Received: from unknown (HELO IVESIA67) (sgnerd@203.196.190.138 with login) by smtp1.mail.vip.ukl.yahoo.com with SMTP; 10 Nov 2003 11:59:42 -0000 Message-ID: <03b801c3a781$c9d9cda0$7502a8c0@hdsc.com> Reply-To: "Kumar" From: "Kumar" To: "Christoph Haller" Cc: References: <200311101037.LAA29518@rodos> Subject: Re: Dynamic Query for System functions - now() Date: Mon, 10 Nov 2003 17:27:11 +0530 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/82 X-Sequence-Number: 15897 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