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 01D2FD1B4FE for ; Mon, 10 Nov 2003 13:05:44 +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 04218-01 for ; Mon, 10 Nov 2003 09:05:16 -0400 (AST) Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52]) by svr1.postgresql.org (Postfix) with SMTP id 501ABD1B532 for ; Mon, 10 Nov 2003 09:05:10 -0400 (AST) Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ; Mon Nov 10 14:05:03 2003 +0100 Received: œby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id OAA27306; Mon, 10 Nov 2003 14:05:02 +0100 (MET) From: Christoph Haller Message-Id: <200311101305.OAA15913@rodos> Subject: Re: Dynamic Query for System functions - now() To: achill@matrix.gatewaynet.com (Achilleus Mantzios) Date: Mon, 10 Nov 2003 14:05:02 MET Cc: sgnerd@yahoo.com.sg, pgsql-sql@postgresql.org In-Reply-To: ; from "Achilleus Mantzios" at Nov 10, 103 1:17 pm X-Mailer: Elm [revision: 212.4] X-Virus-Scanned: by amavisd-new at postgresql.org X-Amavis-Alert: BAD HEADER Non-encoded 8-bit data (char 9C hex) in message header 'Received' Received: \234by rodos.fzk.de... ^ X-Archive-Number: 200311/85 X-Sequence-Number: 15900 I like Achilleus' proposal for its elegance, just in case you are interested in the old-school plpgsql way: create or replace function future_date(int) returns timestamp as 'declare future_dat timestamp; future_off text; begin future_dat := ''now'' ; future_off := $1::text; future_off := future_off ||'' month''; return future_dat + future_off::interval; END;' LANGUAGE 'plpgsql' ; select future_date(9); future_date ---------------------------- 2004-08-10 13:57:12.700499 (1 row) Regards, Christoph > > 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 >