From: A. Kretschmer <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: BROBLEM IN BETWEEN QUERY (plpgsql)
Date: Thu, 3 Apr 2008 13:09:19 +0200
Message-ID: <20080403110919.GF23029@a-kretschmer.de> (raw)
In-Reply-To: <1a027d210804030324x263be097x8be6f2fea6ca183d@mail.gmail.com>
References: <1a027d210804030324x263be097x8be6f2fea6ca183d@mail.gmail.com>
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...
HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas.kretschmer@schollglas.com
Subject: Re: BROBLEM IN BETWEEN QUERY (plpgsql)
In-Reply-To: <20080403110919.GF23029@a-kretschmer.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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