Type conversion with default fallback value

Question

In PostgreSQL (8.4), I'm trying to convert a string parameter into a date within a SQL query, falling back to now() when the string is not a valid date (or empty).

In "pseudo-SQL", this would be something like this:

SELECT CASE WHEN ? is not a valid date THEN now()::DATE ELSE CAST(? AS DATE) END;

I've tried to simplify the problem to detect an empty string using these two queries:

SELECT CASE WHEN ?='' THEN now()::DATE ELSE CAST(? AS DATE) END;
SELECT DATE(CASE WHEN ?='' THEN now() ELSE ?  END);

For example, if the parameter is '', this is equivalent to this:

SELECT CASE WHEN ''='' THEN now()::DATE ELSE CAST('' AS DATE) END;
SELECT DATE(CASE WHEN ''='' THEN now() ELSE ''  END);

Both fail with ERROR: invalid input syntax for type timestamp with time zone: "" It makes sense, but it implies that the ELSE block is evaluated (or at least that its types are resolved) whether or not the CASE condition is true. The following works, but what I'd like the CASE (or similar) condition to handle, is precisely the case when it's not a valid date.

SELECT CASE WHEN '2011-12-01'='' THEN now()::DATE ELSE CAST('2011-12-01' AS DATE) END;

The closest I got to a working solution is this:

SELECT DATE(COALESCE(NULLIF(?, '')::timestamptz, now()));

In this case, if the parameter is '', it returns the current date, otherwise, it returns the date passed in the string parameter (provided it can be turned into a valid date).

What I would like is to go a step further and make anything that cannot be turned into a DATE use the current date. I guess this could be done using a custom PL/pgSQL function that would trap this error, but can this be done without such a function, in "plain" SQL (or at least using the existing PostgreSQL functions)?

Answer 1

You can't handle SQL exceptions within an SQL statement like you can from within PL/pgSQL.

You must write a custom function as you suggest, for example:

create function is_valid_date(text) returns boolean language plpgsql immutable as $$
begin
  return case when $1::date is null then false else true end;
exception when others then
  return false;
end;$$;

test:

with w as (select 'asdsad'::text dt1, '2011-12-01'::text dt2, null::text dt3)
select case when is_valid_date(dt1) then dt1::date else current_date end d1,
       case when is_valid_date(dt2) then dt2::date else current_date end d2,
       case when is_valid_date(dt3) then dt3::date else current_date end d3
from w;

     d1     |     d2     |     d3
------------+------------+------------
 2011-12-06 | 2011-12-01 | 2011-12-06