主页 / dba / 问题 / 271670
Accepted
Eugene Pakhomov
Asked: 2020-07-26 21:13:17 +0800 CST

PostgreSQL 在某些情况下会忽略隐式转换

  • 772

设置（仅在 PostgreSQL 9.6 上尝试过）：

CREATE TYPE MY_ENUM AS ENUM ('a', 'b');
CREATE CAST (CHARACTER VARYING AS MY_ENUM) WITH INOUT AS IMPLICIT;
CREATE TABLE t (x MY_ENUM);
INSERT INTO t VALUES ('a'::MY_ENUM), ('b'::MY_ENUM);

正如预期的那样，这些工作正常：

INSERT INTO t VALUES ('a');
SELECT * FROM t WHERE x = 'a';

但这些不会：

PREPARE p(CHARACTER VARYING) AS SELECT * FROM t WHERE x = $1;
;; error: operator does not exist: my_enum = character varying

CREATE FUNCTION f(ix CHARACTER VARYING, OUT ox MY_ENUM) AS
$$
    SELECT * FROM t WHERE x = ix
$$ LANGUAGE sql;
;; error: operator does not exist: my_enum = character varying

CREATE FUNCTION f(ix CHARACTER VARYING) RETURNS VOID AS
$$
BEGIN
    SELECT * FROM t WHERE input_type = ix;
END;
$$ LANGUAGE plpgsql;

SELECT f('piano');
;; error: operator does not exist: my_enum = character varying

根据文档：

如果强制转换标记为 AS IMPLICIT，则可以在任何上下文中隐式调用它，无论是赋值还是在表达式内部。

那么为什么会出现错误呢？

postgresql cast

1 个回答

  1. Best Answer

    区别在于：

    在前两个（工作）示例中，'a'is 只是一个无类型的字符串文字- 这与varchar. enum枚举类型的基本输入/输出函数提供了到您的自定义类型的转换。（系统目录中没有明确的条目pg_cast。） Postgres 确定数据类型的最佳匹配，因为用户没有提供。甚至不需要您的自定义演员表。既不用于类型解析，也不用于运算符解析。

    在以下示例中，您传递了一个类型化的值( varchar)。现在，您的自定义演员可能会派上用场。例如，如果没有您的自定义演员表，这将无法工作：

    INSERT INTO t VALUES ('a'::varchar);

    您的示例不是简单的分配，而是带有 operator 的表达式=。我们处于Operator Type Resolution的领域。第 2a 项规定：

    如果二元运算符调用的一个参数是未知类型，则假定它与此检查的另一个参数的类型相同。

    这将涵盖my_enum_column = 'foo'- 使用无类型文字。

    但是没有什么可以伸展到覆盖my_enum_column = 'foo'::varchar。似乎只有=左右操作数的通用运算符anyenum可用这一事实不足以在运算符解析中提供线索：

    -- missing operators:
SELECT oprleft::regtype, oprright::regtype, *
FROM   pg_operator
WHERE  oprname = '='
AND   ('anyenum'::regtype IN (oprleft, oprright) OR
       'my_enum'::regtype IN (oprleft, oprright));

     oprleft | oprright | ...
 --------+----------+----
 anyenum | anyenum  | ...
(1 row)

    您必须为此注册一个更明确的运算符。但我不会那样做。我会做类似的事情（还要注意各种正交语法修复）：

    PREPARE p(my_enum) AS SELECT * FROM t WHERE x = $1;  -- prepare with type my_enum
EXECUTE p('a'::varchar); -- then your cast kicks in
EXECUTE p('a'); -- untyped string literal works in any case

    CREATE FUNCTION f1(ix my_enum, OUT ox my_enum)   -- again, declare enum
  RETURNS SETOF my_enum AS
$$
SELECT x FROM t WHERE x = ix
$$ LANGUAGE sql;

SELECT * FROM f1('a'::varchar);

    CREATE FUNCTION f2(ix my_enum)  -- same here
  RETURNS SETOF t AS
$$
BEGIN
   RETURN QUERY
   SELECT * FROM t WHERE x = ix;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM f2('a'::varchar);
SELECT * FROM f2('a');

    db<>在这里摆弄

    或者只是添加明确的演员表。这是最简单和最安全的。手册警告（在您引用的同一页面上）：

    保守地将演员表标记为隐式是明智的。...

    还有更多 - 推荐阅读。

    如果您必须创建该自定义转换（甚至添加一个运算符），请考虑使用text而不是varchar，它是字符串类型中的“首选类型”，因此对于可能的极端情况问题更加稳健。

    • 2

相关问题