38.5. 查询语言 (SQL) 函数#

38.5.1. 函数的参数
38.5.2. 基本类型函数
38.5.3. 复合类型函数
38.5.4. 带有输出参数的函数
38.5.5. 带有输出参数的过程
38.5.6. 带有可变数量参数的函数
38.5.7. 带有参数默认值的函数
38.5.8. 函数作为表源
38.5.9. 返回集的函数
38.5.10. 返回 TABLE 的函数
38.5.11. 多态函数
38.5.12. 带有校对的函数

SQL 函数执行任意 SQL 语句列表，返回列表中最后一个查询的结果。在简单（非集合）情况下，将返回最后一个查询结果的第一行。（请记住，除非您使用ORDER BY，否则多行结果的“第一行”是未定义的。）如果最后一个查询恰好没有返回任何行，则将返回 null 值。

或者，可以通过将函数的返回类型指定为SETOF*sometype*，或等效地将其声明为RETURNS TABLE(*columns*)来声明 SQL 函数返回一个集合（即多行）。在这种情况下，将返回最后一个查询结果的所有行。更多详细信息如下所示。

SQL 函数的主体必须是一个用分号分隔的 SQL 语句列表。最后一个语句后的分号是可选的。除非函数声明为返回void，否则最后一个语句必须是SELECT，或带有RETURNING子句的INSERT、UPDATE或DELETE。

可以使用SQL语言中的任何命令集合打包在一起并定义为一个函数。除了SELECT查询之外，这些命令还可以包括数据修改查询（INSERT、UPDATE、DELETE和MERGE），以及其他 SQL 命令。（您不能在SQL函数中使用事务控制命令，例如COMMIT、SAVEPOINT，以及一些实用程序命令，例如VACUUM。）但是，最终命令必须是SELECT或带有RETURNING子句，该子句返回指定为函数返回类型的内容。或者，如果您想定义一个执行操作但没有有用的返回值的 SQL 函数，则可以将其定义为返回void。例如，此函数从emp表中删除工资为负数的行

CREATE FUNCTION clean_emp() RETURNS void AS '
    DELETE FROM emp
        WHERE salary < 0;
' LANGUAGE SQL;

SELECT clean_emp();

 clean_emp
-----------

(1 row)

您还可以将其写为一个过程，从而避免返回类型的问题。例如

CREATE PROCEDURE clean_emp() AS '
    DELETE FROM emp
        WHERE salary < 0;
' LANGUAGE SQL;

CALL clean_emp();

在像这样的简单情况下，返回void的函数和过程之间的区别主要是风格上的。但是，过程提供了额外的功能，例如函数中不可用的事务控制。此外，过程是 SQL 标准，而返回void是 PostgreSQL 扩展。

注意

在执行 SQL 函数的任何部分之前，都会解析其整个主体。虽然 SQL 函数可以包含更改系统目录的命令（例如CREATE TABLE），但此类命令的效果在函数中后续命令的解析分析期间将不可见。因此，例如，如果打包到单个 SQL 函数中，则CREATE TABLE foo (...); INSERT INTO foo VALUES(...);将无法按预期工作，因为当解析INSERT命令时foo还不存在。建议在这种情况下使用PL/pgSQL而不是 SQL 函数。

