From: Richard Huxton <dev@archonet.com>
To: nonsolosoft@diff.org
To: pgsql-sql@postgresql.org
Subject: Re: PL/SQL trouble
Date: Tue, 26 Nov 2002 10:51:18 +0000
Message-ID: <200211261051.18844.dev@archonet.com> (raw)
In-Reply-To: <E18GbWF-0007oU-00@global.dnsprotect.com>
References: <E18GbWF-0007oU-00@global.dnsprotect.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, nonsolosoft@diff.org
Subject: Re: PL/SQL trouble
In-Reply-To: <200211261051.18844.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox