From: | Rory Campbell-Lange <rory(at)campbell-lange(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Debugging pgsql function date error |
Date: | 2009-05-29 00:36:10 |
Message-ID: | 20090529003610.GA3405@campbell-lange.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have a long plpgsql function which takes a date as one of its
arguments but I am receiving an error for another date! I'm using 8.3 on
Debian.
CREATE OR REPLACE FUNCTION
fn_alert01_maker(integer, integer, integer, integer, date, integer)
RETURNS SETOF alert_info
AS $$
DECLARE
userid ALIAS for $1;
alert ALIAS for $2;
since ALIAS for $3;
wait ALIAS for $4;
date_from ALIAS for $5;
days_until ALIAS for $6;
resulter alert_info%rowtype;
since_int INTERVAL;
wait_int INTERVAL;
days_int INTERVAL;
BEGIN
days_int := days_until || ' days';
since_int := since || ' seconds';
wait_int := wait || ' seconds';
...
and has that argument as one of its WHERE conditions:
...
WHERE
rs.dt_modified > ((now() - wait_int) - since_int)
AND
rs.d_date >= date_from
AND
rs.d_date <= date_from + days_int
...
Wierdly, if I call it like this, it is fine:
SELECT * FROM fn_alert01_maker(0, 1, 845, 5, '2009-05-31', 5);
But like this it throws an error:
test=> SELECT * FROM fn_alert01_maker(0, 1, 8450, 5, '2009-05-31', 5);
ERROR: invalid input syntax for integer: "2009-06-01"
CONTEXT: PL/pgSQL function "fn_alert01_maker" line 30 at FOR over
SELECT rows
Any ideas?
Rory
From | Date | Subject | |
---|---|---|---|
Next Message | Douglas Alan | 2009-05-29 00:45:12 | Re: What is the right way to deal with a table with rows that are not in a random order? |
Previous Message | Flavio Henrique Araque Gurgel | 2009-05-29 00:26:32 | Re: Continuent (was: Postgres Clustering) |