From: Anoop G <anoopmadavoor@gmail.com>
To: pgsql-sql@postgresql.org
Subject: BROBLEM IN BETWEEN QUERY (plpgsql)
Date: Thu, 3 Apr 2008 15:54:56 +0530
Message-ID: <1a027d210804030324x263be097x8be6f2fea6ca183d@mail.gmail.com> (raw)
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
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: anoopmadavoor@gmail.com
Subject: Re: BROBLEM IN BETWEEN QUERY (plpgsql)
In-Reply-To: <1a027d210804030324x263be097x8be6f2fea6ca183d@mail.gmail.com>
* 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