Обсуждение: Debugging pgsql function date error

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

On Thursday 28 May 2009 5:36:10 pm Rory Campbell-Lange wrote:
> 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

What is the code at line 30 ?

--
Adrian Klaver
aklaver@comcast.net