CREATE FUNCTION命令的语法要求函数主体写为字符串常量。通常最方便的做法是使用美元引用（参见第 4.1.2.4 节）作为字符串常量。如果您选择使用常规单引号字符串常量语法，则必须在函数主体中对单引号 (') 和反斜杠 (\)（假设使用转义字符串语法）加倍（参见第 4.1.2.1 节）。

38.5.1.SQL函数的参数#

SQL 函数的参数可以使用名称或数字在函数主体中引用。下面提供了两种方法的示例。

要使用名称，请将函数参数声明为具有名称，然后在函数主体中直接写入该名称。如果参数名称与函数内当前 SQL 命令中的任何列名称相同，则列名称将优先。要覆盖此项，请使用函数本身的名称限定参数名称，即*function_name*.*argument_name*。（如果这与限定的列名称冲突，则列名称再次获胜。您可以通过为 SQL 命令中的表选择不同的别名来避免歧义。）

在较旧的数字方法中，使用语法$*n*引用参数：$1引用第一个输入参数，$2引用第二个输入参数，依此类推。无论特定参数是否已声明为具有名称，这都适用。

如果参数是复合类型，则点符号，例如*argname*.*fieldname*或$1.*fieldname*，可用于访问参数的属性。同样，您可能需要使用函数名称限定参数的名称，以使使用参数名称的形式明确无歧义。

SQL 函数参数只能用作数据值，不能用作标识符。因此，例如，这是合理的

INSERT INTO mytable VALUES ($1);

但这将不起作用

INSERT INTO $1 VALUES (42);

注意

在PostgreSQL9.2 中添加了使用名称引用 SQL 函数参数的功能。必须在较旧的服务器中使用的函数必须使用$*n*符号。

38.5.2.SQL基本类型函数#

最简单的SQL函数没有参数，并且仅返回基本类型，例如integer

CREATE FUNCTION one() RETURNS integer AS $$
    SELECT 1 AS result;
$$ LANGUAGE SQL;

-- Alternative syntax for string literal:
CREATE FUNCTION one() RETURNS integer AS '
    SELECT 1 AS result;
' LANGUAGE SQL;

SELECT one();

 one
-----
   1

请注意，我们在函数体中为函数的结果定义了一个列别名（名称为result），但此列别名在函数外部不可见。因此，结果标记为one而不是result。

定义将基本类型作为参数的SQL函数几乎同样容易

CREATE FUNCTION add_em(x integer, y integer) RETURNS integer AS $$
    SELECT x + y;
$$ LANGUAGE SQL;

SELECT add_em(1, 2) AS answer;

 answer
--------
      3

或者，我们可以放弃参数的名称并使用数字

CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$
    SELECT $1 + $2;
$$ LANGUAGE SQL;

SELECT add_em(1, 2) AS answer;

 answer
--------
      3

这是一个更有用的函数，可以用来借记银行帐户

CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$
    UPDATE bank
        SET balance = balance - debit
        WHERE accountno = tf1.accountno;
    SELECT 1;
$$ LANGUAGE SQL;

用户可以执行此函数，如下所示，以 100.00 美元借记帐户 17

SELECT tf1(17, 100.0);

在此示例中，我们为第一个参数选择了名称accountno，但这与bank表中列的名称相同。在UPDATE命令中，accountno引用列bank.accountno，因此必须使用tf1.accountno来引用参数。当然，我们可以通过为参数使用不同的名称来避免这种情况。

在实践中，人们可能希望从函数中获得比常数 1 更有用的结果，因此更可能的定义是

CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$
    UPDATE bank
        SET balance = balance - debit
        WHERE accountno = tf1.accountno;
    SELECT balance FROM bank WHERE accountno = tf1.accountno;
$$ LANGUAGE SQL;

它调整余额并返回新余额。可以使用RETURNING在一个命令中完成相同的事情

CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$
    UPDATE bank
        SET balance = balance - debit
        WHERE accountno = tf1.accountno
    RETURNING balance;
$$ LANGUAGE SQL;

如果SQL函数中的最终SELECT或RETURNING子句未返回与函数声明的结果类型完全相同的值，PostgreSQL将自动将该值转换为所需类型（如果可以通过隐式或赋值转换实现）。否则，您必须编写显式转换。例如，假设我们希望之前的add_em函数返回类型float8。编写以下内容就足够了

CREATE FUNCTION add_em(integer, integer) RETURNS float8 AS $$
    SELECT $1 + $2;
$$ LANGUAGE SQL;

因为integer和可以隐式转换为float8。（有关转换的更多信息，请参阅第 10 章或CREATE CAST。）

38.5.3.SQL复合类型函数#

编写具有复合类型参数的函数时，我们不仅必须指定所需的某个参数，还必须指定该参数的所需属性（字段）。例如，假设emp是一个包含员工数据的表，因此也是表中每行的复合类型的名称。以下是计算某人的工资翻倍后会是多少的函数double_salary

CREATE TABLE emp (
    name        text,
    salary      numeric,
    age         integer,
    cubicle     point
);

INSERT INTO emp VALUES ('Bill', 4200, 45, '(2,1)');

CREATE FUNCTION double_salary(emp) RETURNS numeric AS $$
    SELECT $1.salary * 2 AS salary;
$$ LANGUAGE SQL;

SELECT name, double_salary(emp.*) AS dream
    FROM emp
    WHERE emp.cubicle ~= point '(2,1)';

 name | dream
------+-------
 Bill |  8400

请注意使用语法$1.salary选择参数行值的一个字段。还要注意调用SELECT命令如何使用*table_name*.*将表的整个当前行作为复合值进行选择。表行也可以只使用表名进行引用，如下所示

SELECT name, double_salary(emp) AS dream
    FROM emp
    WHERE emp.cubicle ~= point '(2,1)';

但这种用法已弃用，因为很容易混淆。（有关表行的复合值的这两种表示法的详细信息，请参阅第 8.16.5 节。）

有时，即时构造复合参数值非常方便。这可以通过ROW构造来完成。例如，我们可以调整传递给函数的数据

SELECT name, double_salary(ROW(name, salary*1.1, age, cubicle)) AS dream
    FROM emp;

还可以构建返回复合类型的函数。以下是一个返回单个emp行的函数示例

CREATE FUNCTION new_emp() RETURNS emp AS $$
    SELECT text 'None' AS name,
        1000.0 AS salary,
        25 AS age,
        point '(2,2)' AS cubicle;
$$ LANGUAGE SQL;

在此示例中，我们使用常量值指定了每个属性，但任何计算都可以替换这些常量。

请注意定义函数的两件重要事项

查询中的选择列表顺序必须与列在复合类型中出现的顺序完全相同。（如上所述，对列进行命名与系统无关。）
我们必须确保每个表达式的类型可以转换为复合类型相应列的类型。否则，我们会收到如下错误
```
ERROR:  return type mismatch in function declared to return emp
DETAIL:  Final statement returns text instead of point at column 4.
```
与基本类型的情况一样，系统不会自动插入显式转换，只会插入隐式或赋值转换。

定义相同函数的另一种方法是

CREATE FUNCTION new_emp() RETURNS emp AS $$
    SELECT ROW('None', 1000.0, 25, '(2,2)')::emp;
$$ LANGUAGE SQL;

这里我们编写了一个SELECT，它只返回正确复合类型的一列。这在这种情况中并没有真正变得更好，但在某些情况下它是一种方便的替代方法——例如，如果我们需要通过调用返回所需复合值的另一个函数来计算结果。另一个示例是，如果我们尝试编写一个返回复合域的函数，而不是一个普通的复合类型，则总是需要将其编写为返回一列，因为没有办法强制转换整个行结果。

我们可以直接调用此函数，方法是在值表达式中使用它

SELECT new_emp();

         new_emp
--------------------------
 (None,1000.0,25,"(2,2)")

或将其作为表函数进行调用

SELECT * FROM new_emp();

 name | salary | age | cubicle
------+--------+-----+---------
 None | 1000.0 |  25 | (2,2)

第二种方法在第 38.5.8 节中进行了更全面的描述。

当您使用返回复合类型的函数时，您可能只想从其结果中获取一个字段（属性）。您可以使用如下语法来实现

SELECT (new_emp()).name;

 name
------
 None

需要额外的括号来防止解析器产生混淆。如果您尝试在没有括号的情况下进行操作，则会得到类似这样的内容

SELECT new_emp().name;
ERROR:  syntax error at or near "."
LINE 1: SELECT new_emp().name;
                        ^

另一种选择是使用函数符号来提取属性

SELECT name(new_emp());

 name
------
 None

如第 8.16.5 节中所述，字段符号和函数符号是等效的。

使用返回复合类型的函数的另一种方法是将结果传递给另一个函数，该函数接受正确的行类型作为输入

CREATE FUNCTION getname(emp) RETURNS text AS $$
    SELECT $1.name;
$$ LANGUAGE SQL;

SELECT getname(new_emp());
 getname
---------
 None
(1 row)

38.5.4.SQL带有输出参数的函数#

描述函数结果的另一种方法是使用输出参数对其进行定义，如下例所示

CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int)
AS 'SELECT x + y'
LANGUAGE SQL;

