32.4. 查询语言（SQL）函数

32.4.1. 基本类型上的 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 函数几乎一样简单。 在下面的例子里，请注意我们在函数中是如何用$1 和 $2 引用参数的。

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

SELECT add_em(1, 2) AS answer;
                                                                                                                                                                            
 answer
--------
      3

下面是一个更有用的函数，我们可以用它对一个银行帐号做扣款（借记消费 debit）动作：

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

一个用户可以象下面这样用这个函数给帐户 17 扣款 $100.00：

SELECT tf1( 17,100.0);

实际上我们可能喜欢函数有一个比常量 1 更有用一些的结果。 所以更有可能的定义是

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

它修改余额并返回新的余额。

32.4.2. 复合类型的SQL函数

当我们书写使用用复合类型做参数的函数时， 我们不仅要声明我们需要哪个参数（像上面我们使用 $1和$2一样），而且要声明参数的字段（数据域）。比如， 假设 emp 是一个包含雇员信息的表，并且因此也是该表每行的复合类型的名字。这里就是一个函数 double_salary，它计算某人薪水翻番之后的数值：

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

CREATE FUNCTION double_salary(emp) RETURNS integer 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)

第二种方法在 Section 32.4.4 里有更完整的描述。

在使用一个返回复合类型的函数的时候，你可能想从结果中只抽取一个字段。 你可以用下面的语法来做：

SELECT (new_emp()).name;

 name
------
 None

我们需要一个额外的圆括弧以防止分析器误解。 如果你想省略这对括弧，你会看见类似下面这样的东西：

SELECT new_emp().name;
ERROR:  syntax error at or near "." at character 17
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)

另外一个把使用返回复合类型的函数的方法是把它当作一个表函数使用，如 Section 32.4.4 所述。

32.4.3. 带输出参数的函数

描述函数的结果的另外一种方法是把它定义成带有输出参数， 像下面的例子一样：

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

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

这个版本和 Section 32.4.1 里面的那个 add_em 版本没有什么本质的区别。输出参数的真正价值在于它提供了定义返回多个字段的函数的便利方法。 比如，

CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int)
AS 'SELECT $1 + $2, $1 * $2'
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。一个 INOUT 参数可以当作输入参数（调用参数列表的一部分）和一个输出参数（结果记录类型的一部分）。

32.4.4. 当做表数据源的 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 rows)

如这个例子显示的那样，我们可以象对待一个普通表的字段一样对待函数的结果字段。

请注意我们只从该函数中获取了一行。这是因为我们没有说 SETOF。 这个问题我们在下一节描述。

32.4.5. 返回集合的 SQL 函数

如果一个 SQL 函数声明为返回 SETOF sometype。 这时候，该函数的最后的SELECT查询一直执行到结束，并且它输出的每行都当做该结果集合的一个元素返回。

这个特性通常用于把函数放在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 FUNCTION listchildren(text) RETURNS SETOF text AS $$
SELECT name FROM nodes WHERE parent = $1
$$ LANGUAGE SQL;

SELECT * FROM nodes;
   name    | parent
-----------+--------
 Top       |
 Child1    | Top
 Child2    | Top
 Child3    | Top
 SubChild1 | Child1
 SubChild2 | Child1
(6 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 为这些参数返回一个空集合， 因此不生成任何结果行。

32.4.6. 多态的 SQL 函数

SQL 函数可以声明为接受并返回多态的类型 anyelement 和 anyarray。参阅 Section 32.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 "anyarray"/"anyelement" type because input has type "unknown"

PostgreSQL 允许有多态的参数的函数返回一个固定类型，但是反过来不行。比如：

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 "anyarray" or "anyelement" must have at least one argument of either type.

多形性可以用于那些有输出参数的函数。比如：

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)