有一种方法来禁用函数重载在Postgres [英] Is there a way to disable function overloading in Postgres

问题描述

我的用户和我不使用PL / pgSQL中的函数重载。我们总是有一个函数per（schema，name）元组。因此，我们希望仅通过名称删除函数，更改其签名而不必首先删除它。例如，考虑以下函数：

  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; 
  

为了节省时间，我们打算如下调用它， c> :: SMALLINT ，因为只有一个名为myfunc的函数，它只有一个名为day_number的参数：

  SELECT * FROM myfunc（day_number：= 1）
  

没有歧义，值1与 SMALLINT 类型一致，但PostgreSQL抱怨：

  SELECT * FROM myfunc（day_number：= 1）; 
  

 错误：function myfunc day_number：= integer）不存在
 LINE 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）;  

为什么？

最后两个很明显，你已经在你的问题中提到过了。

前两个更有趣，解释埋在 功能类型解析 ：

未知文字假设可以转换为任何目的。

这应该是简单解决方案：使用字符串文字。

无类型的字面值$ c>'1'，带引号）或SQL标准中定义的字符串字面值与其他类型的字面值（或常量）不同。

数字常数（ 1 ，不含引号）立即强制转换为数字类型 。 根据文档：

一个既不包含小数点也不包含
指数的数字常量最初假定为整型

strong>如果它的值适合
类型 integer （32位）;否则假定为 bigint 如果
其值适合类型 bigint （64位）;否则取为
类型 numeric 。包含小数点和/或指数的常数
始终最初假设为 numeric 。

数字常量的初始分配数据类型只是类型分辨率算法的
起点。在大多数情况下，
常量会根据上下文自动强制转换为最合适的类型
。必要时，您可以强制将
的数值转换为特定的数据类型。

函数调用中的赋值（ day_number：= 1 ）是一种特殊情况，数据类型的 day_number 未知。 Postgres不能从这个赋值中导出数据类型，默认为 integer 。

整数 。然后，对于仅从整数中隐式转换的类型的函数，换句话说：

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

将找到所有这些 - 如果有多个函数，则会发生冲突。 这将是函数重载 ，您会收到不同的错误消息。有两个候选函数如下：

  SELECT * FROM myfunc（1）; 
  

 错误：function myfunc整数）不唯一
  

请注意消息中的整数 ：数字常量已转换为 integer 。

但是，从 到 smallint 是仅限分配投放。这就是旅程结束的地方：

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

SQL Fiddle。

：

• PostgreSQL错误：函数to_tsvector（字符变化，未知）不存在




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

脏修复

<您可以 通过将演员从整数升级到 smallint 到隐式转换：

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

但我会强烈不鼓励篡改默认投放系统。只有当你知道你在做什么时才考虑这个。你会在Postgres列表中找到相关的讨论。

Aside

函数类型分辨率与所使用的语言完全独立。一个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 any other typed literal (or constant).

A numeric constant (1, without quotes) is cast to a numeric type immediately. Per documentation:

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屋！