PostgreSQL学习手册：SQL语言函数

PostgreSQL学习手册：SQL语言函数

一、基本概念：

SQL函数可以包含任意数量的查询，但是函数只返回最后一个查询(必须是SELECT)的结果。在简单情况下，返回最后一条查询结果的第一行。如果最后一个查询不返回任何行，那么该函数将返回NULL值。如果需要该函数返回最后一条SELECT语句的所有行，可以将函数的返回值定义为集合，即SETOF sometype。

SQL函数的函数体应该是用分号分隔的SQL语句列表，其中最后一条语句之后的分号是可选的。除非函数声明为返回void，否则最后一条语句必须是SELECT。事实上，在SQL函数中，不仅可以包含SELECT查询语句，也可以包含INSERT、UPDATE和DELETE等其他标准的SQL 语句，但是和事物相关的语句不能包含其中，如BEGIN、COMMIT、ROLLBACK和SAVEPOINT 等。

CREATE FUNCTION命令的语法要求函数体写成一个字符串文本。通常来说，该文本字符串常量使用美元符($$)围住，如：

CREATE FUNCTION clean_emp() RETURNS void AS $$

DELETE FROM emp WHERE salary < 0;

$$ LANGUAGE SQL;

最后需要说明的是SQL函数中的参数，PostgreSQL定义$1表示第一个参数，$2为第二个参数并以此类推。如果参数是复合类型，则可以使用点表示法，即$访问复合类型参数中的name字段。需要注意的是函数参数只能用作数据值，而不能用于标识符，如：INSERT INTO mytable VALUES ($1); --合法

INSERT INTO $1 VALUES (42); --不合法(表名属于标示符之一)

二、基本类型：

最简单的SQL函数可能就是没有参数且返回基本类型的函数了，如：

CREATE FUNCTION one() RETURNS integer AS $$

SELECT 1 AS result;

$$ LANGUAGE SQL;

下面的例子声明了基本类型作为函数的参数。

CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$

SELECT $1 + $2;

$$ LANGUAGE SQL;

# 通过select调用函数。

postgres=# SELECT add_em(1,2) AS answer;

answer

--------

3

(1 row)

在下面的例子中，函数体内包含多个SQL语句，它们之间是用分号进行分隔的。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;

三、复合类型：

见如下示例：

1). 创建数据表，这样与之对应的复合类型也随之生成。

CREATE TABLE emp (

name text,

salary numeric,

age integer,

);

2). 创建函数，其参数为复合类型。在函数体内，可以像引用基本类型参数那样引用复合类型，如$1。访问复合类型的字段使用点表达式即可，如：$1.salary。

CREATE FUNCTION double_salary(emp) RETURNS integer AS $$

SELECT ($1.salary * 2)::integer AS salary;

$$ LANGUAGE SQL;

3). 在select语句中，可以使用emp.*表示emp表的一整行数据。

SELECT name, double_salary(emp.*) AS dream FROM emp WHERE age > 30;

4). 我们也可以使用ROW表达式构造自定义的复合类型，如：

SELECT name, double_salary(ROW(name, salary*1.1, age)) AS dream FROM emp;

5). 创建一个函数，其返回值为复合类型，如：

CREATE FUNCTION new_emp() RETURNS emp AS $$

SELECT ROW('None', 1000.0, 25)::emp;

$$ LANGUAGE SQL;

6). 调用返回复合类型的函数。

SELECT new_emp();

7). 调用返回复合类型的函数，同时访问该返回值的某个字段。

SELECT (new_emp()).name;

四、带输出参数的函数：

还有一种方法可以用于返回函数执行的结果，即输出参数，如：

CREATE FUNCTION add_em2 (IN x int, IN y int, OUT sum int) AS $$

SELECT $1 + $2

$$ LANGUAGE SQL;

调用方法和返回结果与add_em(带有返回值的函数)完全一致，如：

SELECT add_em(3,7);

这个带有输出参数的函数和之前的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);