Re: PL/SQL trouble

On Tuesday 26 Nov 2002 8:56 am, Ferruccio Zamuner wrote:
> CREATE FUNCTION MONDAY(timestamp) RETURNS DATE AS '
>  DECLARE
>   var1 date;
>  BEGIN
>   select into var1 to_date($1::date-(case when extract(DOW from
> timestamp $1) = 0 then 6 else (extract(DOW from timestamp $1)-1) end));
>   RETURN var1;
>  END'
> language 'plpgsql';

The problem is the to_date(...) - the value is already a date so there isn't a
to_date that takes a date. You can also remove the timestamp casts:

select into var1 ($1::date - (case when extract(DOW from $1) = 0    then 6   else (extract(DOW from $1) - 1 ) end )
);

If you put your function in a text file and create it with psql -f you can
pinpoint errors more easily.

In this case, the $2 was complaining about the second (expected) paramater to
to_date I think.
--  Richard Huxton