PostgreSQL数据库学习手册之扩展的SQL：函数

9.1. 介绍

PostgreSQL 提供四种类型的函数：

查询语言函数（用 SQL 写的函数）

过程语言函数（用诸如 PL/Tcl 或 PL/pgSQL 这样的语言写的函数）

内部函数

C 语言函数

每种函数都可以以一个基本类型或一个复合类型或是两者的某种组合作为参数．另外，每种函数都可以返回一个基本类型或一个复合类型值．定义 SQL 函数更容易些，所以我们将从这里开始．本章的例子还可以在 funcs.sql 和 funcs.c 里找到．

综观全章，如果你阅读一下 CREATE FUNCTION 的手册页应该会对你理解本章的例子很有帮助．

9.2. 查询语言（SQL）函数

SQL 函数执行一个任意 SQL 查询的列表，返回列表里最后一个查询的结果。它必须是一条 SELECT．在比较简单的情况下(非集合的情况)，返回最后一条查询结果的第一行．(请记住多行结果的"第一行" 是不明确的，除非你用 ORDER BY 对结果排序．) 如果最后一个查询碰巧不返回行，那么返回 NULL．

另外，一个 SQL 函数可以声明为返回一个集合，方法是把该函数的返回类型声明为 SETOF sometype．这个时候最后一条查询结果的所有行都会被返回．更多的细节在下面讲．

SQL 函数的函数体应该是一个用分号分隔的一条或多条 SQL 语句的列表．请注意，因为 CREATE FUNCTION 命令的语法要求函数体要封闭在单引号里面，所以在函数体中使用的单引号 (') 必须逃逸，方法是写两个单引号(') 或者在需要逃逸的单引号之前放一个反斜扛 (')．

SQL 函数的参数在查询里可以用 $n 语法引用： $1指第一个参数，$2 指第二个参数，以此类推。如果参数是复合类型，那么可以用点表示法，例如，"$1.emp"，访问参数里的字段。

9.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．另外，如果你想定义那些执行动作但是不返回有用的数值的 SQL 函数，你可以把它定义成返回 void。这时候它不能以 SELECT 为最后一条语句。比如：

CREATE FUNCTION clean_EMP () RETURNS void AS '

DELETE FROM EMP

WHERE EMP.salary <= 0;

' LANGUAGE SQL;

SELECT clean_EMP();

clean_emp

-----------

(1 row)

9.2.2. 基本类型的 SQL 函数

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

CREATE FUNCTION one()

RETURNS integer

AS 'SELECT 1 as RESULT;'

LANGUAGE 'sql';

SELECT one();

one

-----

注意我们给函数定义了目标列（名称为 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 |

+-------+

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

返回一行（复合类型）的函数可以用作一个表函数，象下面描述地那样。我们还可以在 SQL 表达式的环境里调用它，但是只有在你从该行中抽取一个字段或者把整个行传递给另外一个接受同样复合类型的函数中才可以。比如，

SELECT (new_emp()).name;

name

------

None

我们需要一个额外的圆括弧以防止分析器误解∶

SELECT new_emp().name;

ERROR: parser: parse error at or near "."

另外一个选择是使用函数表示法进行字段抽取．解释这些问题的简单方法是我们通常交互使用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

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

CREATE FUNCTION getname(emp) RETURNS text AS

'SELECT $1.name;'

LANGUAGE SQL;

SELECT getname(new_emp());

getname

---------

None

(1 row)

9.2.4. SQL 表函数

一个表函数就是一个可以在查询的 FROM 子句里使用的函数。所有的 SQL 语言函数都可以用这种方法使用，但是它对于返回复合类型的函数特别有用。如果该函数定义为返回一个基本类型，那么表函数生成一个单字段表。如果该函数定义为返回一个复合类型，那么该表函数生成一个复合类型里每个字段组成的行。

这里是一个例子：

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

(2 rows)

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

请注意我们只从该函数中获取了一行。这是因为我们没有说 SETOF。

9.2.5. 返回集合的 SQL 函数

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

这个特性通常用于把函数当作表函数调用。这个时候函数返回的每一行都成为查询可见的该表的一行。比如，假设表 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)

目前，返回集合的函数也可以在一个 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 为这些输入返回一个空集合，因此不生成任何输出行．

9.3. 过程语言函数

过程语言函数不是内建于 PostgreSQL 里的。它们是通过可装载模块提供的。请参考相关的 PL 的文档获取关于语法和 AS 子句如何被 PL 句柄解释的细节。

在标准的 PostgreSQL 版本里有四种可用的过程语言（PL/pgSQL，PL/Tcl，PL/Perl 和 PL/Python），并且可以定义其他语言。请参考 Chapter 18 获取详细信息。

9.4. 内部函数

内部函数是那些用 C 写的函数，它们已经通过静态链接的方式链接进入 PostgreSQL 后端进程里面。 AS 子句给出函数的 C 语言的名称，它不必与定义给 SQL 使用的名称相同。（出于向下兼容考虑，一个空的 AS 子句可以被接受，意味着 C 函数名与 SQL 函数名相同。）通常，所有在后端里出现的内部函数都在数据库初始化时定义为 SQL 函数，但是用户可以用 CREATE FUNCTION 为内部函数创建额外的别名。

内部函数在 CREATE FUNCTION 命令里是带着语言名字 internal声明的．

表函数

表函数是生成一个行集合的函数，这些行可以是由基本（标量）数据类型，也可以是由复合（多字段）数据类型组成。它们的使用类似 FROM 中的一个表，视图，或者是子查询。表函数返回的字段可以象表，视图，或者子查询字段一样的形式包含在 SELECT，JOIN，或者 WHERE 子句里。

如果表函数返回一个基本数据类型，那么这个单结果字段以函数的名字命名。如果函数返回一个复合类型，那么结果字段获得和该复合类型的独立字段相同的名字。

一个表函数可以在 FROM 子句里面取别名，单它也可以不用别名。如果一个函数不用别名在 FROM 子句里使用，那么函数名用作关系名。

表函数可以在 SELECT 语句里面可以使用表的地方用。比如

CREATE TABLE foo (fooid int, foosubid int, fooname text);

SELECT * FROM foo WHERE fooid = $1;

' LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

SELECT * FROM foo

WHERE foosubid in (select foosubid from getfoo(foo.fooid) z

where z.fooid = foo.fooid);

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);

SELECT * FROM vw_getfoo;

都是合法的语句。

有时候，定义一些可以根据调用方式不同而返回不同字段集合的表函数是有用的。为了支持这个特性，表函数可以声明为返回伪类型 record。如果我们在查询中使用这样的函数，那么预期的行结构必须在查询本身中声明，这样系统才能知道如何分析和规划这个查询。思考一下这个例子：

SELECT *

FROM dblink('dbname=template1', 'select proname, prosrc from pg_proc')

AS t1(proname name, prosrc text)

WHERE proname LIKE 'bytea%';

dblink 函数执行一个远端的查询（参阅 contrib/dblink）。它声明伪返回 record，因为它可能用于任何类型的查询。实际的字段集必须在调用查询里声明，这样分析器才知道象 * 这样的东西应该展开成什么。