From: | "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Datetime conversion in WHERE clause |
Date: | 2004-09-09 14:23:10 |
Message-ID: | 6C0CF58A187DA5479245E0830AF84F420803F0@poweredge.attiksystem.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Passynkov, Vadim | 2004-09-09 15:01:38 | Re: Isnumeric function? |
Previous Message | Jeff Eckermann | 2004-09-09 14:02:06 | Re: Isnumeric function? |