Re: BROBLEM IN BETWEEN QUERY (plpgsql)

am  Thu, dem 03.04.2008, um 15:54:56 +0530 mailte Anoop G folgendes:
> hai all,
> I want to select  dat_replacement  between now ()  and now - 5 dyas or now -7
> days like that  i want to pass the integer value as argument to the function.

I show you a similar solution:

test=*# select * from foo;i |              ts
---+-------------------------------1 | 2008-03-31 15:11:36.214272+02
(1 row)

test=*# create or replace function f1(in i int, out t timestamptz) returns setof timestamptz as $$
declare r record;s timestamptz;
begin s:=current_timestamp-i * '1day'::interval;  for r in select * from foo where ts between s and current_timestamp
loop   raise notice '--> %',$1;   t:=r.ts;    return next;  end loop;  raise notice '%',s;
 
end;
$$ language plpgsql;
CREATE FUNCTION
test=*# select * from f1(1);
NOTICE:  2008-04-02 13:05:08.48866+02t
---
(0 rows)

test=*# select * from f1(10);
NOTICE:  --> 10
NOTICE:  2008-03-24 13:05:08.48866+01              t
-------------------------------2008-03-31 15:11:36.214272+02
(1 row)

More examples with IN/OUT - parameters:
http://people.planetpostgresql.org/xzilla/index.php?/archives/149-out-parameter-sql-plpgsql-examples.html#extended


HTH, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net