From sgnerd@yahoo.com.sg Mon Nov  10 07:15:50 2003
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 C88B8D1B50C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 10 Nov 2003 07:15:50 +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 40915-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 10 Nov 2003 03:15:20 -0400 (AST)
Received: from smtp005.mail.ukl.yahoo.com (smtp005.mail.ukl.yahoo.com
	[217.12.11.36])
	by svr1.postgresql.org (Postfix) with SMTP id 2027CD1B4EF
	for <pgsql-sql@postgresql.org>; Mon, 10 Nov 2003 03:15:18 -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 07:15:17 -0000
Message-ID: <017701c3a75a$0e3e6050$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "psql" <pgsql-sql@postgresql.org>
Subject: Dynamic Query for System functions - now()
Date: Mon, 10 Nov 2003 12:42:47 +0530
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0174_01C3A788.24FBABD0"
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.7 tagged_above=0.0 required=5.0 tests=HTML_40_50
X-Spam-Level: 
X-Archive-Number: 200311/79
X-Sequence-Number: 15894

This is a multi-part message in MIME format.

------=_NextPart_000_0174_01C3A788.24FBABD0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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

------=_NextPart_000_0174_01C3A788.24FBABD0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1264" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY style=3D"COLOR: #0000ff; FONT-FAMILY: Arial" bgColor=3D#ffffff>
<DIV><FONT size=3D2>Dear Friends,</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>I am using PostgreSQL 7.3.4 Server on RH Linux 7.2. I a=
m=20
trying to generate a dynamic query to fetch the next month=20
interval.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>select now()+ interval'1 month';&nbsp; -- This is worki=
ng=20
fine.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>I wanna dynamically assign the interval number. i,e --&=
gt;=20
select now()+ interval'n month';</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>For this I wanted to write a dynamic query.</FONT></DIV>
<DIV><FONT size=3D2>EXECUTE 'select now()+ interval\'' || 3||=20
'month\'';</FONT></DIV>
<DIV><FONT size=3D2>Error</FONT></DIV>
<DIV><FONT size=3D2>&nbsp;&nbsp;&nbsp; ERROR:&nbsp; parser: parse error at =
or near=20
"'select now()+ interval\''" at character 9</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Help help me with this. I wanted to use this query insi=
de a=20
PLpgSQL function.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Regards</FONT></DIV>
<DIV><FONT size=3D2>Kumar</FONT></DIV>
<P></P></BODY></HTML>

------=_NextPart_000_0174_01C3A788.24FBABD0--




From ch@rodos.fzk.de Mon Nov  10 10:37:52 2003
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 3B26FD1B54F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 10 Nov 2003 10:37:52 +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 74781-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 10 Nov 2003 06:37:24 -0400 (AST)
Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52])
	by svr1.postgresql.org (Postfix) with SMTP id B5D2DD1B541
	for <pgsql-sql@postgresql.org>; Mon, 10 Nov 2003 06:37:20 -0400 (AST)
Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ;
 Mon Nov 10 11:37:17 2003 +0100
Received: œby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id LAA26952; Mon, 10 Nov 2003 11:37:16 +0100 (MET)
From: Christoph Haller <ch@rodos.fzk.de>
Message-Id: <200311101037.LAA29518@rodos>
Subject: Re: Dynamic Query for System functions - now()
To: sgnerd@yahoo.com.sg ("Kumar")
Date: Mon, 10 Nov 2003 11:37:15 MET
Cc: pgsql-sql@postgresql.org
In-Reply-To: <017701c3a75a$0e3e6050$7502a8c0@hdsc.com>;
	from "Kumar" at Nov 10, 103 8:19 am
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/80
X-Sequence-Number: 15895

> 
> 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 




From sgnerd@yahoo.com.sg Mon Nov  10 12:00:13 2003
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 <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-sql@postgresql.org>; 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" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "Christoph Haller" <ch@rodos.fzk.de>
Cc: <pgsql-sql@postgresql.org>
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" <ch@rodos.fzk.de>
To: ""Kumar"" <sgnerd@yahoo.com.sg>
Cc: <pgsql-sql@postgresql.org>
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




From achill@matrix.gatewaynet.com Mon Nov  10 12:18:17 2003
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 <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-sql@postgresql.org>; 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 <achill@matrix.gatewaynet.com>
To: Kumar <sgnerd@yahoo.com.sg>
Cc: Christoph Haller <ch@rodos.fzk.de>, <pgsql-sql@postgresql.org>
Subject: Re: Dynamic Query for System functions - now()
In-Reply-To: <03b801c3a781$c9d9cda0$7502a8c0@hdsc.com>
Message-ID: <Pine.LNX.4.44.0311101417470.1995-100000@matrix.gatewaynet.com>
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" <ch@rodos.fzk.de>
> To: ""Kumar"" <sgnerd@yahoo.com.sg>
> Cc: <pgsql-sql@postgresql.org>
> 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




