33.4. 查询语言(SQL)函数

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

SELECT clean_emp();

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

(1 row)

INSERT INTO mytable VALUES ($1);

INSERT INTO $1 VALUES (42);

33.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

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

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

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

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

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

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

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

这个版本和节33.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 参数同时作为输入参数(调用参数列表的一部分)和输出参数(结果记录类型的一部分)。

33.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 row)

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

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

33.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 为这些参数返回一个空集合，因此不生成任何结果行。

33.4.6. 多态的 SQL 函数

SQL 函数可以声明为接受并返回多态的 anyelement 和 anyarray 类型。参阅节33.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)