主页 / dba / 问题 / 203934
Accepted
Manngo
Asked: 2018-04-14 22:20:07 +0800 CST

PostgreSQL 替代 SQL Server 的 `try_cast` 函数

  • 772

Microsoft SQL Server 有一个我认为非常明智的函数，如果强制转换不成功，try_cast()它会返回 a null，而不是引发错误。

这使得可以使用CASE表达式或 acoalesce来回退。例如：

SELECT coalesce(try_cast(data as int),0);

问题是，PostgreSQL 有类似的东西吗？

提出这个问题是为了填补我的知识空白，但也有一般原则，即有些人更喜欢对某些用户错误做出不那么戏剧性的反应。null在 SQL 中返回 a比返回错误更容易。例如SELECT * FROM data WHERE try_cast(value) IS NOT NULL;. 根据我的经验，如果有 B 计划，有时会更好地处理用户错误。

postgresql datatypes

6 个回答

  1. 基本原理

    很难将SQL Server 之TRY_CAST类的东西包装到通用 PostgreSQL 函数中。输入和输出可以是任何数据类型，但 SQL 是严格类型的，并且 Postgres 函数要求在创建时声明参数和返回类型。

    Postgres 有多态类型的概念，但函数声明最多接受一种多态类型。手册：

    多态参数和结果相互关联，并在解析调用多态函数的查询时解析为特定的数据类型。声明为的每个位置（参数或返回值）anyelement都允许具有任何特定的实际数据类型，但在任​​何给定的调用中，它们都必须是相同的实际类型。

    CAST ( expression AS type )似乎是该规则的一个例外，采用任何类型并返回任何（其他）类型。但它cast()只是看起来像一个函数，而它实际上是一个SQL 语法元素。手册：

    [...] 当使用两种标准强制转换语法之一进行运行时转换时，它将在内部调用一个注册函数来执行转换。

    输入和输出类型的每种组合都有一个单独的函数。（您可以使用...创建自己的CREATE CAST）

    功能

    我的妥协是text用作输入，因为任何类型都可以转换为text. 额外的演员text意味着额外的成本（虽然不多）。多态性也增加了一些开销。但成本适中的部分是我们需要的动态 SQL、涉及的字符串连接以及最重要的异常处理。

    也就是说，这个小函数可以用于任何类型的组合，包括数组类型。（但是像 in 这样的类型修饰符varchar(20)丢失了）：

    CREATE OR REPLACE FUNCTION try_cast(_in text, INOUT _out ANYELEMENT)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('SELECT %L::%s', $1, pg_typeof(_out))
   INTO  _out;
EXCEPTION WHEN others THEN
   -- do nothing: _out already carries default
END
$func$;

    该INOUT参数_out有两个用途：

    1. 声明多态类型
    2. 还带有错误情况的默认值

    你不会像在你的例子中那样称呼它：

    SELECT coalesce(try_cast(data as int),0);

    .. whereCOALESCE还从源中消除了真正的 NULL 值（!!），可能不是预期的。但简单地说：

    SELECT try_cast(data, 0);

    ..返回输入或无效输入NULL。NULL0

    短语法适用 whiledata是字符类型（如textor varchar），因为0是隐式类型为 的数字文字integer。在其他情况下，您可能必须更明确：

    示例调用

    无类型的字符串文字开箱即用：

    SELECT try_cast('foo', NULL::varchar);
SELECT try_cast('2018-01-41', NULL::date);   -- returns NULL
SELECT try_cast('2018-01-41', CURRENT_DATE); -- returns current date

    具有已注册隐式转换的类型值text也可以开箱即用：

    SELECT try_cast(name 'foobar', 'foo'::varchar);
SELECT try_cast(my_varchar_column, NULL::numeric);

    已注册隐式强制转换为的数据类型的综合列表text：

    SELECT castsource::regtype
FROM   pg_cast
WHERE  casttarget = 'text'::regtype
AND    castcontext = 'i';

    所有其他输入类型都需要显式转换为text：

    SELECT try_cast((inet '192.168.100.128/20')::text, NULL::cidr);
SELECT try_cast(my_text_array_column::text, NULL::int[]));

    我们可以轻松地使函数体适用于任何类型，但函数类型解析失败。有关的：

    • 23
  2. Best Answer

    如果从一种特定类型转换为另一种特定类型就足够了，您可以使用 PL/pgSQL 函数执行此操作：

    create function try_cast_int(p_in text, p_default int default null)
   returns int
as
$$
begin
  begin
    return $1::int;
  exception 
    when others then
       return p_default;
  end;
end;
$$
language plpgsql;

    然后

    select try_cast_int('42'), try_cast_int('foo', -1), try_cast_int('bar')

    退货

    try_cast_int | try_cast_int | try_cast_int
-------------+--------------+-------------
          42 |           -1 |

    如果这仅适用于数字，另一种方法是使用正则表达式来检查输入字符串是否为有效数字。当您期望许多不正确的值时，这可能比捕获异常更快。

    • 16

  3. 这是一个通用的 try-cast，可能非常慢。

    CREATE OR REPLACE FUNCTION try_cast(p_in text, type regtype, out result text )
RETURNS text AS $$
  BEGIN
    EXECUTE FORMAT('SELECT %L::%s;', $1, $2)
      INTO result;
exception 
    WHEN others THEN result = null;
  END;
$$ LANGUAGE plpgsql;

 SELECT try_cast('2.2','int')::int as "2.2"
   ,try_cast('today','int')::int as "today"
   ,try_cast('222','int')::int as "222";

 SELECT try_cast('2.2','date')::date as "2.2"
   ,try_cast('today','date')::date as "today"
   ,try_cast('222','date')::date as "222";

 SELECT try_cast('2.2','float')::float as "2.2"
   ,try_cast('today','float')::float as "today"
   ,try_cast('222','float')::float as "222";

    这不会接受类似的类型varchar(20)（尽管我们可以添加另一个参数来接受“typemod” like 20.

    此函数返回文本，因为 postgreqsl 函数必须具有固定的返回类型。因此您可能需要在函数之外进行显式强制转换，以将结果强制转换为您想要的类型。

    • 1

  4. 稍有不同的通用版本 - 这将返回 true 或 false，具体取决于是否可以转换该值。也适用于用户定义的域。这适用于 PostgreSQL 12。

    CREATE OR REPLACE FUNCTION appcode.try_cast (p_in TEXT, p_type VARCHAR(128))
RETURNS BOOLEAN
AS
$$
       /*
       Accepts a text value, and a data type; returns true if the
       text value can be cast to the data type, false otherwise
       */
       DECLARE
               valid_cast BOOLEAN = TRUE;
               tmp_val TEXT;
               
       BEGIN 
               -- clean up the input
               p_type = TRIM(LOWER(p_type));
               
               EXECUTE FORMAT('SELECT CAST (%s AS %s)', p_in, p_type)
               INTO tmp_val;
               -- Check for same length, because casts can sometimes truncate 
               -- values; e.g. cast 'ABC' to VARCHAR(1) would result in 'A'
               IF LENGTH(tmp_val) <> LENGTH(p_in)
               THEN
                       valid_cast = FALSE;
               END IF;
               RETURN valid_cast;
                          
               EXCEPTION
                       WHEN OTHERS THEN
                               valid_cast = FALSE;
                               
END;
$$
LANGUAGE plpgsql;
    • 0

  5. 我已经接受了一个答案，但我想我可能会为后代添加这个。这是我在工作中使用的版本：

    DROP FUNCTION IF EXISTS cast_int;
CREATE FUNCTION cast_int(string varchar, planB int default null) RETURNS INT AS $$
    BEGIN
        RETURN floor(cast(string as numeric));
    EXCEPTION
        WHEN OTHERS THEN return planB;
    END
$$ LANGUAGE plpgsql;

    这基本上来自a_horse_with_no_name 上面的答案，但只是更巩固了一点。

    MSSQLTRY_CAST在数据类型方面更为通用，就像CAST函数本身一样。此版本需要针对不同数据类型使用不同的函数，例如cast_date. 您可以扩展它以自己检查数据类型，但这可能太过分了。

    • 0

  6. 使用 PostgreSQL，当强制执行失败时，它会在内部使用ereport. 这在强制中是无法恢复的。

    样本数据

    假设错误率为 1/5

    CREATE TABLE foo AS
  SELECT CASE WHEN x%5=0 THEN 'a' ELSE x::text END
  FROM generate_series(0,1e6) AS gs(x);

    将不良数据列入黑名单。

    这个问题的正常解决方案是在创建类型时自由地接受。这几乎就是现在的工作方式。如果您需要防止某种虚假输入，而不是在失败的情况下捕获，只需在失败之前将其设置为 null。

    SELECT NULLIF(x, 'a')::int
FROM ( VALUES ('6'),('a'),('7') ) AS t(x);

    如果您愿意，也可以将其放入IMMUTABLESQL 语句中。

    CREATE FUNCTION safer_but_not_totally_safe_coercion( i text )
RETURNS int AS $$
  SELECT NULLIF(i, 'a')::int;
$$ LANGUAGE sql
IMMUTABLE;

-- Inlined and fast.
SELECT safer_but_not_totally_safe_coercion(x, 'a')::int
FROM ( VALUES ('6'),('a'),('7') ) AS t(x);

    就验证而言，您还可以使用正则表达式和您想要的任何其他内容。

    EXPLAIN ANALYZE SELECT safer_but_not_totally_safe_coercion(x) FROM foo;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..21925.02 rows=1000001 width=5) (actual time=0.025..210.685 rows=1000001 loops=1)
 Planning time: 0.173 ms
 Execution time: 240.462 ms
(3 rows)

    试着抓

    这种方法很慢。

    EXPLAIN ANALYZE SELECT try_cast_int(x) FROM foo;
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..264425.26 rows=1000001 width=5) (actual time=0.104..7069.281 rows=1000001 loops=1)
 Planning time: 0.056 ms
 Execution time: 7151.917 ms
(3 rows)

    如果你需要它，那么无论如何你都需要它，但它不会是我抢的第一个工具。

    • -2

相关问题