SELECT add_em(3,7);
 add_em
--------
     10
(1 row)

这与第 38.5.2 节中显示的add_em版本没有本质上的不同。输出参数的真正价值在于，它们提供了一种定义返回多列的函数的便捷方式。例如，

CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int)
AS 'SELECT x + y, x * y'
LANGUAGE SQL;

 SELECT * FROM sum_n_product(11,42);
 sum | product
-----+---------
  53 |     462
(1 row)

这里实质上发生的是，我们为函数的结果创建了一个匿名复合类型。上面的示例具有与以下内容相同的结果

CREATE TYPE sum_prod AS (sum int, product int);

CREATE FUNCTION sum_n_product (int, int) RETURNS sum_prod
AS 'SELECT $1 + $2, $1 * $2'
LANGUAGE SQL;

但是，不必费心于单独的复合类型定义通常很方便。请注意，附加到输出参数的名称不仅仅是装饰，而且还决定了匿名复合类型的列名称。（如果您省略输出参数的名称，系统将自行选择一个名称。）

请注意，从 SQL 调用此类函数时，输出参数不会包含在调用参数列表中。这是因为PostgreSQL仅考虑输入参数来定义函数的调用签名。这也意味着在引用函数以进行删除等操作时，只有输入参数才重要。我们可以使用以下任一方法删除上述函数

