Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: nonsolosoft@diff.org, pgsql-sql@postgresql.org
Subject: Re: PL/SQL trouble
Date: Tue, 26 Nov 2002 10:51:18 +0000
User-Agent: KMail/1.4.3
References: <E18GbWF-0007oU-00@global.dnsprotect.com>
In-Reply-To: <E18GbWF-0007oU-00@global.dnsprotect.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200211261051.18844.dev@archonet.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) =3D 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=20
to_date that takes a date. You can also remove the timestamp casts:

select into var1 ($1::date -
  (case when extract(DOW from $1) =3D 0=20
    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=
=20
pinpoint errors more easily.

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