36.4. 查询语言（SQL）函数

36.4.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);

36.4.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 integer AS $$
    UPDATE bank
        SET balance = balance - debit
        WHERE accountno = tf1.accountno;
    SELECT 1;
$$ LANGUAGE SQL;

一个用户可以这样执行这个函数来从账户 17 中借记 $100.00：

SELECT tf1(17, 100.0);

在这个例子中，我们为第一个参数选择了名称accountno，但是这和表bank中的一个列名相同。 在UPDATE命令中， accountno引用列bank.accountno，因此 tf1.accountno必须被用来引用该参数。 我们当然可以通过为该参数使用一个不同的名称来避免这样的问题。

实际上我们可能喜欢从该函数得到一个更有用的结果而不是一个常数 1，因此一个更可能的定义是：

CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS integer 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 integer AS $$
    UPDATE bank
        SET balance = balance - debit
        WHERE accountno = tf1.accountno
    RETURNING balance;
$$ LANGUAGE SQL;

36.4.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命令是如何使用* 来选择一个表的整个当前行作为一个组合值的。该表行也可以只用表名来引用：

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

但这种用法已被废弃因为它很容易被搞混。

有时候实时构建一个组合参数很方便。这可以用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:  function declared to return emp returns varchar instead of text at column 1

定义同样的函数的一种不同的方法是：

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)

第二种方式在第 36.4.7 节中有更完全的描述。

当你使用一个返回组合类型的函数时，你可能只想要其结果中的一个域（属性）。 你可以这样做：

SELECT (new_emp()).name;

 name
------
 None

额外的圆括号是必须的，它用于避免解析器被搞混。如果你不写这些括号，会这样：

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

另一个选项是使用函数记号来抽取一个属性。简而言之就是我们可以交换使用 attribute(table)和 table.attribute。

SELECT name(new_emp());

 name
------
 None

-- 这等同于：
-- SELECT emp.name AS youngster FROM emp WHERE emp.age < 30;

SELECT name(emp) AS youngster FROM emp WHERE age(emp) < 30;

 youngster
-----------
 Sam
 Andy

另一种使用返回组合类型的函数的方法是把结果传递给另一个接收正确行类型作为输入的函数：

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

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

还有另一种使用返回组合类型的函数的方法是把它作为一个表函数调用，可参考第 36.4.7 节。

36.4.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)

这和第 36.4.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参数是输入参数，但被按照后文所述特殊对待。

36.4.5. 带有可变数量参数的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]);    -- 不起作用

但是你实际无法这样写 — 或者说至少它将无法匹配这个函数定义。一个被标记为VARIADIC的参数匹配其元素类型的一次或者多次出现，而不是它自身类型的出现。

有时候能够传递一个已经构造好的数组给 variadic 函数是有用的，特别是当 一个 variadic 函数想要把它的数组参数传递给另一个函数时这会特别方便。 可以在调用中指定VARIADIC来这样做：

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

这会阻止该函数的 variadic 参数扩展成它的元素结构，从而允许数组参 数值正常匹配。VARIADIC只能被附着在函数调用的最后一 个实参上。

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

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

简单地写成SELECT mleast()是没有作用的，因为一个 variadic 参数必须匹配至少一个实参（如果想允许这类调用，你可以定义第二个没有 参数且也叫mleast的函数）。

从一个 variadic 参数产生的数组元素参数会被当做自己不具有名称。这 意味着不能使用命名参数调用 variadic 函数（第 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]);

36.4.6. 带有参数默认值的SQL函数

函数可以被声明为对一些或者所有输入参数具有默认值。只要调用函数时 没有给出足够多的实参，就会插入默认值来弥补缺失的实参。由于参数只 能从实参列表的尾部开始被省略，在一个有默认值的参数之后的所有参数 都不得不也具有默认值（尽管使用命名参数记法可以允许放松这种限制， 这种限制仍然会被强制以便位置参数记法能工作）。

例如：

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();  -- 因为第一个参数没有默认值，所以会失败
ERROR:  function foo() does not exist

=符号也可以用来替代关键词 DEFAULT。

36.4.7. 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。 这会在下一节中介绍。

36.4.8. 返回集合的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。

通过多次调用集合返回函数来构建查询的结果非常有用，每次调用的参数 来自于一个表或者子查询的连续行。做这种事情最好的方法是使用 第 7.2.1.5 节中描述的LATERAL关键 词。这里是一个使用集合返回函数枚举树结构中元素的例子：

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 语法时，我们从与该函数结果的内连接得到的行为是一样的。

36.4.9. 返回TABLE的SQL函数

还有另一种方法可以把函数声明为返回一个集合，即使用 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;

不允许把显式的OUT或者INOUT参数用于 RETURNS TABLE记法 — 必须把所有输出列放在 TABLE列表中。

36.4.10. 多态SQL函数

SQL函数可以被声明为接受并且返回多态类型 anyelement、anyarray、 anynonarray、anyenum以及 anyrange。更多关于多态函数的解释请见第 36.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"

允许具有多态参数和固定的返回类型，但是反过来不行。例如：

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 function returning a polymorphic type must have at least one polymorphic argument.

多态化可以用在具有输出参数的函数上。例如：

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)

多态化也可以用在 variadic 函数上。例如：

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)

36.4.11. 带有排序规则的SQL函数

当一个 SQL 函数具有一个或者更多可排序数据类型的参数时，按照 第 23.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 函数会把它的参数 当作拥有其数据类型的默认排序规则（通常是数据库的默认排序规则， 但是域类型的参数可能会不同）。

可排序参数的行为可以被想成是多态的一种受限形式，只对于文本数据 类型有效。