Re: Dynamic Query for System functions - now() - Mailing list 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
>