PostgreSQL学习

基本概念

函数可以返回一个结果结果可以是一个值、一行数据、一个集合或者不返回任何值

SQL语言的函数体内有下列特性

函数体内可以包含INSERT、UPDATE和DELETE等其他标准的SQL语句但是和事物相关的语句不能包含其中如BEGIN、COMMIT、ROLLBACK和SAVEPOINT等
除非函数不返回任何值(只用于执行某些操作) 否则最后一条SQL必须是SELECT
函数体需要用美元符($$)围住

CREATE FUNCTION clean_user() RETURNS void AS
$$
    DELETE FROM user WHERE last_login < 0;
$$
LANGUAGE SQL;

函数体可以传入参数 $1代表第一个参数 $2代表第二个以此类推
函数体的传入的参数可以作为值但是不能作为标识符

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

基本类型

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

CREATE FUNCTION one() RETURNS integer AS
$$
    SELECT 1 AS result;
$$
LANGUAGE SQL;

实现一个简单的加法函数

CREATE FUNCTION add_num(integer, integer) RETURNS integer AS
$$
    SELECT $1 + $2;
$$
LANGUAGE SQL;

通过 SELECT add_num(1,2) AS result; 即可调用

 result
--------
      3
(1 row)

现在我们实现一个添加积分后返回当前积分的函数传入用户ID和添加的积分返回当前积分

CREATE FUNCTION addscore (integer, numeric) RETURNS numeric AS
$$
    UPDATE user SET score = score + $2 WHERE id = $1;
    SELECT score FROM user WHERE id = $1;
$$
LANGUAGE SQL;

复合类型

创建数据表与之对应的复合类型也随之生成

CREATE TABLE emp (
    name        text,
    salary      numeric,
    age         integer,
);

创建函数其参数为复合类型

CREATE FUNCTION double_salary(emp) RETURNS integer AS 
$$
    SELECT (emp.salary * 2)::integer AS salary;
$$
LANGUAGE SQL;

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

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

也可以使用ROW表达式构造自定义的复合类型

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

创建一个函数其返回值为复合类型

CREATE FUNCTION new_emp() RETURNS emp AS 
$$
    SELECT ROW('None', 1000.0, 25)::emp;
$$
LANGUAGE SQL;

调用返回复合类型的函数
1
SELECT new_emp();
调用返回复合类型的函数同时访问该返回值的某个字段
1
SELECT (new_emp()).name;

带输出参数的函数

相对于基本类型中的第二点这里还可以输出参数返回结果

CREATE FUNCTION add_num2(x integer, y integer, OUT sum integer) AS
$$
    SELECT $1 + $2;
$$
LANGUAGE SQL;

返回结果与 add_num 完全相同

这种返回方式真真的价值在于可以返回多个字段而不需要外部定义

CREATE FUNCTION add_and_n_num(x integer, y integer, OUT sum integer, OUT n integer) AS
$$
    SELECT $1 + $2, $1 * $2;
$$

调用方式不变 SELECT add_and_n_num(1,2);

 result | n
--------+----------
      3 | 2
(1 row)

IN 用于表示该函数参数为输入参数(缺省值，可以忽略)
OUT 则表示该参数为输出参数

返回结果作为表数据源

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

创建一个数据表

 CREATE TABLE rd (
    id    int, 
    name  text, 
    cname text
);

创建函数返回值与rd表对应

1
2
3

CREATE FUNCTION getRd(int) RETURNS rd AS $$
    SELECT * FROM rd WHERE id = $1;
$$ LANGUAGE SQL;

在FROM子句中调用该函数 SELECT *, upper(cname) FROM getRd(1) AS t1;

返回集合的SQL函数

如果SQL函数的返回值为SETOF sometype，那么在调用该函数时，将返回最后一个SELECT查询的全部数据。这个特性通常用于把函数放在FROM子句里调用。

如果不加SETOF默认所有函数只返回一条记录

1
2
3

CREATE FUNCTION getRd_gt(int) RETURNS SETOF rd AS $$
    SELECT * FROM rd WHERE id > $1;
$$ LANGUAGE SQL;

在FROM子句中调用该函数 SELECT *, upper(cname) FROM getRd_gt(1) AS t1; 即可获得id大于1的集合

函数重载

多个函数可以定义成相同的函数名，但是它们的参数一定要有所区分。换句话说，函数名可以重载，此规则有些类似于面向对象语言中的函数重载，见如下示例：

1 2	CREATE FUNCTION test(int, real) RETURNS ... CREATE FUNCTION test(smallint, double) RETURNS ...

由于在PostgreSQL中函数支持重载，因此在删除函数时，也必须指定参数列表，如：

1 2	DROP FUNCTION test(int, real); DROP FUNCTION test(smallint,double);

喵♂呜的博客

PgSql函数学习(SQL语言篇)