DROP FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int);
DROP FUNCTION sum_n_product (int, int);

参数可以标记为IN（默认值）、OUT、INOUT或VARIADIC。INOUT参数用作输入参数（调用参数列表的一部分）和输出参数（结果记录类型的一部分）。VARIADIC参数是输入参数，但会按照如下所述进行特殊处理。

38.5.5.SQL带输出参数的过程#

输出参数也受过程支持，但它们的工作方式与函数略有不同。在CALL命令中，输出参数必须包含在参数列表中。例如，可以这样编写从早期借记银行账户的例程

CREATE PROCEDURE tp1 (accountno integer, debit numeric, OUT new_balance numeric) AS $$
    UPDATE bank
        SET balance = balance - debit
        WHERE accountno = tp1.accountno
    RETURNING balance;
$$ LANGUAGE SQL;

要调用此过程，必须包括与OUT参数匹配的参数。通常编写NULL

CALL tp1(17, 100.0, NULL);

如果您编写其他内容，它必须是隐式可强制转换为参数声明类型的表达式，就像输入参数一样。但请注意，此类表达式不会被评估。

从PL/pgSQL调用过程时，您必须编写一个将接收过程输出的变量，而不是编写NULL。有关详细信息，请参见第 43.6.3 节。

38.5.6.SQL带可变数量参数的函数#

SQL函数可以声明为接受可变数量的参数，只要所有“可选”参数都是相同的数据类型。可选参数将作为数组传递给函数。通过将最后一个参数标记为VARIADIC来声明函数；必须将此参数声明为数组类型。例如

CREATE FUNCTION mleast(VARIADIC arr numeric[]) RETURNS numeric AS $$
    SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;

SELECT mleast(10, -1, 5, 4.4);
 mleast
--------
     -1
(1 row)

实际上，所有实际参数在VARIADIC位置或其之后都会收集到一维数组中，就像您编写了

SELECT mleast(ARRAY[10, -1, 5, 4.4]);    -- doesn't work

不过，您实际上无法编写它——或者至少它与该函数定义不匹配。标记为VARIADIC的参数匹配其元素类型的一个或多个出现，而不是其自身类型。

