主页 / dba / 问题 / 8833
Accepted
Bruno
Asked: 2011-12-07 06:17:43 +0800 CST

使用默认后备值进行类型转换

  • 772

在 PostgreSQL (8.4) 中，我试图将字符串参数转换为 SQL 查询中的日期，now()当字符串不是有效日期（或为空）时回退。

在“伪 SQL”中，这将是这样的：

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

我尝试使用这两个查询来简化问题以检测空字符串：

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

例如，如果参数是''，则等效于：

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

两者都失败了ERROR: invalid input syntax for type timestamp with time zone: "" It 是有道理的，但这意味着无论条件是否为真ELSE，都会评估块（或至少解析其类型） 。CASE以下有效，但我希望CASE（或类似）条件处理的情况恰恰是它不是有效日期的情况。

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

我最接近工作解决方案的是：

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

在这种情况下，如果参数为''，则返回当前日期，否则返回字符串参数中传递的日期（前提是可以转换为有效日期）。

我想要更进一步，让任何不能变成DATE使用的东西成为当前日期。我想这可以使用会捕获此错误的自定义 PL/pgSQL 函数来完成，但是如果没有这样的函数，可以在“普通”SQL（或至少使用现有的 PostgreSQL 函数）中完成吗？

postgresql type-conversion

1 个回答

  1. Best Answer

    您不能像在 PL/pgSQL中那样处理SQL 语句中的SQL 异常。

    您必须按照您的建议编写自定义函数，例如：

    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;$$;

    测试：

    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
    • 8

相关问题