Date: Thu, 3 Apr 2008 13:09:19 +0200
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: BROBLEM IN BETWEEN QUERY (plpgsql)
Message-ID: <20080403110919.GF23029@a-kretschmer.de>
References: <1a027d210804030324x263be097x8be6f2fea6ca183d@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1a027d210804030324x263be097x8be6f2fea6ca183d@mail.gmail.com>
User-Agent: Mutt/1.5.9i

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+02
 t
---
(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