From georgew1@mts.net Mon Nov  10 13:56:35 2003
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 <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-sql@postgresql.org>; 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" <georgew1@mts.net>
To: "Kumar" <sgnerd@yahoo.com.sg>,
	"Christoph Haller" <ch@rodos.fzk.de>
Cc: <pgsql-sql@postgresql.org>
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" <sgnerd@yahoo.com.sg>
To: "Christoph Haller" <ch@rodos.fzk.de>
Cc: <pgsql-sql@postgresql.org>
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" <ch@rodos.fzk.de>
> To: ""Kumar"" <sgnerd@yahoo.com.sg>
> Cc: <pgsql-sql@postgresql.org>
> 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
>




From ch@rodos.fzk.de Mon Nov  10 13:05:44 2003
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 <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-sql@postgresql.org>; 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 <ch@rodos.fzk.de>
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: <Pine.LNX.4.44.0311101417470.1995-100000@matrix.gatewaynet.com>;
	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" <ch@rodos.fzk.de>
> > To: ""Kumar"" <sgnerd@yahoo.com.sg>
> > Cc: <pgsql-sql@postgresql.org>
> > 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
> 




From sszabo@megazone.bigpanda.com Mon Nov  10 15:30:58 2003
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 78455D1B532
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 10 Nov 2003 15:30:58 +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 25304-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 10 Nov 2003 11:30:31 -0400 (AST)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id ABD0CD1B531
	for <pgsql-sql@postgresql.org>; Mon, 10 Nov 2003 11:30:27 -0400 (AST)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id C7D5A353A6; Mon, 10 Nov 2003 07:30:26 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id C6B82351CE; Mon, 10 Nov 2003 07:30:26 -0800 (PST)
Date: Mon, 10 Nov 2003 07:30:26 -0800 (PST)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Kumar <sgnerd@yahoo.com.sg>
Cc: psql <pgsql-sql@postgresql.org>
Subject: Re: Dynamic Query for System functions - now()
In-Reply-To: <017701c3a75a$0e3e6050$7502a8c0@hdsc.com>
Message-ID: <20031110072842.K16647@megazone.bigpanda.com>
References: <017701c3a75a$0e3e6050$7502a8c0@hdsc.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200311/88
X-Sequence-Number: 15903

On Mon, 10 Nov 2003, Kumar wrote:

> select now()+ interval'1 month';  -- This is working fine.
>
> I wanna dynamically assign the interval number. i,e --> select now()+
> interval'n month';

If n is an integer, I'd suggest something like
 select now() + n * interval '1 month';
rather than messing around with the text of the interval literal.




From mendola@bigfoot.com Mon Nov  10 19:13:36 2003
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 C4C18D1B562
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 10 Nov 2003 19:13:36 +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 68306-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 10 Nov 2003 15:13:06 -0400 (AST)
Received: from smtp.noos.fr (nan-smtp-19.noos.net [212.198.2.119])
	by svr1.postgresql.org (Postfix) with ESMTP id B7BE8D1B56C
	for <pgsql-sql@postgresql.org>; Mon, 10 Nov 2003 15:13:04 -0400 (AST)
Received: (qmail 31509 invoked by uid 0); 10 Nov 2003 19:13:05 -0000
Received: from unknown (HELO bigfoot.com) ([212.198.40.101])
	(envelope-sender <mendola@bigfoot.com>)
	by 212.198.2.119 (qmail-ldap-1.03) with SMTP
	for <pgsql-sql@postgresql.org>; 10 Nov 2003 19:13:05 -0000
Message-ID: <3FAFE331.2040308@bigfoot.com>
Date: Mon, 10 Nov 2003 20:12:49 +0100
From: Gaetano Mendola <mendola@bigfoot.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.5) Gecko/20031013 Thunderbird/0.3
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Cc: Kumar <sgnerd@yahoo.com.sg>
Subject: Re: Dynamic Query for System functions - now()
References: <017701c3a75a$0e3e6050$7502a8c0@hdsc.com>
In-Reply-To: <017701c3a75a$0e3e6050$7502a8c0@hdsc.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200311/96
X-Sequence-Number: 15911

Kumar wrote:

> 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()+ 
> interval'n month';

What about:

select now() + n * '1 month'::intervall;


Regards
Gaetano Mendola