From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | nonsolosoft(at)diff(dot)org, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: PL/SQL trouble |
Date: | 2002-11-26 10:51:18 |
Message-ID: | 200211261051.18844.dev@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Christoph Haller | 2002-11-26 11:55:36 | Re: PL/SQL trouble |
Previous Message | Richard Huxton | 2002-11-26 10:41:50 | Re: Are sub-select error suppressed? |