BROBLEM IN BETWEEN QUERY (plpgsql)

From: "Anoop G" <anoopmadavoor(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: BROBLEM IN BETWEEN QUERY (plpgsql)
Date: 2008-04-03 10:24:56
Message-ID: 1a027d210804030324x263be097x8be6f2fea6ca183d@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message A. Kretschmer 2008-04-03 11:09:19 Re: BROBLEM IN BETWEEN QUERY (plpgsql)
Previous Message Ivan Sergio Borgonovo 2008-04-03 08:53:36 Re: update with join