35.4. 查询语言(SQL)函数

35.4.1. Arguments for SQL Functions

在函数体中使用名称或数字引用SQL函数的参数。这两种方法的例子在下面。

使用一个名字，声明有名称的函数参数，然后在函数体中写上这个名字。如果参数名称 在当前SQL命令的同一函数中与任何列的名称相同，将优先考虑列名称。为了重写， 限定参数名与函数名本身，也就是说function_name.argument_name。 （如果有一个合格的列名称冲突，再次列名称获胜。你可以通过选择一个SQL命令表不同的别名来避免歧义。）

在旧的数值方法中，使用语法$n: $1引用第一个输入参数， $2到第二个，等等。是否声明带有名字的特定参数将要工作。

如果一个参数是复合类型，然后圆点标记法，比如，argname.fieldname或者 $1.fieldname可以用于访问参数属性。再次，你可能需要限定函数名的参数名 来形成模糊参数名形式。

SQL函数参数只能作为数据值使用，而不能作为标示符。因此比如这是合理的：

INSERT INTO mytable VALUES ($1);

but this will not work:

INSERT INTO $1 VALUES (42);

35.4.2. 基本类型上的SQL函数

最简单的SQL函数可能没有参数并且返回一个基本类型， 比如一个返回integer的函数：

CREATE FUNCTION one() RETURNS integer AS $$
    SELECT 1 AS result;
$$ LANGUAGE SQL;
   --另外一种字符串文本的语法：
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;

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

第二种方法在第 35.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)

还可以把返回复合类型的函数当作一个表函数使用，如第 35.4.7 节所述。

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

这个版本和第 35.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参数是输入参数，但是作为描述文本特殊对待。

35.4.5. 带有参数可变数量的SQL

SQL函数声明接受参数可变数量，只要所有"optional" 参数有相同数据类型。可选参数将被作为数组传递给函数。函数通过把最后参数作为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的参数匹配一个或多个元素类型 的发生，而不是固有类型。

有时候可以将已构建数组传递给可变参数函数；

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]);

35.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();  -- fails since there is no default for the first argument
ERROR:  function foo() does not exist

=符号也可以用在关键字DEFAULT的位置。

35.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。' 这个问题在下一节讲述。

35.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。

它通过调用多次设置返回函数构建一个查询结果经常是有用的，为了每个参数调用 一个表或查询连续的行。这样做的最佳方法是使用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语法时，这同从内部链接到函数结果行为是一样的。

35.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;

不允许使用明确的带有RETURNS TABLE标记的 OUT或者INOUT参数— 你必须将所有输出参数放在 TABLE列表中。

35.4.10. 多态SQL函数

SQL函数可以声明为接受并返回多态类型anyelement, anyarray, anynonarray, anyenum和anyrange。 参阅第 35.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)

多态性也可以使用可变参数函数。比如：

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)

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

当一个SQL函数具有一个或多个collatable数据类型的参数， 排序规则认同每个函数调用依赖 于分配给实际参数的排序规则，正如第 22.2 节描述的。如果 一个排序规则成功地被识别（即不存在参数之间的隐式排序规则的冲突） 然后所有的collatable参数作为 含蓄的排序规则对待。这会影响函数内排序规则区分操作行为。 例如，使用上文描述的 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;

但是请注意如果适用于非-collatable数据类型，则将抛出一个错误。

如果在实际参数之间没有识别通用排序规则， 那么一个SQL函数将其参数作为数据类型 的默认排序规则（通常是数据库的默认排序规则，但不同于域类型参数）。

collatable参数操作可以被认为是多态的有限形式，只适用于文本数据类型。