pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBROBLEM IN BETWEEN QUERY (plpgsql)
2+ messages / 2 participants
[nested] [flat]
* BROBLEM IN BETWEEN QUERY (plpgsql)
@ 2008-04-03 10:24 Anoop G <anoopmadavoor@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Anoop G @ 2008-04-03 10:24 UTC (permalink / raw)
To: pgsql-sql
hai all,
I have a plpgsql function and I am using postgresl 8.1.9
CREATE OR REPLACE FUNCTION get_vehicle_id(INT) RETURNS SETOF RECORD AS
$BODY$
DECLARE
r RECORD;
int_day ALIAS FOR $1;
BEGIN
FOR r in SELECT fk_bint_old_vehicle_number AS vehicle_id
FROM tbl_rac_vehicle_replacement
WHERE dat_replacement BETWEEN now() - interval '% day',int_day
AND now() LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END
$BODY$ LANGUAGE 'plpgsql';
But i cant craete the function The error message is:
ERROR: syntax error at or near "," at character 137
QUERY: SELECT fk_bint_old_vehicle_number AS vehicle_id FROM
tbl_rac_vehicle_replacement WHERE dat_replacement BETWEEN now() - interval
'% day', $1 AND now()
CONTEXT: SQL statement in PL/PgSQL function "test" near line 11
LINE 1: ...E dat_replacement BETWEEN now() - interval '% day', $1 AND ...
The problem line is BETWEEN now() - interval '% day', $1 AND now()
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 also tried like this WHERE dat_replacement BETWEEN now() - interval
int_day day
AND now(),but it also failed.
Please help me .
regards:
Anoop
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BROBLEM IN BETWEEN QUERY (plpgsql)
@ 2008-04-03 11:09 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Anoop G <anoopmadavoor@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: A. Kretschmer @ 2008-04-03 11:09 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2008-04-03 11:09 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-04-03 10:24 BROBLEM IN BETWEEN QUERY (plpgsql) Anoop G <anoopmadavoor@gmail.com>
2008-04-03 11:09 ` A. Kretschmer <andreas.kretschmer@schollglas.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