有时能够将已构建的数组传递给可变参数函数很有用；当一个可变参数函数希望将其数组参数传递给另一个可变参数函数时，这尤其方便。此外，这是调用在允许不受信任的用户创建对象的模式中找到的可变参数函数的唯一安全方式；请参阅第 10.3 节。您可以在调用中通过指定VARIADIC来执行此操作

SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);

这会阻止函数的可变参数参数扩展到其元素类型，从而允许数组参数值正常匹配。VARIADIC只能附加到函数调用的最后一个实际参数。

在调用中指定VARIADIC也是将空数组传递给可变参数函数的唯一方式，例如

SELECT mleast(VARIADIC ARRAY[]::numeric[]);

简单地编写SELECT mleast()不起作用，因为可变参数参数必须至少匹配一个实际参数。（如果您想允许此类调用，您可以定义第二个函数，也命名为mleast，且没有参数。）

从可变参数参数生成的数组元素参数被视为没有自己的名称。这意味着不可能使用命名参数（第 4.3 节）调用可变参数函数，除非您指定VARIADIC。例如，这将起作用

SELECT mleast(VARIADIC arr => ARRAY[10, -1, 5, 4.4]);

但这些不行

SELECT mleast(arr => 10);
SELECT mleast(arr => ARRAY[10, -1, 5, 4.4]);

38.5.7.SQL带有参数默认值的函数#

可以声明函数，其中某些或所有输入参数具有默认值。每当使用数量不足的实际参数调用函数时，都会插入默认值。由于只能从实际参数列表的末尾省略参数，因此具有默认值的参数之后的全部参数也必须具有默认值。（虽然使用命名参数符号可以放松此限制，但仍强制执行此限制，以便位置参数符号合理地起作用。）无论您是否使用它，此功能都要求在调用某些用户不信任其他用户的数据库中的函数时采取预防措施；请参阅第 10.3 节。

例如

CREATE FUNCTION foo(a int, b int DEFAULT 2, c int DEFAULT 3)
RETURNS int
LANGUAGE SQL
AS $$
    SELECT $1 + $2 + $3;
$$;

SELECT foo(10, 20, 30);
 foo
-----
  60
(1 row)

SELECT foo(10, 20);
 foo
-----
  33
(1 row)

SELECT foo(10);
 foo
-----
  15
(1 row)

SELECT foo();  -- fails since there is no default for the first argument
ERROR:  function foo() does not exist

也可以使用=符号代替关键字DEFAULT。

38.5.8.SQL函数作为表源#

所有 SQL 函数都可以在查询的FROM子句中使用，但对于返回复合类型的函数来说尤其有用。如果函数被定义为返回基本类型，则表函数会生成一个单列表。如果函数被定义为返回复合类型，则表函数会为复合类型的每个属性生成一列。

下面是一个示例

CREATE TABLE foo (fooid int, foosubid int, fooname text);
INSERT INTO foo VALUES (1, 1, 'Joe');
INSERT INTO foo VALUES (1, 2, 'Ed');
INSERT INTO foo VALUES (2, 1, 'Mary');

CREATE FUNCTION getfoo(int) RETURNS foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT *, upper(fooname) FROM getfoo(1) AS t1;

 fooid | foosubid | fooname | upper
-------+----------+---------+-------
     1 |        1 | Joe     | JOE
(1 row)

正如示例所示，我们可以像操作常规表的列一样操作函数结果的列。

请注意，我们只从函数中获取了一行。这是因为我们没有使用SETOF。这将在下一节中进行描述。

38.5.9.SQL返回集合的函数#

当 SQL 函数被声明为返回SETOF*sometype*时，函数的最终查询会执行到完成，并且它输出的每一行都会作为结果集的一个元素返回。

此功能通常在FROM子句中调用函数时使用。在这种情况下，函数返回的每一行都会变成查询看到的表的一行。例如，假设表foo的内容与上面相同，并且我们说

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

那么我们会得到

