pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDynamic Query for System functions - now()
8+ messages / 6 participants
[nested] [flat]
* Dynamic Query for System functions - now()
@ 2003-11-10 07:12 Kumar <sgnerd@yahoo.com.sg>
0 siblings, 3 replies; 8+ messages in thread
From: Kumar @ 2003-11-10 07:12 UTC (permalink / raw)
To: pgsql-sql
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';
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 function.
Regards
Kumar
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Dynamic Query for System functions - now()
@ 2003-11-10 11:37 Christoph Haller <ch@rodos.fzk.de>
parent: Kumar <sgnerd@yahoo.com.sg>
2 siblings, 1 reply; 8+ messages in thread
From: Christoph Haller @ 2003-11-10 11:37 UTC (permalink / raw)
To: sgnerd@yahoo.com.sg; +Cc: pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Dynamic Query for System functions - now()
@ 2003-11-10 11:57 Kumar <sgnerd@yahoo.com.sg>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 2 replies; 8+ messages in thread
From: Kumar @ 2003-11-10 11:57 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Dynamic Query for System functions - now()
@ 2003-11-10 12:18 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Kumar <sgnerd@yahoo.com.sg>
1 sibling, 1 reply; 8+ messages in thread
From: Achilleus Mantzios @ 2003-11-10 12:18 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; +Cc: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Dynamic Query for System functions - now()
@ 2003-11-10 13:55 George Weaver <georgew1@mts.net>
parent: Kumar <sgnerd@yahoo.com.sg>
1 sibling, 0 replies; 8+ messages in thread
From: George Weaver @ 2003-11-10 13:55 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Dynamic Query for System functions - now()
@ 2003-11-10 14:05 Christoph Haller <ch@rodos.fzk.de>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 8+ messages in thread
From: Christoph Haller @ 2003-11-10 14:05 UTC (permalink / raw)
To: achill@matrix.gatewaynet.com; +Cc: sgnerd@yahoo.com.sg; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Dynamic Query for System functions - now()
@ 2003-11-10 15:30 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Kumar <sgnerd@yahoo.com.sg>
2 siblings, 0 replies; 8+ messages in thread
From: Stephan Szabo @ 2003-11-10 15:30 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Dynamic Query for System functions - now()
@ 2003-11-10 19:12 Gaetano Mendola <mendola@bigfoot.com>
parent: Kumar <sgnerd@yahoo.com.sg>
2 siblings, 0 replies; 8+ messages in thread
From: Gaetano Mendola @ 2003-11-10 19:12 UTC (permalink / raw)
To: pgsql-sql; +Cc: Kumar <sgnerd@yahoo.com.sg>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-11-10 19:12 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-10 07:12 Dynamic Query for System functions - now() Kumar <sgnerd@yahoo.com.sg>
2003-11-10 11:37 ` Christoph Haller <ch@rodos.fzk.de>
2003-11-10 11:57 ` Kumar <sgnerd@yahoo.com.sg>
2003-11-10 12:18 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-11-10 14:05 ` Christoph Haller <ch@rodos.fzk.de>
2003-11-10 13:55 ` George Weaver <georgew1@mts.net>
2003-11-10 15:30 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-11-10 19:12 ` Gaetano Mendola <mendola@bigfoot.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox