有没有办法在 Postgres 中禁用函数重载 [英] Is there a way to disable function overloading in Postgres

问题描述

我和我的用户不使用 PL/pgSQL 中的函数重载.每个(模式，名称)元组总是有一个函数.因此，我们只想按名称删除函数，更改其签名而不必先删除它，等等.例如，考虑以下函数:

创建或替换函数 myfunc(day_number SMALLINT)退货表(a INT)作为$BODY$开始返回查询(SELECT 1 as a)；结尾;$BODY$语言 plpgsql;

为了节省时间，我们想如下调用它，不用::SMALLINT限定1，因为只有一个名为myfunc的函数，而且它只有一个名为day_number的参数:

SELECT * FROM myfunc(day_number := 1)

没有歧义，值1与SMALLINT类型一致，但PostgreSQL报错:

SELECT * FROM myfunc(day_number := 1);

<块引用>

错误:函数 myfunc(day_number := integer) 不存在第 12 行:SELECT * FROM myfunc(day_number := 1);^提示:没有函数匹配给定的名称和参数类型.您可能需要添加显式类型转换.

当我们从 Python 调用这样的函数时，我们使用一个包装器来查找函数的签名并用类型限定参数.这种方法有效，但似乎有改进的潜力.

有没有办法完全关闭函数重载?

解决方案

这实际上不是直接的函数重载问题(这是不可能关闭"的).这是函数类型解析的问题.(当然，该算法在没有重载函数的情况下可以更宽容.)

所有这些都行得通:

SELECT * FROM myfunc(day_number := '1');SELECT * FROM myfunc('1');-- 注意引号SELECT * FROM myfunc(1::smallint);SELECT * FROM myfunc('1'::smallint);

为什么?

最后两个相当明显，你已经在你的问题中提到了.
前两个比较有趣，解释埋在Function Type分辨率:

<块引用>

假设未知文字可以为此目的转换为任何内容.

这应该是适合您的简单解决方案:使用字符串文字.

SQL 标准中定义的无类型文字 '1'(带引号)或字符串文字"在本质上与 类型 文字(或常量)不同.

数字常量1(不带引号)立即转换为数字类型.手册:

<块引用>

既不包含小数点也不包含小数点的数字常量指数最初假定为整数类型，如果其值适合输入 integer(32 位)；否则假定为 bigint 类型，如果它的值适合 bigint 类型(64 位)；否则被认为是输入数字.包含小数点和/或指数的常量最初总是假定为 numeric 类型.

初始分配的数值常量的数据类型只是一个类型解析算法的起点.在大多数情况下常量将被自动强制为最合适的类型视上下文而定.必要时，您可以强制一个数值通过强制转换将其解释为特定的数据类型.

粗体强调我的.

函数调用中的赋值(day_number := 1)是一个特例，此时day_number的数据类型unknown观点.Postgres 不能从这个赋值中派生数据类型，默认为 integer.

因此，Postgres 寻找一个采用 integer first 的函数.然后对于只采用隐式转换远离整数的类型的函数，换句话说:

SELECT casttarget::regtype从 pg_castWHERE castsource = 'int'::regtypeAND castcontext = 'i';

所有这些都会被找到——如果有多个函数，就会发生冲突.那将是函数重载，并且您会收到不同的错误消息.有两个这样的候选函数:

SELECT * FROM myfunc(1);

<块引用>

错误:函数 myfunc(integer) 不是唯一的

注意消息中的整数":数字常量已转换为integer.

然而，从 integer 到 smallint 的转换只是"一个赋值转换.这就是旅程结束的地方:

<块引用>

没有函数匹配给定的名称和参数类型.

SQL 小提琴.

在这些相关答案中有更详细的解释:

• PostgreSQL 错误: 函数 to_tsvector(字符变化，未知)不存在

• 生成日期系列- 使用日期类型作为输入

肮脏的修复

您可以通过将演员表从integer升级"到smallint 到来解决这个问题隐式转换:

更新 pg_castSET castcontext = 'i'WHERE castsource = 'int'::regtypeAND casttarget = 'int2'::regtype;

但我强烈反对篡改默认的投射系统.仅当您确切地知道自己在做什么时才考虑这一点.您可以在 Postgres 列表中找到相关讨论.它可以有各种各样的副作用，从函数类型解析开始，但还不止于此.

旁白

函数类型解析完全独立于使用的语言.SQL 函数将与 PL/perl 或 PL/pgSQL 或内部"函数竞争.函数签名是必不可少的.内置函数只排在第一位，因为 pg_catalog 在默认的 search_path 中排在第一位.

My users and I do not use function overloading in PL/pgSQL. We always have one function per (schema, name) tuple. As such, we'd like to drop a function by name only, change its signature without having to drop it first, etc. Consider for example, the following function:

CREATE OR REPLACE FUNCTION myfunc(day_number SMALLINT)
RETURNS TABLE(a INT)
AS
$BODY$
BEGIN
  RETURN QUERY (SELECT 1 AS a);
END;
$BODY$
LANGUAGE plpgsql;

To save time, we would like to invoke it as follows, without qualifying 1 with ::SMALLINT, because there is only one function named myfunc, and it has exactly one parameter named day_number:

SELECT * FROM myfunc(day_number := 1)

There is no ambiguity, and the value 1 is consistent with SMALLINT type, yet PostgreSQL complains:

SELECT * FROM myfunc(day_number := 1);

ERROR:  function myfunc(day_number := integer) does not exist
LINE 12: SELECT * FROM myfunc(day_number := 1);
                       ^
HINT:  No function matches the given name and argument types.
You might need to add explicit type casts.

When we invoke such functions from Python, we use a wrapper that looks up functions' signatures and qualifies parameters with types. This approach works, but there seems to be a potential for improvement.

Is there a way to turn off function overloading altogether?

解决方案

This is actually not directly a matter of function overloading (which would be impossible to "turn off"). It's a matter of function type resolution. (Of course, that algorithm could be more permissive without overloaded functions.)

All of these would just work:

SELECT * FROM myfunc(day_number := '1');
SELECT * FROM myfunc('1');               -- note the quotes

SELECT * FROM myfunc(1::smallint);
SELECT * FROM myfunc('1'::smallint);

Why?

The last two are rather obvious, you mentioned that in your question already.
The first two are more interesting, the explanation is buried in the Function Type Resolution:

unknown literals are assumed to be convertible to anything for this purpose.

And that should be the simple solution for you: use string literals.

An untyped literal '1' (with quotes) or "string literal" as defined in the SQL standard is different in nature from a typed literal (or constant).

A numeric constant 1 (without quotes) is cast to a numeric type immediately. The manual:

A numeric constant that contains neither a decimal point nor an exponent is initially presumed to be type integer if its value fits in type integer (32 bits); otherwise it is presumed to be type bigint if its value fits in type bigint (64 bits); otherwise it is taken to be type numeric. Constants that contain decimal points and/or exponents are always initially presumed to be type numeric.

The initially assigned data type of a numeric constant is just a starting point for the type resolution algorithms. In most cases the constant will be automatically coerced to the most appropriate type depending on context. When necessary, you can force a numeric value to be interpreted as a specific data type by casting it.

Bold emphasis mine.

The assignment in the function call (day_number := 1) is a special case, the data type of day_number is unknown at this point. Postgres cannot derive a data type from this assignment and defaults to integer.

Consequently, Postgres looks for a function taking an integer first. Then for functions taking a type only an implicit cast away from integer, in other words:

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

All of these would be found - and conflict if there were more than one function. That would be function overloading, and you would get a different error message. With two candidate functions like this:

SELECT * FROM myfunc(1);

ERROR:  function myfunc(integer) is not unique

Note the "integer" in the message: the numeric constant has been cast to integer.

However, the cast from integer to smallint is "only" an assignment cast. And that's where the journey ends:

No function matches the given name and argument types.

SQL Fiddle.

More detailed explanation in these related answers:

• PostgreSQL ERROR: function to_tsvector(character varying, unknown) does not exist

• Generate series of dates - using date type as input

Dirty fix

You could fix this by "upgrading" the cast from integer to smallint to an implicit cast:

UPDATE pg_cast
SET    castcontext = 'i'
WHERE  castsource = 'int'::regtype
AND    casttarget  = 'int2'::regtype;

But I would strongly discourage tampering with the default casting system. Only consider this if you know exactly what you are doing. You'll find related discussions in the Postgres lists. It can have all kinds of side effects, starting with function type resolution, but not ending there.

Aside

Function type resolution is completely independent from the used language. An SQL function would compete with PL/perl or PL/pgSQL or "internal" functions just the same. The function signature is essential. Built-in functions only come first, because pg_catalog comes first in the default search_path.

这篇关于有没有办法在 Postgres 中禁用函数重载的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！