PostgreSQL - 自动转换类型？

Question

我正致力于将数据库从 Firebird 移植到 PostgreSQL，并且有很多与类型转换相关的错误。例如，让我们来看一个简单的函数:

CREATE OR REPLACE FUNCTION f_Concat3 (
  s1 varchar, s2 varchar, s3 varchar
)
RETURNS varchar AS
$body$
BEGIN
   return s1||s2||s3;
END;
$body$ LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER LEAKPROOF COST 100;

由于 Firebird 对类型非常灵活，因此调用此函数的方式不同:一些参数可能是另一种类型:整数/ double /时间戳。当然，在 Postgres 函数中调用 f_Concat3 ('1', 2, 345.345) 会导致如下错误:

function f_Concat3(unknown, integer, numeric) not found.

建议文档使用显式转换，例如:

f_Concat3 ('1'::varchar, 2::varchar, 345.345::varchar) 

我还可以为所有可能发生的类型组合创建一个函数克隆，它会起作用。解决错误的示例:

CREATE OR REPLACE FUNCTION f_Concat3 (
   s1 varchar, s2 integer, s3 numeric
)
RETURNS varchar AS
$body$
  BEGIN
    return s1::varchar||s2::varchar||s3::varchar;
  END;

然而，这是非常糟糕和丑陋的，它不能用于大函数。

重要提示:我们有一个适用于所有数据库的通用代码库，并使用我们自己的语言来创建包含选择查询的应用程序对象(表单、报告等)。不可能在函数调用上使用显式转换，因为我们将失去与其他数据库的兼容性。

我很困惑整数参数不能转换为 numeric 或 double precision，或 date/number 到 string。我什至遇到 integer 到 smallint 的问题，反之亦然。大多数数据库不是这样的。

对于这种情况有什么最佳实践吗？
是否有显式转换的替代方案？

Answer 1

SQL 是一种类型化语言，PostgreSQL 比其他关系数据库更重视这一点。不幸的是，这意味着在移植编码草率的应用程序时要付出额外的努力。

很有诱惑力add implicit casts , 但是 the documentation警告您不要在内置数据类型之间创建转换:

Additional casts can be added by the user with the CREATE CAST command. (This is usually done in conjunction with defining new data types. The set of casts between built-in types has been carefully crafted and is best not altered.)

这不是空闲警告，因为 function resolution如果您在现有类型之间创建新的转换，其他事情可能会突然失败或行为不端。

我认为，如果您真的不想清理代码(这将使它在未来更具可移植性)，您别无选择，只能添加更多版本的函数。

幸运的是 PostgreSQL 有 function overloading这使得这成为可能。

您可以通过将一个参数与 polymorphic type 结合使用来简化工作在你的函数定义中，像这样:

CREATE OR REPLACE FUNCTION f_concat3 (
   s1 text, s2 integer, s3 anyelement
) RETURNS text
   LANGUAGE sql IMMUTABLE LEAKPROOF AS
'SELECT f_concat3(s1, s2::text, s3::text)';

不过，您不能使用多个 anyelement 参数，因为只有当所有此类参数都属于同一类型时才有效。

如果您使用函数重载，请注意不要产生会使函数解析失败的歧义。