PostgreSQL教程(十九)：SQL语言函数
一、基本概念：
SQL函数可以包含任意数量的查询，但是函数只返回最后一个查询(必须是SELECT)的结果。在简单情况下，返回最后一条查询结果的第一行。如果最后一个查询不返回任何行，那么该函数将返回NULL值。如果需要该函数返回最后一条SELECT语句的所有行，可以将函数的返回值定义为集合，即SETOFsometype。
SQL函数的函数体应该是用分号分隔的SQL语句列表，其中最后一条语句之后的分号是可选的。除非函数声明为返回void，否则最后一条语句必须是SELECT。事实上，在SQL函数中，不仅可以包含SELECT查询语句，也可以包含INSERT、UPDATE和DELETE等其他标准的SQL语句，但是和事物相关的语句不能包含其中，如BEGIN、COMMIT、ROLLBACK和SAVEPOINT等。
CREATEFUNCTION命令的语法要求函数体写成一个字符串文本。通常来说，该文本字符串常量使用美元符($$)围住，如：
CREATEFUNCTIONclean_emp()RETURNSvoidAS$$
DELETEFROMempWHEREsalary<0;
$$LANGUAGESQL;
最后需要说明的是SQL函数中的参数，PostgreSQL定义$1表示第一个参数，$2为第二个参数并以此类推。如果参数是复合类型，则可以使用点表示法，即$1.name访问复合类型参数中的name字段。需要注意的是函数参数只能用作数据值，而不能用于标识符，如：
INSERTINTOmytableVALUES($1); --合法 INSERTINTO$1VALUES(42); --不合法(表名属于标示符之一)
二、基本类型：
最简单的SQL函数可能就是没有参数且返回基本类型的函数了，如：
CREATEFUNCTIONone()RETURNSintegerAS$$
SELECT1ASresult;
$$LANGUAGESQL;
下面的例子声明了基本类型作为函数的参数。
CREATEFUNCTIONadd_em(integer,integer)RETURNSintegerAS$$
SELECT$1+$2;
$$LANGUAGESQL;
#通过select调用函数。
postgres=#SELECTadd_em(1,2)ASanswer;
answer
--------
3
(1row)
在下面的例子中，函数体内包含多个SQL语句，它们之间是用分号进行分隔的。
CREATEFUNCTIONtf1(integer,numeric)RETURNSnumericAS$$
UPDATEbankSETbalance=balance-$2WHEREaccountno=$1;
SELECTbalanceFROMbankWHEREaccountno=$1;
$$LANGUAGESQL;
三、复合类型：
见如下示例：
1).创建数据表，这样与之对应的复合类型也随之生成。
CREATETABLEemp(
name text,
salary numeric,
age integer,
);
2).创建函数，其参数为复合类型。在函数体内，可以像引用基本类型参数那样引用复合类型，如$1。访问复合类型的字段使用点表达式即可，如：$1.salary。
CREATEFUNCTIONdouble_salary(emp)RETURNSintegerAS$$
SELECT($1.salary*2)::integerASsalary;
$$LANGUAGESQL;
3).在select语句中，可以使用emp.*表示emp表的一整行数据。
SELECTname,double_salary(emp.*)ASdreamFROMempWHEREage>30;
4).我们也可以使用ROW表达式构造自定义的复合类型，如：
SELECTname,double_salary(ROW(name,salary*1.1,age))ASdreamFROMemp;
5).创建一个函数，其返回值为复合类型，如：
CREATEFUNCTIONnew_emp()RETURNSempAS$$
SELECTROW('None',1000.0,25)::emp;
$$LANGUAGESQL;
6).调用返回复合类型的函数。
SELECTnew_emp();
7).调用返回复合类型的函数，同时访问该返回值的某个字段。
SELECT(new_emp()).name;
四、带输出参数的函数：
还有一种方法可以用于返回函数执行的结果，即输出参数，如：
CREATEFUNCTIONadd_em2(INxint,INyint,OUTsumint)AS$$
SELECT$1+$2
$$LANGUAGESQL;
调用方法和返回结果与add_em(带有返回值的函数)完全一致，如：
SELECTadd_em(3,7);
这个带有输出参数的函数和之前的add_em函数没有本质的区别。事实上，输出参数的真正价值在于它为函数提供了返回多个字段的途径。如，
CREATEFUNCTIONsum_n_product(xint,yint,OUTsumint,OUTproductint)AS$$
SELECT$1+$2,$1*$2
$$LANGUAGESQL;
调用方式没有改变，只是返回结果多出一列。
SELECT*FROMsum_n_product(11,42);
sum|product
-----+---------
53| 462
(1row)
在上面的示例中，IN用于表示该函数参数为输入参数(缺省值，可以忽略)，OUT则表示该参数为输出参数。
五、返回结果作为表数据源：
所有SQL函数都可以在查询的FROM子句里使用。该方法对于返回复合类型的函数而言特别有用，如果该函数定义为返回一个基本类型，那么该函数生成一个单字段表，如果该函数定义为返回一个复合类型，那么该函数生成一个复合类型里每个属性组成的行。见如下示例：
1).创建一个数据表。
CREATETABLEfoo(
fooid int,
foosubidint,
fooname text
);
2).创建SQL函数，其返回值为与foo表对应的复合类型。
CREATEFUNCTIONgetfoo(int)RETURNSfooAS$$
SELECT*FROMfooWHEREfooid=$1;
$$LANGUAGESQL;
3).在FROM子句中调用该函数。
SELECT*,upper(fooname)FROMgetfoo(1)ASt1;
六、返回集合的SQL函数：
如果SQL函数的返回值为SETOFsometype，那么在调用该函数时，将返回最后一个SELECT查询的全部数据。这个特性通常用于把函数放在FROM子句里调用，见如下示例：
CREATEFUNCTIONgetfoo(int)RETURNSsetoffooAS$$
SELECT*FROMfooWHEREfooid=$1;
$$LANGUAGESQL;
在FROM子句中调用了返回复合类型集合的函数，其结果等同于：SELECT*FROM(SELECT*FROMfooWHEREfooid=1)t1;
SELECT*FROMgetfoo(1)ASt1;
七、多态的SQL函数：
SQL函数可以声明为接受多态类型(anyelement和anyarray)的参数或返回多态类型的返回值，见如下示例：
1).函数参数和返回值均为多态类型。
CREATEFUNCTIONmake_array(anyelement,anyelement)RETURNSanyarrayAS$$
SELECTARRAY[$1,$2];
$$LANGUAGESQL;
其调用方式和调用其它类型的SQL函数完全相同，只是在传递字符串类型的参数时，需要显式转换到目标类型，否则将会被视为unknown类型，如：
SELECTmake_array(1,2)ASintarray,make_array('a'::text,'b')AStextarray;
2).函数的参数为多态类型，而返回值则为基本类型。
CREATEFUNCTIONis_greater(anyelement,anyelement)RETURNSbooleanAS$$
SELECT$1>$2;
$$LANGUAGESQL;
3).多态类型用于函数的输出参数。
CREATEFUNCTIONdup(f1anyelement,OUTf2anyelement,OUTf3anyarray)AS$$
SELECT$1,ARRAY[$1,$1]
$$LANGUAGEsql;
八、函数重载：
多个函数可以定义成相同的函数名，但是它们的参数一定要有所区分。换句话说，函数名可以重载，此规则有些类似于面向对象语言中的函数重载，见如下示例：
CREATEFUNCTIONtest(int,real)RETURNS... CREATEFUNCTIONtest(smallint,double)RETURNS...
由于在PostgreSQL中函数支持重载，因此在删除函数时，也必须指定参数列表，如：
DROPFUNCTIONtest(int,real); DROPFUNCTIONtest(smallint,double);