Datetime conversion in WHERE clause

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 = foo1.pk

WHERE
lower(foo1.text_data1) LIKE lower($1)
OR lower(foo1.text_data2) LIKE lower($1)
OR foo1.date_data = $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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: Datetime conversion in WHERE clause

Philippe,

> 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?

You'd need to write a custom function, using an external language that allows
you to test for valid date values. I prefer using PL/perlU with
Date::Manip, but use what you like.

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings