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