一个刚毕业就当爹的程序猿 正在迷雾中寻找道路...

PgSql函数学习(SQL语言篇)

PostgreSQL学习

基本概念

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

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

  • 函数体内可以包含INSERT、UPDATE和DELETE等其他标准的SQL语句 但是和事物相关的语句不能包含其中 如BEGIN、COMMIT、ROLLBACK和SAVEPOINT等
  • 除非函数不返回任何值(只用于执行某些操作) 否则最后一条SQL必须是SELECT
  • 函数体需要用美元符($$)围住
1
2
3
4
5
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'); --不合法(表名属于标示符之一)

基本类型

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

    1
    2
    3
    4
    5
    CREATE FUNCTION one() RETURNS integer AS
    $$
    SELECT 1 AS result;
    $$
    LANGUAGE SQL;
  2. 实现一个简单的加法函数

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

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

    1
    2
    3
    4
     result
    --------
    3
    (1 row)
  3. 现在我们实现一个添加积分后返回当前积分的函数 传入用户ID和添加的积分 返回当前积分

    1
    2
    3
    4
    5
    6
    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;

复合类型

  1. 创建数据表 与之对应的复合类型也随之生成
    1
    2
    3
    4
    5
    CREATE TABLE emp (
    name text,
    salary numeric,
    age integer,
    );
  2. 创建函数 其参数为复合类型
    1
    2
    3
    4
    5
    CREATE FUNCTION double_salary(emp) RETURNS integer AS 
    $$
    SELECT (emp.salary * 2)::integer AS salary;
    $$
    LANGUAGE SQL;
  3. 在select语句中 可以使用emp.*表示emp表的一整行数据
    1
    SELECT name, double_salary(emp.*) AS dream FROM emp WHERE age > 30;
  4. 也可以使用ROW表达式构造自定义的复合类型
    1
    SELECT name, double_salary(ROW(name, salary*1.1, age)) AS dream FROM emp;
  5. 创建一个函数 其返回值为复合类型
    1
    2
    3
    4
    5
    CREATE FUNCTION new_emp() RETURNS emp AS 
    $$
    SELECT ROW('None', 1000.0, 25)::emp;
    $$
    LANGUAGE SQL;
  6. 调用返回复合类型的函数
    1
    SELECT new_emp();
  7. 调用返回复合类型的函数 同时访问该返回值的某个字段
    1
    SELECT (new_emp()).name;

带输出参数的函数

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

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

    返回结果与 add_num 完全相同

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

    1
    2
    3
    4
    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);

    1
    2
    3
    4
     result | n
    --------+----------
    3 | 2
    (1 row)
  • IN 用于表示该函数参数为输入参数(缺省值,可以忽略)
  • OUT 则表示该参数为输出参数

返回结果作为表数据源

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

  1. 创建一个数据表
    1
    2
    3
    4
    5
     CREATE TABLE rd (
    id int,
    name text,
    cname text
    );
  2. 创建函数 返回值与rd表对应
    1
    2
    3
    CREATE FUNCTION getRd(int) RETURNS rd AS $$
    SELECT * FROM rd WHERE id = $1;
    $$ LANGUAGE SQL;
  3. 在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);