Postgresql 中文操作指南

10.3. Functions #

函数调用所引用的特定函数使用以下过程来确定。

The specific function that is referenced by a function call is determined using the following procedure.

Function Type Resolution

注意，对于运算符和函数类型解决方案，“最佳匹配”规则相同。下面是某些示例。

Note that the “best match” rules are identical for operator and function type resolution. Some examples follow.

Example 10.6. Rounding Function Argument Type Resolution

只有一个 round 函数需要两个参数；它的第一个参数的类型为 numeric，第二个参数的类型为 integer。因此，以下查询自动将类型 integer 的第一个参数转换为 numeric：

There is only one round function that takes two arguments; it takes a first argument of type numeric and a second argument of type integer. So the following query automatically converts the first argument of type integer to numeric:

SELECT round(4, 4);

 round
--------
 4.0000
(1 row)

该查询实际上由解析器转换为：

That query is actually transformed by the parser to:

SELECT round(CAST (4 AS numeric), 4);

由于带有小数点的数字常量最初被指定类型 numeric，因此以下查询不需要类型转换，因而可能略微高效一些：

Since numeric constants with decimal points are initially assigned the type numeric, the following query will require no type conversion and therefore might be slightly more efficient:

SELECT round(4.0, 4);

Example 10.7. Variadic Function Resolution

CREATE FUNCTION public.variadic_example(VARIADIC numeric[]) RETURNS int
  LANGUAGE sql AS 'SELECT 1';
CREATE FUNCTION

此函数接受 VARIADIC 关键字，但不要求它。它容忍整数和数字参数：

This function accepts, but does not require, the VARIADIC keyword. It tolerates both integer and numeric arguments:

SELECT public.variadic_example(0),
       public.variadic_example(0.0),
       public.variadic_example(VARIADIC array[0.0]);
 variadic_example | variadic_example | variadic_example
------------------+------------------+------------------
                1 |                1 |                1
(1 row)

但是，第一个和第二个调用会优先使用更具体的函数（如果可用）：

However, the first and second calls will prefer more-specific functions, if available:

CREATE FUNCTION public.variadic_example(numeric) RETURNS int
  LANGUAGE sql AS 'SELECT 2';
CREATE FUNCTION

CREATE FUNCTION public.variadic_example(int) RETURNS int
  LANGUAGE sql AS 'SELECT 3';
CREATE FUNCTION

SELECT public.variadic_example(0),
       public.variadic_example(0.0),
       public.variadic_example(VARIADIC array[0.0]);
 variadic_example | variadic_example | variadic_example
------------------+------------------+------------------
                3 |                2 |                1
(1 row)

考虑到默认配置并且仅存在第一个函数，第一个和第二个调用是不安全的。任何用户都可以通过创建第二个或第三个函数来拦截它们。通过精确匹配参数类型并使用 VARIADIC 关键字，第三个调用是安全的。

Given the default configuration and only the first function existing, the first and second calls are insecure. Any user could intercept them by creating the second or third function. By matching the argument type exactly and using the VARIADIC keyword, the third call is secure.

Example 10.8. Substring Function Type Resolution

有几个 substr 函数，其中一个函数接受类型 text 和 integer。如果使用未指定类型的字符串常量调用该函数，那么系统会选择接受首选类别（即类型 text）的函数候选项。

There are several substr functions, one of which takes types text and integer. If called with a string constant of unspecified type, the system chooses the candidate function that accepts an argument of the preferred category string (namely of type text).

SELECT substr('1234', 3);

 substr
--------
     34
(1 row)

如果将该字符串声明为类型 varchar，比如可能来自一张表时，那么解析器将尝试将其转换为 text：

If the string is declared to be of type varchar, as might be the case if it comes from a table, then the parser will try to convert it to become text:

SELECT substr(varchar '1234', 3);

 substr
--------
     34
(1 row)

这将由解析器转换为实际地成为：

This is transformed by the parser to effectively become:

SELECT substr(CAST (varchar '1234' AS text), 3);

Note

解析器从 pg_cast 目录了解到 text 和 varchar 是二进制兼容的，这意味着可以将一个传递给一个接受另一个的函数，而无需执行任何物理转换。因此，在这种情况下不会真正插入类型转换调用。

The parser learns from the pg_cast catalog that text and varchar are binary-compatible, meaning that one can be passed to a function that accepts the other without doing any physical conversion. Therefore, no type conversion call is really inserted in this case.

而且，如果使用类型 integer 的参数调用该函数，解析器将尝试将其转换为 text：

And, if the function is called with an argument of type integer, the parser will try to convert that to text:

SELECT substr(1234, 3);
ERROR:  function substr(integer, integer) does not exist
HINT:  No function matches the given name and argument types. You might need
to add explicit type casts.

这不起作用，因为 integer 没有 text 的隐式强制转换。但是，显式强制转换会起作用：

This does not work because integer does not have an implicit cast to text. An explicit cast will work, however:

SELECT substr(CAST (1234 AS text), 3);

 substr
--------
     34
(1 row)

[10 ] 如果搜索路径含允许不受信任用户创建对象的模式，则不会出现非模式限定名称的危险，因为它不是 secure schema usage pattern 。

[10] The hazard does not arise with a non-schema-qualified name, because a search path containing schemas that permit untrusted users to create objects is not a secure schema usage pattern.

[11 ] 执行此步骤的目的是支持没有实际转换函数的情况下函数样式的转换规范。如果有转换函数，通常以其输出类型命名，因此不必有特殊情况。有关其他评论，请参见 CREATE CAST 。

[11] The reason for this step is to support function-style cast specifications in cases where there is not an actual cast function. If there is a cast function, it is conventionally named after its output type, and so there is no need to have a special case. See CREATE CAST for additional commentary.