postgresql PL/pgSQL—存储过程结构和变量声明

ref: https://www.postgresql.org/docs/9.6/static/plpgsql-structure.html

一. 函数结构

CREATE FUNCTION somefunc(integer, text) RETURNS integer
AS 'function body text'
LANGUAGE plpgsql;

PL/pgSQL是块结构（block-structured）语言，上面示例里的function body必须是一个块（block），块结构如下

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
END [ label ];

块可以嵌套，嵌套块里可以通过外层块的标签来调用外层块的同名变量，示例：

 CREATE FUNCTION somefunc() RETURNS integer AS $$
<< outerblock >>
DECLARE
    quantity integer := 30;
BEGIN
    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 30
    quantity := 50;
    --
    -- Create a subblock
    --
    DECLARE
        quantity integer := 80;
    BEGIN
        RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 80
        RAISE NOTICE 'Outer quantity here is %', outerblock.quantity;  -- Prints 50
    END;

    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 50

    RETURN quantity;
END;
$$ LANGUAGE plpgsql;

* PL/pgSQL的BEGIN/END与事务无关，只起分隔块的作用；

* Function和trigger不能发起或提交一个事务，只能运行在外部查询发起的事务里，因为其没有事务的上下文；

* EXCEPTION表达式可以处理存储过程里的错误，类似Java里的catch（see: PLPGSQL-ERROR-TRAPPING）

 

二. 变量声明（Declarations）

1. 块声明域

name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];

示例：

i integer;
quantity numeric(5);
quantity integer DEFAULT 32;
url varchar := 'http://mysite.com';
id_user CONSTANT integer := 10;

2. 函数形参

函数形参主要有两种方式调用

1) 命名参数，如：

CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

2) 通过$n调用，如

CREATE FUNCTION sales_tax(real) RETURNS real AS $$
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

如上的函数形参传参方式都是值传递，可以通过OUT关键字将形参设置为引用传递，示例：

CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
BEGIN
    tax := subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

这里跟前面示例的sales_tax功能一样，只是前面是作为返回值返回，而这里将计算结果记录在了引用传递的参数里，外部可以在调用函数后访问tax变量获取函数计算结果。这就像C里的指针变量一样，也类似C#里参数的ref关键字。

3.  Alias

可以给变量赋予别名

newname ALIAS FOR oldname;

示例

DECLARE
  prior ALIAS FOR old;
  updated ALIAS FOR new;

4. 复制数据类型（Copying Types）

variable%TYPE

%TYPE能获取一个变量或列的数据类型。要声明一个跟表users的列id_user同样数据类型的变量，可以通过如下方式实现：

id_user users.id_user%TYPE;

通过复制数据类型的方式，我们可以在不知道引用对象数据类型的情况下就进行引用，更关键的是当引用对象的数据类型改变时，我们的引用代码并不需要跟着改动。

5. 复制列（Row Types）

name table_name%ROWTYPE;

table_name%ROWTYPE可以复制整个表所有用户定义的列（非OID或者其他表系统列）

示例

    CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
DECLARE
    t2_row table2%ROWTYPE;
BEGIN
    SELECT * INTO t2_row FROM table2 WHERE ... ;
    RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
END;
$$ LANGUAGE plpgsql;

SELECT merge_fields(t.*) FROM table1 t WHERE ... ;