fooid | foosubid | fooname
-------+----------+---------
     1 |        1 | Joe
     1 |        2 | Ed
(2 rows)

也可以使用输出参数定义的列返回多行，如下所示

CREATE TABLE tab (y int, z int);
INSERT INTO tab VALUES (1, 2), (3, 4), (5, 6), (7, 8);

CREATE FUNCTION sum_n_product_with_tab (x int, OUT sum int, OUT product int)
RETURNS SETOF record
AS $$
    SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

SELECT * FROM sum_n_product_with_tab(10);
 sum | product
-----+---------
  11 |      10
  13 |      30
  15 |      50
  17 |      70
(4 rows)

这里的关键点是必须编写RETURNS SETOF record来指示函数返回多行而不是一行。如果只有一个输出参数，请编写该参数的类型而不是record。

通过多次调用返回集合的函数来构造查询结果通常很有用，每次调用的参数都来自表或子查询的连续行。执行此操作的首选方法是使用LATERAL关键字，该关键字在第 7.2.1.5 节中进行了描述。下面是一个使用返回集合的函数枚举树结构元素的示例

SELECT * FROM nodes;
   name    | parent
-----------+--------
 Top       |
 Child1    | Top
 Child2    | Top
 Child3    | Top
 SubChild1 | Child1
 SubChild2 | Child1
(6 rows)

CREATE FUNCTION listchildren(text) RETURNS SETOF text AS $$
    SELECT name FROM nodes WHERE parent = $1
$$ LANGUAGE SQL STABLE;

SELECT * FROM listchildren('Top');
 listchildren
--------------
 Child1
 Child2
 Child3
(3 rows)

SELECT name, child FROM nodes, LATERAL listchildren(name) AS child;
  name  |   child
--------+-----------
 Top    | Child1
 Top    | Child2
 Top    | Child3
 Child1 | SubChild1
 Child1 | SubChild2
(5 rows)

此示例并未执行任何无法通过简单联接完成的操作，但在更复杂的计算中，将部分工作放入函数中的选项会非常方便。

返回集合的函数也可以在查询的选择列表中调用。对于查询自行生成的每一行，都会调用返回集合的函数，并且为函数结果集的每个元素生成一个输出行。前面的示例也可以通过以下查询完成

SELECT listchildren('Top');
 listchildren
--------------
 Child1
 Child2
 Child3
(3 rows)

SELECT name, listchildren(name) FROM nodes;
  name  | listchildren
--------+--------------
 Top    | Child1
 Top    | Child2
 Top    | Child3
 Child1 | SubChild1
 Child1 | SubChild2
(5 rows)

在最后一个SELECT中，请注意Child2、Child3等未出现任何输出行。这是因为listchildren为这些参数返回一个空集合，因此未生成任何结果行。这与使用LATERAL语法将内部联接用于函数结果时获得的行为相同。

PostgreSQL中查询选择列表中返回集合的函数的行为几乎与在LATERAL FROM子句项中编写返回集合的函数的行为完全相同。例如，

SELECT x, generate_series(1,5) AS g FROM tab;

几乎等同于

SELECT x, g FROM tab, LATERAL generate_series(1,5) AS g;

除了在这个特定示例中，计划程序可以选择将g放在嵌套循环联接的外部，因为g对tab没有实际的横向依赖关系。这将导致不同的输出行顺序。选择列表中的返回集合的函数始终被评估为好像它们位于FROM子句其余部分的嵌套循环联接的内部，以便在考虑FROM子句的下一行之前运行函数直到完成。

如果查询的选择列表中有多个返回集合的函数，则行为类似于将函数放入单个LATERAL ROWS FROM( ... )``FROM子句项中所获得的行为。对于基础查询的每一行，都有一个输出行使用每个函数的第一个结果，然后是一个使用第二个结果的输出行，依此类推。如果某些返回集合的函数产生的输出少于其他函数，则用空值替换缺失的数据，以便为一个基础行发出的总行数与产生最多输出的返回集合函数相同。因此，返回集合的函数运行“同步”，直到它们全部耗尽，然后执行继续进行下一基础行。

