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
