MIME-Version: 1.0
Subject: Datetime conversion in WHERE clause
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
content-class: urn:content-classes:message
Date: Thu, 9 Sep 2004 16:23:10 +0200
Message-ID: <6C0CF58A187DA5479245E0830AF84F420803F0@poweredge.attiksystem.ch>
Thread-Topic: Datetime conversion in WHERE clause
Thread-Index: AcSWeIh0NYCgHEBETreDd/CeOFtmMw==
From: "Philippe Lang" <philippe.lang@attiksystem.ch>
To: <pgsql-sql@postgresql.org>

Hello,

I'm converting a varchar to a date in a search routine which looks like:

----------------------------------------------
CREATE FUNCTION public.search_data(varchar)
  RETURNS SETOF foo1 AS
'
  SELECT DISTINCT

  foo1.*

  FROM foo1
  LEFT JOIN foo2
  ON foo2.fk =3D foo1.pk

  WHERE
     lower(foo1.text_data1) LIKE lower($1)
  OR lower(foo1.text_data2) LIKE lower($1)
  OR foo1.date_data =3D $1::text:date

  OR lower(foo2.text_data3) LIKE lower($1)
  OR lower(foo2.text_data4) LIKE lower($1)
'
  LANGUAGE 'sql' VOLATILE;
----------------------------------------------

The problem with the ::text::date conversion is that the whole function
fails if the search string parameter cannot be converted into a valid
date, like 'test%', for example, which is valid for the other text
fields.

Is there a way to force the date conversion to fail sliently, and simply
return a null in case the parameter is not a valid date?

Thanks

Philippe Lang