返回集合的函数可以嵌套在选择列表中，尽管在FROM子句项中不允许这样做。在这种情况下，每个嵌套级别都将被单独处理，就好像它是一个单独的LATERAL ROWS FROM( ... )项。例如，在

SELECT srf1(srf2(x), srf3(y)), srf4(srf5(z)) FROM tab;

集合返回函数srf2、srf3和srf5将针对tab的每一行按部就班地运行，然后srf1和srf4将针对较低函数产生的每一行按部就班地应用。

集合返回函数不能在条件评估构造中使用，例如CASE或COALESCE。例如，考虑

SELECT x, CASE WHEN x > 0 THEN generate_series(1, 5) ELSE 0 END FROM tab;

这似乎应该生成五次具有x > 0的输入行重复，以及一次不具有该条件的输入行重复；但实际上，由于generate_series(1, 5)将在隐式LATERAL FROM项中运行，在CASE表达式得到评估之前，它将生成每一行输入的五次重复。为了减少混淆，此类情况将生成解析时错误。

注意

如果函数的最后一个命令是INSERT、UPDATE或DELETE且带有RETURNING，则该命令将始终执行到完成，即使函数未声明为SETOF或调用查询未获取所有结果行。由RETURNING子句生成的所有额外行将被静默删除，但命令的表修改仍然会发生（并且在从函数返回之前全部完成）。

注意

在PostgreSQL10 之前，在同一选择列表中放置多个集合返回函数的行为并不十分明智，除非它们始终生成相等数量的行。否则，您将获得一个输出行数量，该数量等于集合返回函数生成的行数量的最小公倍数。此外，嵌套的集合返回函数不能按上述方式工作；相反，集合返回函数最多只能有一个集合返回参数，并且集合返回函数的每个嵌套独立运行。此外，以前允许条件执行（CASE等中的集合返回函数），这使事情变得更加复杂。在编写需要在较旧PostgreSQL版本中工作的查询时，建议使用LATERAL语法，因为这将在不同版本中提供一致的结果。如果您有依赖集合返回函数的条件执行的查询，则可以通过将条件测试移至自定义集合返回函数来修复它。例如，可以变成此公式将在所有版本的PostgreSQL中以相同的方式工作。

38.5.10.SQL函数返回`TABLE`#

另一种将函数声明为返回集合的方法是使用语法RETURNS TABLE(*columns*)。这等效于使用一个或多个OUT参数，以及将函数标记为返回SETOF record（或SETOF单个输出参数的类型，视情况而定）。此表示法在最新版本的 SQL 标准中指定，因此可能比使用SETOF更具可移植性。

例如，前面的求和和乘积示例也可以这样完成

CREATE FUNCTION sum_n_product_with_tab (x int)
RETURNS TABLE(sum int, product int) AS $$
    SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

不允许在RETURNS TABLE表示法中使用显式的OUT或INOUT参数 — 您必须将所有输出列放入TABLE列表中。

38.5.11. 多态SQL函数#

SQL函数可以声明为接受和返回第 38.2.5 节中描述的多态类型。这是一个多态函数make_array，它从两个任意数据类型元素构建一个数组

CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$
    SELECT ARRAY[$1, $2];
$$ LANGUAGE SQL;

SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;
 intarray | textarray
----------+-----------
 {1,2}    | {a,b}
(1 row)

注意类型转换'a'::text的用法，以指定参数的类型为text。如果参数只是一个字符串文字，则这是必需的，因为否则它将被视为类型unknown，而unknown数组不是一个有效类型。如果没有类型转换，您将收到如下错误

ERROR:  could not determine polymorphic type because input has type unknown

如果make_array声明如上，您必须提供两个完全相同数据类型的参数；系统不会尝试解决任何类型差异。因此，例如，这不起作用

SELECT make_array(1, 2.5) AS numericarray;
ERROR:  function make_array(integer, numeric) does not exist

另一种方法是使用“common”多态类型系列，它允许系统尝试识别合适的公共类型

