12.2. 查询语言（ SQL ）函数

12.2.1. 例子

看看下面这个简单的 SQL 函数的例子， 它将用于对一个银行帐号做扣款（借记消费 debit）动作：

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

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

SELECT tp1( 17,100.0);
     

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

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

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

SQL 里面的任何命令集都可以打成一个包， 做成一个函数．这些命令可以包含数据修改(也就是说， INSERT ， UPDATE ， 和 DELETE )以及 SELECT 查询． 不过，最后的命令必须是一条返回函数声明的返回类型的 SELECT ．

CREATE FUNCTION clean_EMP () RETURNS integer AS '
    DELETE FROM EMP
        WHERE EMP.salary <= 0;
    SELECT 1 AS ignore_this;
' LANGUAGE SQL;

SELECT clean_EMP();

 x
---
 1

12.2.2. 基本类型的 SQL 函数

最简单的 SQL 函数可能是不带参数，只是返回一个基本类型如 integer 的函数：

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      |
+-------+
     

12.2.3. 复合类型的 SQL 函数

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

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
------+-------
 Sam  |  2400

请注意这里使用 $1.salary 的语法 选择参数行数值的一个字段．还要注意 SELECT 命令是如何 使用一个表的名字表示该表的整个当前行作为复合数值．

我们也可以写一个返回复合类型的函数．(不过，我们下面将看到， 在这些函数的使用上有一些不幸的限制．) 下面是一个返回一行 EMP 函数的例子∶

CREATE FUNCTION new_emp() RETURNS EMP AS '
    SELECT text ''None'' AS name,
        1000 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
  

在目前的 PostgreSQL 版本里有一些让人 不快的限制，这些限制约束了我们可以使用复合类型返回值的方法． 简单说，在调用一个返回一行的函数的时候，我们无法检索整行． 我们要么是把某个字段映射出该行，要么是把整行传递给另外一个函数． (试图显示整行数值将产生没有意义的数字．)比如，

SELECT name(new_emp());

 name
------
 None

这个例子使用了函数概念进行字段抽取．解释这些问题的简单方法是 我们通常交互使用 attribute(table) 和 table.attribute 的概念∶

--
-- 下面的与这句话相同∶
--  SELECT EMP.name AS youngster FROM EMP WHERE EMP.age < 30
--
SELECT name(EMP) AS youngster
    FROM EMP
    WHERE age(EMP) < 30;

 youngster
-----------
 Sam

通常，我们必须使用函数语法映射作为函数返回值的字段的原因 是在和函数调用接合在一起时，分析器不能理解用于映射的点分语法．

SELECT new_emp().name AS nobody;
ERROR:  parser: parse error at or near "."

另外一个使用函数返回行结果的方法是声明另外一个函数， 该函数接受一个行类型参数，然后把函数结果传递给这个第二个函数∶

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

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

12.2.4. 返回集合的 SQL 函数

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

返回集合的函数只能在一个 SELECT 查询的目标列表中调用． 对于 SELECT 自己生成的每个行都会调用这个返回集合的函数， 并且为该函数的结果集的每个元素生成一个输出行．比如∶

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 为这些输入返回一个空集合， 因此不生成任何输出行．