CREATE FUNCTION make_array2(anycompatible, anycompatible)
RETURNS anycompatiblearray AS $$
    SELECT ARRAY[$1, $2];
$$ LANGUAGE SQL;

SELECT make_array2(1, 2.5) AS numericarray;
 numericarray
--------------
 {1,2.5}
(1 row)

因为公共类型解析规则在所有输入都是未知类型时默认选择类型text，所以这也起作用

SELECT make_array2('a', 'b') AS textarray;
 textarray
-----------
 {a,b}
(1 row)

允许具有固定返回类型的多态参数，但反之则不然。例如

CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$
    SELECT $1 > $2;
$$ LANGUAGE SQL;

SELECT is_greater(1, 2);
 is_greater
------------
 f
(1 row)

CREATE FUNCTION invalid_func() RETURNS anyelement AS $$
    SELECT 1;
$$ LANGUAGE SQL;
ERROR:  cannot determine result data type
DETAIL:  A result of type anyelement requires at least one input of type anyelement, anyarray, anynonarray, anyenum, or anyrange.

多态性可与具有输出参数的函数一起使用。例如

CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray)
AS 'select $1, array[$1,$1]' LANGUAGE SQL;

SELECT * FROM dup(22);
 f2 |   f3
----+---------
 22 | {22,22}
(1 row)

多态性也可用于可变参数函数。例如

CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$
    SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;

SELECT anyleast(10, -1, 5, 4);
 anyleast
----------
       -1
(1 row)

SELECT anyleast('abc'::text, 'def');
 anyleast
----------
 abc
(1 row)

CREATE FUNCTION concat_values(text, VARIADIC anyarray) RETURNS text AS $$
    SELECT array_to_string($2, $1);
$$ LANGUAGE SQL;

SELECT concat_values('|', 1, 4, 2);
 concat_values
---------------
 1|4|2
(1 row)

38.5.12.SQL带排序规则的函数#

当 SQL 函数具有一个或多个可排序数据类型的参数时，将根据分配给实际参数的排序规则为每个函数调用识别一个排序规则，如第 24.2 节中所述。如果成功识别排序规则（即，参数之间没有隐式排序规则冲突），则所有可排序参数都将被视为隐式具有该排序规则。这将影响函数中与排序规则相关的操作的行为。例如，使用上面描述的anyleast函数，结果

SELECT anyleast('abc'::text, 'ABC');

将取决于数据库的默认排序规则。在C区域设置中，结果将是ABC，但在许多其他区域设置中，结果将是abc。可以通过向任何参数添加COLLATE子句来强制使用排序规则，例如

SELECT anyleast('abc'::text, 'ABC' COLLATE "C");

或者，如果您希望函数在不考虑调用方式的情况下使用特定排序规则，请在函数定义中根据需要插入COLLATE子句。此版本的anyleast将始终使用en_US区域设置来比较字符串

CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$
    SELECT min($1[i] COLLATE "en_US") FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;

但请注意，如果应用于不可排序的数据类型，这将引发错误。

如果在实际参数之间无法识别公共排序规则，则 SQL 函数会将其参数视为具有其数据类型的默认排序规则（通常是数据库的默认排序规则，但对于域类型参数可能不同）。

可排序参数的行为可以被认为是多态性的有限形式，仅适用于文本数据类型。

38.5. 查询语言 (SQL) 函数# ​

38.5.1.SQL函数的参数# ​

38.5.2.SQL基本类型函数# ​

38.5.3.SQL复合类型函数# ​

38.5.4.SQL带有输出参数的函数# ​

38.5.5.SQL带输出参数的过程# ​

38.5.6.SQL带可变数量参数的函数# ​

38.5.7.SQL带有参数默认值的函数# ​

38.5.8.SQL函数作为表源# ​

38.5.9.SQL返回集合的函数# ​

38.5.10.SQL函数返回TABLE# ​

38.5.11. 多态SQL函数# ​

38.5.12.SQL带排序规则的函数# ​