PostgreSQL开发技术基础:过程与函数

PostgreSQL开发技术基础:过程与函数

扩展SQL

PostgreSQL是可扩展的。PostgreSQL和标准关系型数据库系统的一个关键不同是PostgreSQL在其目录中存储更多信息：不只是有关表和列的信息，还有关于数据类型、函数、访问方法等等的信息。这些表可以被用户修改，并且因为PostgreSQL的操作是基于这些表的，所以PostgreSQL可以被用户扩展。

PostgreSQL服务器能够通过动态库(载入)把用户编写的代码结合到它自身中。

类型系统

基础类型

容器类型

域

伪类型 (Pseudo-Types)

有一些用于特殊目的“伪类型”。伪类型不能作为表列或者容器类型的组件出现，但是它们能被用于声明函数的参数和结果类型。这在类型系统中提供了一种机制来标识函数的特殊分类。

• any

• anyelement

• anyarray

• anyrange

• cstring

• internal

• fdw_handler

• record

• trigger

• event_trigger

• pg_ddl_command

• void

• unknown

多态类型

在伪类型当中，有一些比较重要的有用的类型属于多态类型。允许单个函数定义对许多不同的数据类型进行操作，具体数据类型由特定调用中实际传递给它的数据类型确定。主要是any打头的那些伪类型。

用户定义的函数

1) 查询语言函数(用SQL编写的函数)
2) 过程语言函数(PL/pgSQL或PL/Tcl等编写的函数)，过程语言并不内建在PostgreSQL服务器中，它们通过可装载模块提供。
3) 内部函数

内部函数由 C 编写并且已经被静态链接到PostgreSQL    服务器中。该函数定义的“主体”指定该函数的 C 语言名称，    它必须和声明 SQL 函数所用的名称一样（为了向后兼容性的原因，也接受空    主体，那时会认为 C 语言函数名与 SQL 函数名相同）。

通常，所有存在于服务器中的内部函数都在数据库集簇的初始化（见    第 19.2 节）期间被声明，但是用户可以使用    CREATE FUNCTION
为一个内部函数创建    额外的别名。在CREATE FUNCTION
中用    语言名internal
来声明内部函数。例如，要为    sqrt
函数创建一个别名：

1CREATE FUNCTION square_root(double precision) RETURNS double precision
2    AS 'dsqrt'
3    LANGUAGE internal
4    STRICT;

大部分内部函数应该被声明为“STRICT”

1. C语言函数

以动态库的方式实现 ，参考：http://postgres.cn/docs/14/xfunc-c.html

用户定义的过程

1CREATE PROCEDURE insert_data(a integer, b integer)
2LANGUAGE SQL
3AS $$
4INSERT INTO t1 VALUES (a);
5INSERT INTO t1 VALUES (b);
6$$;
7
8CALL insert_data(1, 2);

用CALL来调用，不能用select

过程和函数可以统称为例程(routine)。可以直接用drop来删除，不用考虑是哪一类型。不过没有CREATE routine命令.

1mydb=# drop routine square_root;
2DROP ROUTINE
3mydb=# drop routine insert_data;
4DROP ROUTINE
5ALTER ROUTINE insert_data(integer, integer) RENAME TO insert_d;

查询语言函数

(refer:  http://postgres.cn/docs/14/xfunc-sql.html)

过程语言概览

• PostgreSQL允许使用除了 SQL 和 C 之外的其他语言编写用户定义的函数。这些其他的语言通常被称作过程语言（PL）。对于一个用过程语言编写的函数，数据库服务器没有关于如何解释该函数的源文本的内建知识。因此，这个任务被交给一个了解语言细节的特殊处理器。该处理器能够自己处理所有的解析、语法分析、执行工作，或者它可以作为一种PostgreSQL和编程语言既有实现之间的“粘合剂”。

• PostgreSQL 允许用户自定义的函数，它可以用各种过程语言编写。数据库服务器是没 有任何内建的知识获知如何解析该函数的源文本的。实际上这些任务都传递给一个知 道如何处理这些细节的句柄(handler )处理。

• PostgreSQL 当前支持多个标准的过程语言：

• PL/pgSQL  

• PL/Tcl  

• PL/Perl

• PL/Python  

• PL/Java

• PL/Ruby

• 其它语言可以由用户自定义

1postgres=# select * from pg_language;
2  oid  | lanname  | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl
3-------+----------+----------+---------+--------------+---------------+-----------+--------------+--------
4    12 | internal |       10 | f       | f            |             0 |         0 |         2246 |
5    13 | c        |       10 | f       | f            |             0 |         0 |         2247 |
6    14 | sql      |       10 | f       | t            |             0 |         0 |         2248 |
7 14476 | plpgsql  |       10 | t       | t            |         14473 |     14474 |        14475 |
8(4 rows)

安装过程 语言的过程 见:  P126

https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-8-x86_64/

 1yum install postgresql14-plpython3
 2yum install postgresql14-plpython3-14.4  //具体的14的子版本
 3
 4mydb=# \dx
 5                         List of installed extensions
 6    Name    | Version |   Schema   |                Description
 7------------+---------+------------+-------------------------------------------
 8 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
 9 plpython3u | 1.0     | pg_catalog | PL/Python3U untrusted procedural language
10(2 rows)

 1使用pl/Python的一个简单示例：
 2create extension if not exists plpython3u;
 3
 4create function pymax (a integer, b integer)
 5returns integer
 6as $$
 7    if a > b:
 8        return a;
 9    return b;
10$$ language plpython3u;
11
12select pymax(1,99);
13mydb2=# select pymax(1,99);
14 pymax
15-------
16    99
17(1 row)

6.0 Language SQL与PL/pgSQL

在后续介绍之前，有必要知道SQL和PL/pgSQL之间的一些区别

https://www.dovov.com/postgresqlsqlplpgsql.html  
https://stackoverflow.com/questions/24755468/difference-between-language-sql-and-language-plpgsql-in-postgresql-functions

(language SQL与language plpgsql的区别)

PL PgSQL是基于SQL的特定于PostgreSQL的过程语言 。它有循环，variables，错误/exception处理等等。并不是所有的SQL都是有效的PL PgSQL，正如你发现的那样，例如，你不能在没有INTO
或RETURN QUERY
情况下使用SELECT
。PL PgSQL也可以在DO
块中用于一次性程序。

sql
函数只能使用纯SQL，但通常效率更高，写起来更简单，因为您不需要BEGIN ... END;
块等.SQL函数可以内联，这对于PL PgSQL来说是不正确的。

人们经常使用PL PgSQL，因为它们习惯于程序思考。在大多数情况下，当你认为你需要PL PgSQL时，你可能不需要。recursionCTE，横向查询等通常满足大多数需求。

6.1 PL/pgSQL

6.1.1 概览

• PL/pgSQL是 PostgreSQL 数据库系统的一个可装载的过程语言 。

• PL/pgSQL 有一些特性:

1. 可用于创建函数和触发器过程

2. 为 SQL 语言增加控制结构

3. 可以执行复杂的计算 ,

4. 继承所有用户定义类型，函数和操作符

5. 可以定义为被服务器信任的语言 ,

6. 容易使用

6.1.2 PL/pgSQL和执行计划

• 当函数第一次被调用的时候(在每个会话中)，PL/pgSQL 调用句柄产生一个二进制 指令树

• 该指令树完全转换了 PL/pgSQL 语句结构， 但是在函数内使用到的独立的 SQL 表 达式和 SQL 命令并未立即转换

• 在每个函数中用到的表达式和 SQL 命令在函数里首次使用的时候，PL/pgSQL 解释 器创建一个准备好的执行规划

• 随后对该表达式或者命令的访问都将在数据库连接的整个生命周期内使用已准备 好的规划。

6.1.3 PL/pgSQL结构

• PL/pgSQL是一种块结构(block-structured)的语言。函数定义的所有文本都必须是一个 块。一个块用下面的方法定义：

1[ <<Label>> ]   
2[ DECLARE 
3     declarations ] -- 声明部分
4BEGIN 
5    statements 
6END [ label ]; 

• 块中的每个声明和每条语句都是用一个分号终止的， 如果一个子块在另外一个块里， 那么 END 后面必须有个分号，如上所述；不过结束函数体的最后的 END 可以不要这 个分号。提示：BEGIN后边是不需要分号的

• 所有的关键字和标识符(identifiers)可以大小写混用。如果没有双引号，标识 符默认转换为小写。

• 在 PL/pgSQL中有两种注释

• -- 单行注释

• /* 多行注释 */

• 在块前面的位于声明部分的变量会在每次块载入的时候(不是每次函数被调 用的时候)被初始化为默认值。

一个示例如下：

 1CREATE FUNCTION somefunc() RETURNS integer AS $$
 2<< outerblock >>
 3DECLARE
 4    quantity integer := 30;
 5BEGIN
 6    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 30
 7    quantity := 50;
 8    --
 9    -- 创建一个子块
10    --
11    DECLARE
12        quantity integer := 80;
13    BEGIN
14        RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 80
15        RAISE NOTICE 'Outer quantity here is %', outerblock.quantity;  -- Prints 50
16    END;
17    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 50
18    RETURN quantity;
19END;
20$$ LANGUAGE plpgsql;

6.1.4 声明

在一个块中使用的所有变量必须在该块的声明小节中声明（唯一的例外是在一个整数范围上迭代的FOR
循环变量会被自动声明为一个整数变量，并且相似地在一个游标结果上迭代的FOR
循环变量会被自动地声明为一个记录变量）。

PL/pgSQL变量可以是任意 SQL 数据类型，例如integer
、varchar
和char
。   如：

1user_id integer;
2quantity numeric(5);
3url varchar;
4myrow tablename%ROWTYPE;
5myfield tablename.columnname%TYPE;
6arow RECORD;

声明的语法如下：

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

如果给定DEFAULT
子句，它会指定进入该块时分      配给该变量的初始值。如果没有给出DEFAULT
子句，      则该变量被初始化为SQL空值。CONSTANT
选项阻止该变量在初始化之后被赋值，这样它的值在块的持续期内保持不变。

一个变量的默认值会在每次进入该块时被计算并且赋值给该变量（不是每次函数调用只计算一次）。因此，例如将now()
赋值给类型为timestamp
的一个变量将会导致该变量具有当前函数调用的时间，而不是该函数被预编译的时间。

1quantity integer DEFAULT 32;
2url varchar := 'http://mysite.com';
3user_id CONSTANT integer := 10;

声明函数参数

传递给函数的参数被命名为标识符$1
、$2
等等。可选地，能够为$*
n*
参数名声明别名来增加可读性。不管是别名还是数字标识符都能用来引用参数值。

有两种方式来创建一个别名。比较好的方式是在CREATE FUNCTION
命令中为参数给定一个名称。例如：

1CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
2BEGIN
3    RETURN subtotal * 0.06;
4END;
5$$ LANGUAGE plpgsql;

name ALIAS FOR

1CREATE FUNCTION sales_tax(real) RETURNS real AS $$
2DECLARE
3    subtotal ALIAS FOR $1;
4BEGIN
5    RETURN subtotal * 0.06;
6END;
7$$ LANGUAGE plpgsql;

当一个PL/pgSQL函数被声明为带有输出参数，输出参数可以用普通输入参数相同的方式被给定$*
n*
名称以及可选的别名。一个输出参数实际上是一个最初为 NULL 的变量，它应当在函数的执行期间被赋值。该参数的最终值就是要被返回的东西。例如，sales-tax 例子也可以用这种方式来做，也可以返回多值。

 1CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
 2BEGIN
 3    tax := subtotal * 0.06;
 4END;
 5$$ LANGUAGE plpgsql;
 6
 7select sales_tax(12345);
 8
 9CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
10BEGIN
11    sum := x + y;
12    prod := x * y;
13END;
14$$ LANGUAGE plpgsql;
15-- 这实际上为该函数的结果创建了一个匿名记录类型。如果给定了一个RETURNS子句，它必须RETURNS record。 
16mydb2=# select sum_n_product(1, 2);
17 sum_n_product
18---------------
19 (3,2)
20(1 row)

声明一个PL/pgSQL函数的另一种方式是用RETURNS TABLE
，例如：

1CREATE FUNCTION extended_sales(p_itemno int)
2RETURNS TABLE(quantity int, total numeric) AS $$
3BEGIN
4    RETURN QUERY SELECT s.quantity, s.quantity * s.price FROM sales AS s
5                 WHERE s.itemno = p_itemno;
6END;
7$$ LANGUAGE plpgsql;

当PL/pgSQL函数的返回类型被声明为多态类型时，一个特殊的参数 $0
已创建。它的数据类型是函数的实际返回类型，从实际输入类型推导出来。     $0
被初始化为空并且不能被该函数修改，因此它能够被用来保持可能需要的返回值，不过这不是必须的。     $0
也可以被给定一个别名。例如，这个函数工作在任何具有一个+
操作符的数据类型上：

通过声明一个或多个输出参数为多态类型可以得到同样的效果。在这种情况下，不使用特殊的$0
参数，输出参数本身就用作相同的目的。例如：

 1CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
 2RETURNS anyelement AS $$
 3DECLARE
 4    result ALIAS FOR $0;
 5BEGIN
 6    result := v1 + v2 + v3;
 7    RETURN result;
 8END;
 9$$ LANGUAGE plpgsql;
10----- ==>
11CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement,
12                                 OUT sum anyelement)
13AS $$
14BEGIN
15    sum := v1 + v2 + v3;
16END;
17$$ LANGUAGE plpgsql;

在实践中，使用anycompatible
类型系列声明多态函数可能更有用，以便将输入参数自动提升为公共类型。例如：

1CREATE FUNCTION add_three_values(v1 anycompatible, v2 anycompatible, v3 anycompatible)
2RETURNS anycompatible AS $$
3BEGIN
4    RETURN v1 + v2 + v3;
5END;
6$$ LANGUAGE plpgsql;
7SELECT add_three_values(1, 2, 4.7);

Alias

1newname ALIAS FOR oldname;

可以为任意变量声明一个别名，而不只是函数参数。其主要实际用途是为预先决定了名称的变量分配一个不同的名称，例如在一个触发器过程中的NEW
或OLD
。  例子：

1DECLARE
2  prior ALIAS FOR old;
3  updated ALIAS FOR new;

因为ALIAS
创造了两种不同的方式来命名相同的对象，如果对其使用不加限制就会导致混淆。最好只把它用来覆盖预先决定的名称。

复制类型 (Copying types)

1variable%TYPE

%TYPE
提供了一个变量或表列的数据类型。你可以用它来声明将保持数据库值的变量。例如，如果你在users
中有一个名为user_id
的列。要定义一个与users.user_id
具有相同数据类型的变量：user_id users.user_id%TYPE;
通过使用%TYPE
，你不需要知道你要引用的结构的实际数据类型，而且最重要地，如果被引用项的数据类型在未来被改变（例如你把user_id
的类型从integer
改为real
），你不需要改变你的函数定义。 
%TYPE
在多态函数中特别有价值，因为内部变量所需的数据类型能在两次调用时改变。可以把%TYPE
应用在函数的参数或结果占位符上来创建合适的变量。

行类型(Row Type)

1name table_name%ROWTYPE;
2name composite_type_name;

一个组合类型的变量被称为一个行变量（或行类型变量）。这样一个变量可以保持一个SELECT
或FOR
查询结果的一整行，前提是查询的列集合匹配该变量被声明的类型。该行值的各个域可以使用通常的点号标记访问，例如rowvar.field
。

通过使用table_name
%ROWTYPE
标记，一个行变量可以被声明为具有和一个现有表或视图的行相同的类型。它也可以通过给定一个组合类型名称来声明（因为每一个表都有一个相关联的具有相同名称的组合类型，所以在PostgreSQL中实际上写不写%ROWTYPE
都没有关系。但是带有%ROWTYPE
的形式可移植性更好）。

一个函数的参数可以是组合类型（完整的表行）。在这种情况下，相应的标识符n;使用这种风格的同一个例子看起来是：¨G12G当一个PL/pgSQL函数被声明为带有输出参数，输出参数可以用普通输入参数相同的方式被给定¨C123Cn¨C124C名称以及可选的别名。一个输出参数实际上是一个最初为NULL的变量，它应当在函数的执行期间被赋值。该参数的最终值就是要被返回的东西。例如，sales−tax例子也可以用这种方式来做，也可以返回多值。¨G13G声明一个PL/pgSQL函数的另一种方式是用¨C125C，例如：¨G14G当PL/pgSQL函数的返回类型被声明为多态类型时，一个特殊的参数¨C126C已创建。它的数据类型是函数的实际返回类型，从实际输入类型推导出来。¨C127C被初始化为空并且不能被该函数修改，因此它能够被用来保持可能需要的返回值，不过这不是必须的。¨C128C也可以被给定一个别名。例如，这个函数工作在任何具有一个¨C129C操作符的数据类型上：通过声明一个或多个输出参数为多态类型可以得到同样的效果。在这种情况下，不使用特殊的¨C130C参数，输出参数本身就用作相同的目的。例如：¨G15G在实践中，使用¨C131C类型系列声明多态函数可能更有用，以便将输入参数自动提升为公共类型。例如：¨G16G¨K173K¨G17G可以为任意变量声明一个别名，而不只是函数参数。其主要实际用途是为预先决定了名称的变量分配一个不同的名称，例如在一个触发器过程中的¨C132C或¨C133C。例子：¨G18G因为¨C134C创造了两种不同的方式来命名相同的对象，如果对其使用不加限制就会导致混淆。最好只把它用来覆盖预先决定的名称。¨K174K¨G19G¨C135C提供了一个变量或表列的数据类型。你可以用它来声明将保持数据库值的变量。例如，如果你在¨C136C中有一个名为¨C137C的列。要定义一个与¨C138C具有相同数据类型的变量：useridusers.userid通过使用¨C139C，你不需要知道你要引用的结构的实际数据类型，而且最重要地，如果被引用项的数据类型在未来被改变（例如你把¨C140C的类型从¨C141C改为¨C142C），你不需要改变你的函数定义。¨C143C在∗∗多态函数中特别有价值∗∗，因为内部变量所需的数据类型能在两次调用时改变。可以把¨C144C应用在函数的参数或结果占位符上来创建合适的变量。¨K175K¨G20G一个组合类型的变量被称为一个∗行∗变量（或∗行类型∗变量）。这样一个变量可以保持一个¨C145C或¨C146C查询结果的一整行，前提是查询的列集合匹配该变量被声明的类型。该行值的各个域可以使用通常的点号标记访问，例如¨C147C。通过使用∗¨C148C∗¨C149C标记，一个行变量可以被声明为具有和一个现有表或视图的行相同的类型。它也可以通过给定一个组合类型名称来声明（因为每一个表都有一个相关联的具有相同名称的组合类型，所以在PostgreSQL中实际上写不写¨C150C都没有关系。但是带有¨C151C的形式可移植性更好）。一个函数的参数可以是组合类型（完整的表行）。在这种情况下，相应的标识符n将是一个行变量，并且可以从中选择域，例如$1.user_id
。

 1CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
 2DECLARE
 3    t2_row table2%ROWTYPE;
 4BEGIN
 5    SELECT * INTO t2_row FROM table2 WHERE ... ;
 6    RETURN t_row.f1 || t2_row.f3 || t_row.f2 || t2_row.f4;
 7END;
 8$$ LANGUAGE plpgsql;
 9
10SELECT merge_fields(t.*) FROM table1 t WHERE ... ;

记录类型

1name RECORD;

记录变量和行类型变量类似，但是它们没有预定义的结构。它们采用在一个SELECT
或FOR
命令期间为其赋值的行的真实行结构。注意RECORD
并非一个真正的数据类型，只是一个占位符。我们也应该认识到当一个PL/pgSQL函数被声明为返回类型record
，这与一个记录变量并不是完全相同的概念，即便这样一个函数可能会用一个记录变量来保持其结果。

PL/pgSQL变量的排序规则

当一个PL/pgSQL函数有一个或多个可排序数据类型的参数时，为每一次函数调用都会基于赋值给实参的排序规则来确定出一个排序规则，如果一个排序规则被成功地确定（即在参数之间隐式排序规则没有冲突），那么所有的可排序参数会被当做隐式具有那个排序规则。这将在函数中影响行为受到排序规则影响的操作。例如，考虑

1CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
2BEGIN
3    RETURN a < b;
4END;
5$$ LANGUAGE plpgsql;
6
7SELECT less_than(text_field_1, text_field_2) FROM table1;
8SELECT less_than(text_field_1, text_field_2 COLLATE "C") FROM table1;

less_than
的第一次使用将会采用text_field_1
和text_field_2
共同的排序规则进行比较，而第二次使用将采用C
排序规则。

此外，被确定的排序规则也被假定为任何可排序数据类型本地变量的排序规则。因此，当这个函数被写为以下形式时，它工作将不会有什么不同。

1CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
2DECLARE
3    local_a text := a;
4    local_b text := b;
5BEGIN
6    RETURN local_a < local_b;
7END;
8$$ LANGUAGE plpgsql;

通过在一个可排序数据类型的本地变量的声明中包括COLLATE
选项，可以为它指定一个不同的排序规则，例如

1DECLARE
2    local_a text COLLATE "en_US";

还有，如果一个函数想要强制在一个特定操作中使用一个特定排序规则，当然可以在该函数内部写一个显式的COLLATE
子句。例如：

1CREATE FUNCTION less_than_c(a text, b text) RETURNS boolean AS $$
2BEGIN
3    RETURN a < b COLLATE "C";
4END;
5$$ LANGUAGE plpgsql;

这会覆盖表达式中使用的表列、参数或本地变量相关的排序规则，就像在纯 SQL 命令中发生的一样。

PL/pgSQL 表达式

PL/pgSQL语句中用到的所有表达式会被服务器的主SQL执行器处理。例如，当你写一个这样的PL/pgSQL语句时

1IF expression THEN ...

PL/pgSQL将通过给主 SQL 引擎发送一个查询

1SELECT expression

来计算该表达式。在构造该SELECT
命令时，PL/pgSQL变量名的每一次出现会被参数所替换。这允许SELECT
的查询计划仅被准备一次并且被重用于之后的对于该变量不同值的计算。因此，在一个表达式第一次被使用时实际发生的本质上是一个PREPARE
命令。例如，如果我们已经声明了两个整数变量x
和y
，并且我们写了

1IF x < y THEN ...

在现象之后发生的等效于

1PREPARE statement_name(integer, integer) AS SELECT $1 < $2;

并且然后为每一次IF
语句的执行，这个预备语句都会被EXECUTE
，执行时使用变量的当前值作为参数值。通常这些细节对于一个PL/pgSQL用户并不重要，但是在尝试诊断一个问题时了解它们很有用。

PL/pgSQL基本语句

赋值

为一个PL/pgSQL变量赋一个值可以被写为：

1variable { := | = } expression;

正如以前所解释的，这样一个语句中的表达式被以一个 SQL      SELECT
命令被发送到主数据库引擎的方式计算。     该表达式必须得到一个单一值（如果该变量是一个行或记录变量，      它可能是一个行值）。该目标变量可以是一个简单变量（      可以选择用一个块名限定）、一个行或记录变量的域或是一个简单      变量或域的数组元素。    等号（=
）可以被用来代替 PL/SQL-兼容的      :=
。

如果该表达式的结果数据类型不匹配变量的数据类型，该值将被强制转换。

1tax := subtotal * 0.06;
2my_record.user_id := 20;    

执行一个没有结果的命令

对于任何不返回行的 SQL 命令（例如没有一个RETURNING
子句的INSERT
），你可以通过把该命令直接写在一个 PL/pgSQL 函数中执行它。   任何出现在该命令文本中的PL/pgSQL变量名被当作一个参数，并且接着该变量的当前值被提供为运行时该参数的值。

有时候计算一个表达式或SELECT
查询但抛弃其结果是有用的，例如调用一个有副作用但是没有有用的结果值的函数。在PL/pgSQL中要这样做，可使用PERFORM
语句：

1PERFORM query;

这会执行query
并且丢弃掉结果。以写一个SQL SELECT
命令相同的方式写该query
，并且将初始的关键词SELECT
替换为PERFORM
。对于WITH
查询，使用PERFORM
并且接着把该查询放在圆括号中（在这种情况中，该查询只能返回一行）。PL/pgSQL变量将被替换到该查询中，正像对不返回结果的命令所作的那样，并且计划被以相同的方式被缓存。

1PERFORM create_mv('cs_session_page_requests_mv', my_query);

执行一个有单一行结果的查询

一个产生单一行（可能有多个列）的 SQL 命令的结果可以被赋值给一个记录变量、行类型变量或标量变量列表。

1SELECT select_expressions INTO [STRICT] target FROM ...;
2INSERT ... RETURNING expressions INTO [STRICT] target;
3UPDATE ... RETURNING expressions INTO [STRICT] target;
4DELETE ... RETURNING expressions INTO [STRICT] target;

其中target
可以是一个记录变量、一个行变量或一个有逗号分隔的简单变量和记录/行域列表。

如果STRICT
没有在INTO
子句中被指定，那么target
将被设置为该查询返回的第一个行，或者在该查询不返回行时设置为空（注意除非使用了ORDER BY
，否则“第一行”的界定并不清楚）

1SELECT * INTO myrec FROM emp WHERE empname = myname;
2IF NOT FOUND THEN
3    RAISE EXCEPTION 'employee % not found', myname;
4END IF;

如果指定了STRICT
选项，该查询必须刚好返回一行或者将会报告一个运行时错误，该错误可能是NO_DATA_FOUND
（没有行）或TOO_MANY_ROWS
（多于一行）。成功执行一个带STRICT
的命令总是会将FOUND
置为真。

1BEGIN
2    SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
3    EXCEPTION
4        WHEN NO_DATA_FOUND THEN
5            RAISE EXCEPTION 'employee % not found', myname;
6        WHEN TOO_MANY_ROWS THEN
7            RAISE EXCEPTION 'employee % not unique', myname;
8END;

 1CREATE FUNCTION get_userid(username text) RETURNS int
 2AS $$
 3#print_strict_params on
 4DECLARE
 5userid int;
 6BEGIN
 7    SELECT users.userid INTO STRICT userid
 8        FROM users WHERE users.username = get_userid.username;
 9    RETURN userid;
10END;
11$$ LANGUAGE plpgsql;

STRICT
选项完全匹配 Oracle PL/SQL 的SELECT INTO
和相关语句的行为。

PL/pgSQL 执行动态命令

很多时候你将想要在PL/pgSQL函数中产生动态命令，也就是每次执行中会涉及到不同表或不同数据类型的命令。PL/pgSQL通常对于命令所做的缓存计划尝试在这种情境下无法工作。要处理这一类问题，需要提供EXECUTE
语句：

1EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

在计算得到的命令字符串中，不会做PL/pgSQL变量的替换。任何所需的变量值必须在命令字符串被构造时被插入其中，或者你可以使用下面描述的参数。   命令字符串可以使用参数值，它们在命令中用$1
、$2
等引用。这些符号引用在USING
子句中提供的值。这种方法常常更适合于把数据值作为文本插入到命令字符串中：它避免了将该值转换为文本以及转换回来的运行时负荷，并且它更不容易被 SQL 注入攻击，因为不需要引用或转义。一个例子是：

1EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
2   INTO c
3   USING checked_user, checked_date;

需要注意的是，参数符号只能用于数据值 — 如果想要使用动态决定的表名或列名，你必须将它们以文本形式插入到命令字符串中。例如，如果前面的那个查询需要在一个动态选择的表上执行，你可以这么做：

1EXECUTE 'SELECT count(*) FROM '
2    || quote_ident(tabname)
3    || ' WHERE inserted_by = $1 AND inserted <= $2'
4   INTO c
5   USING checked_user, checked_date;

一种更干净的方法是为表名或者列名使用format()
的     %I
规范（被新行分隔的字符串会被串接起来）：

1EXECUTE format('SELECT count(*) FROM %I '
2   'WHERE inserted_by = $1 AND inserted <= $2', tabname)
3   INTO c
4   USING checked_user, checked_date;

在动态查询中引用值,  如：动态值需要被小心地处理，因为它们可能包含引号字符。一个使用     format()
的例子（这假设你用美元符号引用了函数     体，因此引号不需要被双写）

1EXECUTE format('UPDATE tbl SET %I = $1 '
2   'WHERE key = $2', colname) USING newvalue, keyvalue;
3EXECUTE 'UPDATE tbl SET '
4        || quote_ident(colname)
5        || ' = '
6        || quote_literal(newvalue)
7        || ' WHERE key = '
8        || quote_literal(keyvalue);

为了安全，在进行一个动态查询中的插入之前，包含列或表标识符的表达式应该通过quote_ident
被传递。如果表达式包含在被构造出的命令中应该是字符串的值时，它应该通过quote_literal
被传递。这些函数采取适当的步骤来分别返回被封闭在双引号或单引号中的文本，其中任何嵌入的特殊字符都会被正确地转义。

动态 SQL 语句也可以使用format
函数来安全地构造。例如：

1EXECUTE format('UPDATE tbl SET %I = %L '
2   'WHERE key = %L', colname, newvalue, keyvalue);

%I
等效于quote_ident
并且     %L
等效于quote_nullable
。    format
函数可以和     USING
子句一起使用：

1EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
2   USING newvalue, keyvalue;

这种形式更好，因为变量被以它们天然的数据类型格式处理，而不是无     条件地把它们转换成文本并且通过%L
引用它们。这也效率     更高。

获得结果状态

有好几种方法可以判断一条命令的效果。第一种方法是使用GET DIAGNOSTICS
命令，其形式如下：

1GET [ CURRENT ] DIAGNOSTICS variable { = | := } item [ , ... ];

这条命令允许检索系统状态指示符。每个item
是一个关键字， 它标识一个要被赋予给指定变量
的状态值（变量应具有正确的数据类型来接收状态值）, 如：

1GET DIAGNOSTICS integer_var = ROW_COUNT;

可用的诊断项

第二种判断命令效果的方法是检查一个名为FOUND
的boolean
类型的特殊变量。在每一次PL/pgSQL函数调用时，FOUND
开始都为假。它的值会被下面的每一种类型的语句设置：

• 如果一个SELECT INTO
  语句赋值了一行，它将把FOUND
  设置为真，如果没有返回行则将之设置为假。

• 如果一个PERFORM
  语句生成（并且抛弃）一行或多行，它将把FOUND
  设置为真，如果没有产生行则将之设置为假。

• 如果UPDATE
  、INSERT
  以及DELETE
  语句影响了至少一行，它们会把FOUND
  设置为真，如果没有影响行则将之设置为假。

• 如果一个FETCH
  语句返回了一行，它将把FOUND
  设置为真，如果没有返回行则将之设置为假。

• 如果一个MOVE
  语句成功地重定位了游标，它将会把FOUND
  设置为真，否则设置为假。

• 如果一个FOR
  或FOREACH
  语句迭代了一次或多次，它将会把FOUND
  设置为真，否则设置为假。当循环退出时，FOUND
  用这种方式设置；在循环执行中，尽管FOUND
  可能被循环体中的其他语句的执行所改变，但它不会被循环语句修改。

• 如果查询返回至少一行，RETURN QUERY
  和RETURN QUERY EXECUTE
  语句会把FOUND
  设为真， 如果没有返回行则设置为假。

其他的PL/pgSQL语句不会改变FOUND
的状态。尤其需要注意的一点是：EXECUTE
会修改GET DIAGNOSTICS
的输出，但不会修改FOUND
的输出。

FOUND
是每个PL/pgSQL函数的局部变量；任何对它的修改只影响当前的函数。

DO NOTHING

有时一个什么也不做的占位语句也很有用。例如，它能够指示 if/then/else 链中故意留出的空分支。可以使用NULL
语句达到这个目的：

1NULL;

例如，下面的两段代码是等价的：

 1BEGIN
 2    y := x / 0;
 3EXCEPTION
 4    WHEN division_by_zero THEN
 5        NULL;  -- 忽略错误
 6END;
 7
 8BEGIN
 9    y := x / 0;
10EXCEPTION
11    WHEN division_by_zero THEN  -- 忽略错误
12END;

究竟使用哪一种取决于各人的喜好。   注意：在 Oracle 的 PL/SQL 中，不允许出现空语句列表，并且因此在这种情况下必须使用NULL
语句。而PL/pgSQL允许你什么也不写。    

6.1.5 PL/pgSQL控制结构

从函数返回

从函数中返回数据：RETURN
和RETURN NEXT
。

Return

语法为:  Return Expression。带有一个表达式的RETURN
用于终止函数并把expression
的值返回给调用者。这种形式被用于不返回集合的PL/pgSQL函数。如果一个函数返回一个标量类型，表达式的结果将被自动转换成函数的返回类型。但是要返回一个复合（行）值，你必须写一个正好产生所需列集合的表达式。这可能需要使用显式造型。如果你声明函数返回void
，一个RETURN
语句可以被用来提前退出函数；但是不要在RETURN
后面写一个表达式。    e.g.

1-- 返回一个标量类型的函数
2RETURN 1 + 2;
3RETURN scalar_var;
4
5-- 返回一个组合类型的函数
6RETURN composite_type_var;
7RETURN (1, 2, 'three'::text);  -- 必须把列造型成正确的类型

Return NEXT和Return QUERY

1RETURN NEXT expression;
2RETURN QUERY query;
3RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ];

当一个PL/pgSQL函数被声明为返回SETOF *
sometype*
，那么遵循的过程则略有不同。在这种情况下，要返回的个体项被用一个RETURN NEXT
或者RETURN QUERY
命令的序列指定，并且接着会用一个不带参数的最终RETURN
命令来指示这个函数已经完成执行。RETURN NEXT
可以被用于标量和复合数据类型；对于复合类型，将返回一个完整的结果“表”。RETURN QUERY
将执行一个查询的结果追加到一个函数的结果集中。在一个单一的返回集合的函数中，RETURN NEXT
和RETURN QUERY
可以被随意地混合，这样它们的结果将被串接起来。

RETURN NEXT
和RETURN QUERY
实际上不会从函数中返回 — 它们简单地向函数的结果集中追加零或多行。然后会继续执行PL/pgSQL函数中的下一条语句。随着后继的RETURN NEXT
和RETURN QUERY
命令的执行，结果集就建立起来了。最后一个RETURN
（应该没有参数）会导致控制退出该函数（或者你可以让控制到达函数的结尾）。    RETURN QUERY
有一种变体RETURN QUERY EXECUTE
，它可以动态指定要被执行的查询。可以通过USING
向计算出的查询字符串插入参数表达式，这和在EXECUTE
命令中的方式相同。    如果你声明函数带有输出参数，只需要写不带表达式的RETURN NEXT
。在每一次执行时，输出参数变量的当前值将被保存下来用于最终返回为结果的一行。注意为了创建一个带有输出参数的集合返回函数，在有多个输出参数时，你必须声明函数为返回SETOF record
；或者如果只有一个类型为sometype
的输出参数时，声明函数为SETOF sometype
。

下面是一个使用RETURN NEXT
的函数例子：

 1CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
 2INSERT INTO foo VALUES (1, 2, 'three');
 3INSERT INTO foo VALUES (4, 5, 'six');
 4
 5CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS
 6$BODY$
 7DECLARE
 8    r foo%rowtype;
 9BEGIN
10    FOR r IN
11        SELECT * FROM foo WHERE fooid > 0
12    LOOP
13        -- 这里可以做一些处理
14        RETURN NEXT r; -- 返回 SELECT 的当前行
15    END LOOP;
16    RETURN;
17END;
18$BODY$
19LANGUAGE plpgsql;
20
21SELECT * FROM get_all_foo();

使用RETURN QUERY
的函数的例子：

 1CREATE FUNCTION get_available_flightid(date) RETURNS SETOF integer AS
 2$BODY$
 3BEGIN
 4    RETURN QUERY SELECT flightid
 5                   FROM flight
 6                  WHERE flightdate >= $1
 7                    AND flightdate < ($1 + 1);
 8
 9    -- 因为执行还未结束，我们可以检查是否有行被返回
10    -- 如果没有就抛出异常。
11    IF NOT FOUND THEN
12        RAISE EXCEPTION 'No flight at %.', $1;
13    END IF;
14    RETURN;
15 END;
16$BODY$
17LANGUAGE plpgsql;
18
19-- 返回可用的航班或者在没有可用航班时抛出异常。
20SELECT * FROM get_available_flightid(CURRENT_DATE);

注意： 目前RETURN NEXT
和RETURN QUERY
的实现在从函数返回之前会把整个结果集都保存起来。这意味着如果一个PL/pgSQL函数生成一个非常大的结果集，性能可能会很差：数据将被写到磁盘上以避免内存耗尽，但是函数本身在整个结果集都生成之前不会退出。将来的PL/pgSQL版本可能会允许用户定义没有这种限制的集合返回函数。目前，数据开始被写入到磁盘的时机由配置变量work_mem控制。拥有足够内存来存储大型结果集的管理员可以考虑增大这个参数。

从过程中返回

过程没有返回值。因此，过程的结束可以不用RETURN
语句。    如果想用一个RETURN
语句提前退出代码，只需写一个没有表达式的RETURN
。    如果过程有输出参数，那么输出参数最终的值会被返回给调用者。

调用存储过程

PL/pgSQL函数，存储过程或DO
块可以使用 CALL
调用存储过程。输出参数的处理方式与纯SQL中CALL
的工作方式不同。存储过程的每个INOUT
参数必须和CALL
语句中的变量对应， 并且无论存储过程返回什么，都会在返回后赋值给该变量。例如：

 1CREATE PROCEDURE triple(INOUT x int)
 2LANGUAGE plpgsql
 3AS $$
 4BEGIN
 5    x := x * 3;
 6END;
 7$$;
 8
 9DO $$
10DECLARE myvar int := 5;
11BEGIN
12  CALL triple(myvar);
13  RAISE NOTICE 'myvar = %', myvar;  -- prints 15
14END;
15$$;

条件

IF
和CASE
语句让你可以根据某种条件执行二选其一的命令。PL/pgSQL有三种形式的IF
：

• IF … THEN … END IF
  

• IF … THEN … ELSE … END IF
  

• IF … THEN … ELSIF … THEN … ELSE … END IF
  

以及两种形式的CASE
：

• CASE … WHEN … THEN … ELSE … END CASE
  

• CASE WHEN … THEN … ELSE … END CASE
  

IF-THEN

1IF boolean-expression THEN
2    statements
3END IF;

IF-THEN
语句是IF
的最简单形式。如果条件为真，在THEN
和END IF
之间的语句将被执行。否则，将忽略它们。      e.g.

1IF v_user_id <> 0 THEN
2    UPDATE users SET email = v_email WHERE user_id = v_user_id;
3END IF;

IF-THEN-ELSE

1IF boolean-expression THEN
2    statements
3ELSE
4    statements
5END IF;

IF-THEN-ELSE
语句对IF-THEN
进行了增加，它让你能够指定一组在条件不为真时应该被执行的语句（注意这也包括条件为 NULL 的情况）。   e.g.

 1IF parentid IS NULL OR parentid = ''
 2THEN
 3    RETURN fullname;
 4ELSE
 5    RETURN hp_true_filename(parentid) || '/' || fullname;
 6END IF;
 7
 8IF v_count > 0 THEN
 9    INSERT INTO users_count (count) VALUES (v_count);
10    RETURN 't';
11ELSE
12    RETURN 'f';
13END IF;

IF-THEN-ELSIF

 1IF boolean-expression THEN
 2    statements
 3[ ELSIF boolean-expression THEN
 4    statements
 5[ ELSIF boolean-expression THEN
 6    statements
 7    ...
 8]
 9]
10[ ELSE
11    statements ]
12END IF;

有时会有多于两种选择。IF-THEN-ELSIF
则提供了一个简便的方法来检查多个条件。IF
条件会被一个接一个测试，直到找到第一个为真的。然后执行相关语句，然后控制会被交给END IF
之后的下一个语句（后续的任何IF
条件不会被测试）。如果没有一个IF
条件为真，那么ELSE
块（如果有）将被执行。e.g.

 1IF number = 0 THEN
 2    result := 'zero';
 3ELSIF number > 0 THEN
 4    result := 'positive';
 5ELSIF number < 0 THEN
 6    result := 'negative';
 7ELSE
 8    -- 嗯，唯一的其他可能性是数字为空
 9    result := 'NULL';
10END IF;

关键词ELSIF
也可以被拼写成ELSEIF
。

另一个可以完成相同任务的方法是嵌套IF-THEN-ELSE
语句，如下例：

1IF demo_row.sex = 'm' THEN
2    pretty_sex := 'man';
3ELSE
4    IF demo_row.sex = 'f' THEN
5        pretty_sex := 'woman';
6    END IF;
7END IF;

不过，这种方法需要为每个IF
都写一个匹配的END IF
，因此当有很多选择时，这种方法比使用ELSIF
要麻烦得多。

简单CASE

1CASE search-expression
2    WHEN expression [, expression [ ... ]] THEN
3      statements
4  [ WHEN expression [, expression [ ... ]] THEN
5      statements
6    ... ]
7  [ ELSE
8      statements ]
9END CASE;

CASE
的简单形式提供了基于操作数等值判断的有条件执行。search-expression
会被计算（一次）并且一个接一个地与WHEN
子句中的每个expression
比较。如果找到一个匹配，那么相应的statements
会被执行，并且接着控制会被交给END CASE
之后的下一个语句（后续的WHEN
表达式不会被计算）。如果没有找到匹配，ELSE
语句
会被执行。但是如果ELSE
不存在，将会抛出一个CASE_NOT_FOUND
异常。

这里是一个简单的例子：

1CASE x
2    WHEN 1, 2 THEN
3        msg := 'one or two';
4    ELSE
5        msg := 'other value than one or two';
6END CASE;

搜索CASE

1CASE
2    WHEN boolean-expression THEN
3      statements
4  [ WHEN boolean-expression THEN
5      statements
6    ... ]
7  [ ELSE
8      statements ]
9END CASE;

CASE
的搜索形式基于布尔表达式真假的有条件执行。每一个WHEN
子句的boolean-expression
会被依次计算，直到找到一个得到真
的。然后相应的statements
会被执行，并且接下来控制会被传递给END CASE
之后的下一个语句（后续的WHEN
表达式不会被计算）。如果没有找到为真的结果，ELSE
statements
会被执行。但是如果ELSE
不存在，那么将会抛出一个CASE_NOT_FOUND
异常。

这里是一个例子：

1CASE
2    WHEN x BETWEEN 0 AND 10 THEN
3        msg := 'value is between zero and ten';
4    WHEN x BETWEEN 11 AND 20 THEN
5        msg := 'value is between eleven and twenty';
6END CASE;

简单循环

总体上使用LOOP
、EXIT
、CONTINUE
、WHILE
、FOR
和FOREACH
语句，你可以安排PL/pgSQL重复一系列命令。

LOOP

1[ <<label>> ]
2LOOP
3    statements
4END LOOP [ label ];

LOOP
定义一个无条件的循环，它会无限重复直到被EXIT
或RETURN
语句终止。可选的label
可以被EXIT
和CONTINUE
语句用在嵌套循环中指定这些语句引用的是哪一层循环。

EXIT

1EXIT [ label ] [ WHEN boolean-expression ];

如果没有给出label
，那么最内层的循环会被终止，然后跟在END LOOP
后面的语句会被执行。如果给出了label
，那么它必须是当前或者更高层的嵌套循环或者语句块的标签。然后该命名循环或块就会被终止，并且控制会转移到该循环/块相应的END
之后的语句上。

如果指定了WHEN
，只有boolean-expression
为真时才会发生循环退出。否则，控制会转移到EXIT
之后的语句。

EXIT
可以被用在所有类型的循环中，它并不限于在无条件循环中使用。

在和BEGIN
块一起使用时，EXIT
会把控制交给块结束后的下一个语句。需要注意的是，一个标签必须被用于这个目的；一个没有被标记的EXIT
永远无法被认为与一个BEGIN
块匹配（这种状况从PostgreSQL 8.4 之前的发布就已经开始改变。这可能允许一个未被标记的EXIT
匹配一个BEGIN
块）。

 1LOOP
 2    -- 一些计算
 3    IF count > 0 THEN
 4        EXIT;  -- 退出循环
 5    END IF;
 6END LOOP;
 7
 8LOOP
 9    -- 一些计算
10    EXIT WHEN count > 0;  -- 和前一个例子相同的结果
11END LOOP;
12
13<<ablock>>
14BEGIN
15    -- 一些计算
16    IF stocks > 100000 THEN
17        EXIT ablock;  -- 导致从 BEGIN 块中退出
18    END IF;
19    -- 当stocks > 100000时，这里的计算将被跳过
20END;

CONTINUE

1CONTINUE [ label ] [ WHEN boolean-expression ];

如果没有给出label
，最内层循环的下一次迭代会开始。也就是，循环体中剩余的所有语句将被跳过，并且控制会返回到循环控制表达式（如果有）来决定是否需要另一次循环迭代。如果label
存在，它指定应该继续执行的循环的标签。

如果指定了WHEN
，该循环的下一次迭代只有在boolean-expression
为真时才会开始。否则，控制会传递给CONTINUE
后面的语句。

CONTINUE
可以被用在所有类型的循环中，它并不限于在无条件循环中使用。

1LOOP
2    -- 一些计算
3    EXIT WHEN count > 100;
4    CONTINUE WHEN count < 50;
5    -- 一些用于 count IN [50 .. 100] 的计算
6END LOOP;

WHILE

1[ <<label>> ]
2WHILE boolean-expression LOOP
3    statements
4END LOOP [ label ];

只要boolean-expression
被计算为真，WHILE
语句就会重复一个语句序列。在每次进入到循环体之前都会检查该表达式。

1WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
2    -- 这里是一些计算
3END LOOP;
4
5WHILE NOT done LOOP
6    -- 这里是一些计算
7END LOOP;

FOR (整型变体)

1[ <<label>> ]
2FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP
3    statements
4END LOOP [ label ];

这种形式的FOR
会创建一个在一个整数范围上迭代的循环。变量name
会自动定义为类型integer
并且只在循环内存在（任何该变量名的现有定义在此循环内都将被忽略）。给出范围上下界的两个表达式在进入循环的时候计算一次。如果没有指定BY
子句，迭代步长为 1，否则步长是BY
中指定的值，该值也只在循环进入时计算一次。如果指定了REVERSE
，那么在每次迭代后步长值会被减除而不是增加。

整数FOR
循环的一些例子：

 1FOR i IN 1..10 LOOP
 2    -- 我在循环中将取值 1,2,3,4,5,6,7,8,9,10 
 3END LOOP;
 4
 5FOR i IN REVERSE 10..1 LOOP
 6    -- 我在循环中将取值 10,9,8,7,6,5,4,3,2,1 
 7END LOOP;
 8
 9FOR i IN REVERSE 10..1 BY 2 LOOP
10    -- 我在循环中将取值 10,8,6,4,2 
11END LOOP;

如果下界大于上界（或者在REVERSE
情况下是小于），循环体根本不会被执行。而且不会抛出任何错误。

如果一个label
被附加到FOR
循环，那么整数循环变量可以用一个使用那个label
的限定名引用。

通过查询结果循环

使用一种不同类型的FOR
循环，你可以通过一个查询的结果进行迭代并且操纵相应的数据。语法是：

1[ <<label>> ]
2FOR target IN query LOOP
3    statements
4END LOOP [ label ];

target
是一个记录变量、行变量或者逗号分隔的标量变量列表。target
被连续不断被赋予来自query
的每一行，并且循环体将为每一行执行一次。下面是一个例子：

 1CREATE FUNCTION refresh_mviews() RETURNS integer AS $$
 2DECLARE
 3    mviews RECORD;
 4BEGIN
 5    RAISE NOTICE 'Refreshing all materialized views...';
 6
 7    FOR mviews IN
 8       SELECT n.nspname AS mv_schema,
 9              c.relname AS mv_name,
10              pg_catalog.pg_get_userbyid(c.relowner) AS owner
11         FROM pg_catalog.pg_class c
12    LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
13        WHERE c.relkind = 'm'
14     ORDER BY 1
15    LOOP
16
17        -- Now "mviews" has one record with information about the materialized view
18
19        RAISE NOTICE 'Refreshing materialized view %.% (owner: %)...',
20                     quote_ident(mviews.mv_schema),
21                     quote_ident(mviews.mv_name),
22                     quote_ident(mviews.owner);
23        EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I', mviews.mv_schema, mviews.mv_name);
24    END LOOP;
25
26    RAISE NOTICE 'Done refreshing materialized views.';
27    RETURN 1;
28END;
29$$ LANGUAGE plpgsql;

如果循环被一个EXIT
语句终止，那么在循环之后你仍然可以访问最后被赋予的行值。

在这类FOR
语句中使用的query
可以是任何返回行给调用者的 SQL 命令：最常见的是SELECT
，但你也可以使用带有RETURNING
子句的INSERT
、UPDATE
或DELETE
。一些EXPLAIN
之类的功能性命令也可以用在这里。

FOR-IN-EXECUTE
语句是在行上迭代的另一种方式：

1[ <<label>> ]
2FOR target IN EXECUTE text_expression [ USING expression [, ... ] ] LOOP
3    statements
4END LOOP [ label ];

这个例子类似前面的形式，只不过源查询被指定为一个字符串表达式，在每次进入FOR
循环时都会计算它并且重新规划。这允许程序员在一个预先规划好了的命令的速度和一个动态命令的灵活性之间进行选择，就像一个纯EXECUTE
语句那样。在使用EXECUTE
时，可以通过USING
将参数值插入到动态命令中。

另一种指定要对其结果迭代的查询的方式是将它声明为一个游标。后边有相应介绍。

通过数组循环

FOREACH
循环很像一个FOR
循环，但不是通过一个 SQL 查询返回的行进行迭代，它通过一个数组值的元素来迭代（通常，FOREACH
意味着通过一个组合值表达式的部件迭代；用于通过除数组之外组合类型进行循环的变体可能会在未来被加入）。在一个数组上循环的FOREACH
语句是：

1[ <<label>> ]
2FOREACH target [ SLICE number ] IN ARRAY expression LOOP
3    statements
4END LOOP [ label ];

如果没有SLICE
，或者如果没有指定SLICE 0
，循环会通过计算expression
得到的数组的个体元素进行迭代。target
变量被逐一赋予每一个元素值，并且循环体会为每一个元素执行。这里是一个通过整数数组的元素循环的例子：

 1CREATE FUNCTION sum(int[]) RETURNS int8 AS $$
 2DECLARE
 3  s int8 := 0;
 4  x int;
 5BEGIN
 6  FOREACH x IN ARRAY $1
 7  LOOP
 8    s := s + x;
 9  END LOOP;
10  RETURN s;
11END;
12$$ LANGUAGE plpgsql;
13
14select sum(array[1,2,3]);

元素会被按照存储顺序访问，而不管数组的维度数。尽管target
通常只是一个单一变量，当通过一个组合值（记录）的数组循环时，它可以是一个变量列表。在那种情况下，对每一个数组元素，变量会被从组合值的连续列赋值。

通过一个正SLICE
值，FOREACH
通过数组的切片而不是单一元素迭代。SLICE
值必须是一个不大于数组维度数的整数常量。target
变量必须是一个数组，并且它接收数组值的连续切片，其中每一个切片都有SLICE
指定的维度数。这里是一个通过一维切片迭代的例子：

 1CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$
 2DECLARE
 3  x int[];
 4BEGIN
 5  FOREACH x SLICE 1 IN ARRAY $1
 6  LOOP
 7    RAISE NOTICE 'row = %', x;
 8  END LOOP;
 9END;
10$$ LANGUAGE plpgsql;
11
12SELECT scan_rows(ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]);
13
14NOTICE:  row = {1,2,3}
15NOTICE:  row = {4,5,6}
16NOTICE:  row = {7,8,9}
17NOTICE:  row = {10,11,12}

捕获错误

默认情况下，PL/pgSQL函数中发生的任何错误都会中止函数和周围事务的执行。你可以使用一个带有EXCEPTION
子句的BEGIN
块俘获错误并且从中恢复。其语法是BEGIN
块通常的语法的一个扩展：(https://www.postgresql.org/docs/14/errcodes-appendix.html,  http://postgres.cn/docs/14/errcodes-appendix.html)

 1[ <<label>> ]
 2[ DECLARE
 3    declarations ]
 4BEGIN
 5    statements
 6EXCEPTION
 7    WHEN condition [ OR condition ... ] THEN
 8        handler_statements
 9    [ WHEN condition [ OR condition ... ] THEN
10          handler_statements
11      ... ]
12END;

如果没有发生错误，这种形式的块只是简单地执行所有statements
， 并且接着控制转到END
之后的下一个语句。但是如果在statements
内发生了一个错误，则会放弃对statements
的进一步处理，然后控制会转到EXCEPTION
列表。系统会在列表中寻找匹配所发生错误的第一个condition
。如果找到一个匹配，则执行对应的handler_statements
，并且接着把控制转到END
之后的下一个语句。如果没有找到匹配，该错误就会传播出去，就好像根本没有EXCEPTION
一样：错误可以被一个带有EXCEPTION
的闭合块捕捉，如果没有EXCEPTION
则中止该函数的处理。

condition
的名字可以是附录 A中显示的任何名字。一个分类名匹配其中所有的错误。特殊的条件名OTHERS
匹配除了QUERY_CANCELED
和ASSERT_FAILURE
之外的所有错误类型（虽然通常并不明智，还是可以用名字捕获这两种错误类型）。条件名是大小写无关的。一个错误条件也可以通过SQLSTATE
代码指定，例如以下是等价的：

1WHEN division_by_zero THEN ...
2WHEN SQLSTATE '22012' THEN ...

如果在选中的handler_statements
内发生了新的错误，那么它不能被这个EXCEPTION
子句捕获，而是被传播出去。一个外层的EXCEPTION
子句可以捕获它。

当一个错误被EXCEPTION
捕获时，PL/pgSQL函数的局部变量会保持错误发生时的值，但是该块中所有对持久数据库状态的改变都会被回滚。例如，考虑这个片段：

 1INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
 2BEGIN
 3    UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
 4    x := x + 1;
 5    y := x / 0;
 6EXCEPTION
 7    WHEN division_by_zero THEN
 8        RAISE NOTICE 'caught division_by_zero';
 9        RETURN x;
10END;

当控制到达对y
赋值的地方时，它会带着一个division_by_zero
错误失败。这个错误将被EXCEPTION
子句捕获。而在RETURN
语句中返回的值将是x
增加过后的值。但是UPDATE
命令的效果将已经被回滚。不过，在该块之前的INSERT
将不会被回滚，因此最终的结果是数据库包含Tom Jones
但不包含Joe Jones
。

提示：进入和退出一个包含EXCEPTION
子句的块要比不包含EXCEPTION
的块开销大的多。因此，只在必要的时候使用EXCEPTION
。    

e.g.  UPDATE/INSERT的异常

这个例子使用异常处理来酌情执行UPDATE
或    INSERT
。我们推荐应用使用带有    ON CONFLICT DO UPDATE
的INSERT
   而不是真正使用这种模式。下面的例子主要是为了展示    PL/pgSQL如何控制流程：

 1CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
 2
 3CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
 4$$
 5BEGIN
 6    LOOP
 7        -- 首先尝试更新键
 8        UPDATE db SET b = data WHERE a = key;
 9        IF found THEN
10            RETURN;
11        END IF;
12        -- 不在这里，那么尝试插入该键
13        -- 如果其他某人并发地插入同一个键，
14        -- 我们可能得到一个唯一键失败
15        BEGIN
16            INSERT INTO db(a,b) VALUES (key, data);
17            RETURN;
18        EXCEPTION WHEN unique_violation THEN
19            -- 什么也不做，并且循环再次尝试 UPDATE
20        END;
21    END LOOP;
22END;
23$$
24LANGUAGE plpgsql;
25
26SELECT merge_db(1, 'david');
27SELECT merge_db(1, 'dennis');

这段代码假定unique_violation
错误是INSERT
造成，并且不是由该表上一个触发器函数中的INSERT
导致。如果在该表上有多于一个唯一索引，也可能会发生不正确的行为，因为不管哪个索引导致该错误它都将重试该操作。通过接下来要讨论的特性来检查被捕获的错误是否为所预期的会更安全。

得到有关某个错误的信息

异常处理器经常被用来标识发生的特定错误。有两种方法来得到PL/pgSQL中当前异常的信息：特殊变量和GET STACKED DIAGNOSTICS
命令。    在一个异常处理器内，特殊变量SQLSTATE
包含了对应于被抛出异常的错误代码（可能的错误代码列表见表 A.1）。特殊变量SQLERRM
包含与该异常相关的错误消息。这些变量在异常处理器外是未定义的。

在一个异常处理器内，我们也可以用GET STACKED DIAGNOSTICS
命令检索有关当前异常的信息，该命令的形式为：

1GET STACKED DIAGNOSTICS variable { = | := } item [ , ... ];

每个item
是一个关键词，它标识一个被赋予给指定变量
（应该具有接收该值的正确数据类型）的状态值。表 43.2中显示了当前可用的状态项。

错误诊断项

如果异常没有为一个项设置值，将返回一个空字符串。

 1DECLARE
 2  text_var1 text;
 3  text_var2 text;
 4  text_var3 text;
 5BEGIN
 6  -- 某些可能导致异常的处理
 7  ...
 8EXCEPTION WHEN OTHERS THEN
 9  GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
10                          text_var2 = PG_EXCEPTION_DETAIL,
11                          text_var3 = PG_EXCEPTION_HINT;
12END;

获得执行位置信息

GET DIAGNOSTICS
命令检索有关当前执行状态的信息（反之上文讨论的GET STACKED DIAGNOSTICS
命令会把有关执行状态的信息报告成一个以前的错误）。它的PG_CONTEXT
状态项可用于标识当前执行位置。状态项PG_CONTEXT
将返回一个文本字符串，其中有描述该调用栈的多行文本。第一行会指向当前函数以及当前正在执行GET DIAGNOSTICS
的命令。第二行及其后的行表示调用栈中更上层的调用函数。例如：

 1CREATE OR REPLACE FUNCTION outer_func() RETURNS integer AS $$
 2BEGIN
 3  RETURN inner_func();
 4END;
 5$$ LANGUAGE plpgsql;
 6
 7CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$
 8DECLARE
 9  stack text;
10BEGIN
11  GET DIAGNOSTICS stack = PG_CONTEXT;
12  RAISE NOTICE E'--- Call Stack ---\n%', stack;
13  RETURN 1;
14END;
15$$ LANGUAGE plpgsql;
16
17SELECT outer_func();
18
19NOTICE:  --- Call Stack ---
20PL/pgSQL function inner_func() line 5 at GET DIAGNOSTICS
21PL/pgSQL function outer_func() line 3 at RETURN
22CONTEXT:  PL/pgSQL function outer_func() line 3 at RETURN
23 outer_func
24 ------------
25           1
26(1 row)

GET STACKED DIAGNOSTICS ... PG_EXCEPTION_CONTEXT
返回同类的栈跟踪，但是它描述检测到错误的位置而不是当前位置。

错误码及错误信息和异常信息

(https://www.postgresql.org/docs/14/errcodes-appendix.html,  http://postgres.cn/docs/14/errcodes-appendix.html)

PostgreSQL服务器发出的所有消息都被赋予了五个字符错误代码，这遵循 SQL 标准对“SQLSTATE”代码的习惯。需要知道发生了什么错误条件的应用通常应该测试错误代码， 而不是查看文本形式的错误消息。

根据标准，错误代码的前两个字符表示错误类别，而后三个字符表示在该类别内的一种特定情况。因此， 那些不能识别特定错误代码的应用仍然可以从错误类别中推断要做什么。

PostgreSQL错误代码

6.2 游标

和一次执行整个查询不同，可以建立一个游标来封装该查询，并且接着一次读取该查询结果的一些行。这样做的原因之一是在结果中包含大量行时避免内存不足（不过，PL/pgSQL用户通常不需要担心这些，因为FOR
循环在内部会自动使用一个游标来避免内存问题）。一种更有趣的用法是返回一个函数已经创建的游标的引用，允许调用者读取行。这提供了一种有效的方法从函数中返回大型行集。

声明游标变量

所有在PL/pgSQL中对游标的访问都会通过游标变量，它总是特殊的数据类型refcursor
。创建游标变量的一种方法是把它声明为一个类型为refcursor
的变量。另外一种方法是使用游标声明语法，通常是：

1name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;

（为了对Oracle的兼容性，可以用IS
替代FOR
）。如果指定了SCROLL
，那么游标可以反向滚动；如果指定了NO SCROLL
，那么反向取的动作会被拒绝；如果二者都没有被指定，那么能否进行反向取就取决于查询。如果指定了arguments
， 那么它是一个逗号分隔的*
name* *
datatype*
对的列表， 它们定义在给定查询中要被参数值替换的名称。实际用于替换这些名字的值将在游标被打开之后指定。

1DECLARE
2    curs1 refcursor;
3    curs2 CURSOR FOR SELECT * FROM tenk1;
4    curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;

所有这三个变量都是refcursor
类型，但是第一个可以用于任何查询，而第二个已经被绑定了一个完全指定的查询，并且最后一个被绑定了一个参数化查询。（游标被打开时，key
将被一个整数参数值替换）。变量curs1
被称为未绑定，因为它没有被绑定到任何特定查询。

打开游标

在一个游标可以被用来检索行之前，它必需先被打开（这是和 SQL 命令DECLARE CURSOR
等效的操作）。PL/pgSQL有三种形式的OPEN
命令，其中两种用于未绑定游标变量，另外一种用于已绑定的游标变量。

Open For Query

1OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR query;

该游标变量被打开并且被给定要执行的查询。游标不能是已经打开的，并且它必需已经被声明为一个未绑定的游标变量（也就是声明为一个简单的refcursor
变量）。该查询必须是一条SELECT
或者其它返回行的东西（例如EXPLAIN
）。该查询会按照其它PL/pgSQL中的 SQL 命令同等的方式对待：先代换PL/pgSQL变量名，并且执行计划会被缓存用于可能的重用。当一个PL/pgSQL变量被替换到游标查询中时，替换的值是在OPEN
时它所具有的值。对该变量后续的改变不会影响游标的行为。对于一个已经绑定的游标，SCROLL
和NO SCROLL
选项具有相同的含义。

1OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;

Open For Execute

1OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR EXECUTE query_string
2                                     [ USING expression [, ... ] ];

打开游标变量并且执行指定的查询。该游标不能是已打开的，并且必须已经被声明为一个未绑定的游标变量（也就是声明为一个简单的refcursor
变量）。该查询以和EXECUTE
中相同的方式被指定为一个字符串表达式。照例，这提供了灵活性，因此查询计划可以在两次运行之间变化（见第 43.11.2 节），并且它也意味着在该命令字符串上还没有完成变量替换。正如EXECUTE
，可以通过format()
和USING
将参数值插入到动态命令中。SCROLL
和NO SCROLL
选项具有和已绑定游标相同的含义。

一个例子：

1OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;

在这个例子中，表名被通过format()
插入到查询中。       col1
的比较值被通过一个USING
参数插入，        所以它不需要引用。

打开一个已绑定的游标

1OPEN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ];

这种形式的OPEN
被用于打开一个游标变量，它的查询是在声明时绑定的。该游标不能是已经打开的。当且仅当该游标被声明为接收参数时，才必需出现一个实际参数值表达式的列表。这些值将被替换到命令中。

一个已绑定游标的查询计划总是被认为是可缓存的，在这种情况中没有EXECUTE
的等效形式。注意SCROLL
和NO SCROLL
不能在OPEN
中指定，因为游标的滚动行为已经被确定。

使用位置或命名记号可以传递参数值。在位置记号中，所有参数都必须按照顺序指定。在命名记号中，每一个参数的名字被使用:=
指定以将它和参数表达式分隔开。类似于第 4.3 节中描述的调用函数，也允许混合位置和命名记号。

例子（这些例子使用上面例子中的游标声明）：

1OPEN curs2;
2OPEN curs3(42);
3OPEN curs3(key := 42);

因为在一个已绑定游标的查询上已经完成了变量替换，实际有两种方式将值传到游标中：给OPEN
一个显式参数，或者在查询中隐式引用一个PL/pgSQL变量。不过，只有在已绑定游标之前声明的变量才将会被替换到游标中。在两种情况下，要被传递的值都是在OPEN
时确定的。例如，得到上例中curs3
相同效果的另一种方式是

1DECLARE
2    key integer;
3    curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
4BEGIN
5    key := 42;
6    OPEN curs4;

使用游标

一旦一个游标已经被打开，那么就可以用这里描述的语句操作它。

这些操作不需要发生在打开该游标开始操作的同一个函数中。你可以从一个函数返回一个refcursor
值，并且让调用者在该游标上操作（在内部，refcursor
值只是一个包含该游标活动查询的所谓入口的字符串名称。这个名字可以被传递、赋予给其它refcursor
变量等等，而不用担心扰乱入口）。

所有入口会在事务的结尾被隐式地关闭。因此一个refcursor
值只能在该事务结束前用于引用一个打开的游标。

Fetch Cursor

1FETCH [ direction { FROM | IN } ] cursor INTO target;

就像SELECT INTO
一样，FETCH
从游标中检索下一行到目标中，目标可以是一个行变量、记录变量或者逗号分隔的简单变量列表。如果没有下一行，目标会被设置为 NULL。与SELECT INTO
一样，可以检查特殊变量FOUND
来看一行是否被获得。

direction
子句可以是 SQL FETCH命令中允许的任何变体，除了那些能够取得多于一行的。即它可以是     NEXT
、     PRIOR
、     FIRST
、     LAST
、     ABSOLUTE
count
、     RELATIVE
count
、     FORWARD
或者     BACKWARD
。    省略direction
和指定NEXT
是一样的。在使用count
的形式中，count
可以是任意的整数值表达式（与SQL命令FETCH
不一样，FETCH
仅允许整数常量）。除非游标被使用SCROLL
选项声明或打开，否则要求反向移动的direction
值很可能会失败。

cursor
必须是一个引用已打开游标入口的refcursor
变量名。

1FETCH curs1 INTO rowvar;
2FETCH curs2 INTO foo, bar, baz;
3FETCH LAST FROM curs3 INTO x, y;
4FETCH RELATIVE -2 FROM curs4 INTO x;

Move Cursor

1MOVE [ direction { FROM | IN } ] cursor;

MOVE
重新定位一个游标而不检索任何数据。MOVE
的工作方式与FETCH
命令很相似，但是MOVE
只是重新定位游标并且不返回至移动到的行。与SELECT INTO
一样，可以检查特殊变量FOUND
来看要移动到的行是否存在。   e.g.

1MOVE curs1;
2MOVE LAST FROM curs3;
3MOVE RELATIVE -2 FROM curs4;
4MOVE FORWARD 2 FROM curs4;

UPDATE/DELETE WHERE CURRENT OF

1UPDATE table SET ... WHERE CURRENT OF cursor;
2DELETE FROM table WHERE CURRENT OF cursor;

当一个游标被定位到一个表行上时，使用该游标标识该行就可以对它进行更新或删除。对于游标的查询可以是什么是有限制的（尤其是不能有分组），并且最好在游标中使用FOR UPDATE
。详见DECLARE参考页。      e.g.

1UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;

Close Cursor

1CLOSE cursor;

CLOSE
关闭一个已打开游标的底层入口。这样就可以在事务结束之前释放资源，或者释放掉该游标变量以便再次打开。

Return Cursor

PL/pgSQL函数可以向调用者返回游标。这对于返回多行或多列（特别是巨大的结果集）很有用。要想这么做，该函数打开游标并且把该游标的名字返回给调用者（或者简单的使用调用者指定的或已知的入口名打开游标）。调用者接着可以从游标中取得行。游标可以由调用者关闭，或者是在事务关闭时自行关闭。

用于一个游标的入口名可以由编程者指定或者自动生成。要指定一个入口名，只需要在打开refcursor
变量之前简单地为它赋予一个字符串。OPEN
将把refcursor
变量的字符串值用作底层入口的名字。不过，如果refcursor
变量为空，OPEN
会自动生成一个与任何现有入口不冲突的名称，并且将它赋予给refcursor
变量。

注意：一个已绑定的游标变量被初始化为表示其名称的字符串值，因此入口的名字和游标变量名相同，除非在打开游标之前通过赋值覆盖了这个名字。但是一个未绑定的游标变量最初默认为空值，因此它会收到一个自动生成的唯一名字，除非被覆盖。

• 调用者提供游标名字:

 1CREATE TABLE test (col text);
 2INSERT INTO test VALUES ('123');
 3
 4CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
 5BEGIN
 6    OPEN $1 FOR SELECT col FROM test;
 7    RETURN $1;
 8END;
 9' LANGUAGE plpgsql;
10
11BEGIN;
12SELECT reffunc('funccursor');
13FETCH ALL IN funccursor;
14COMMIT;

• 自动游标名生成:

 1CREATE FUNCTION reffunc2() RETURNS refcursor AS '
 2DECLARE
 3    ref refcursor;
 4BEGIN
 5    OPEN ref FOR SELECT col FROM test;
 6    RETURN ref;
 7END;
 8' LANGUAGE plpgsql;
 9
10-- 需要在一个事务中使用游标。
11BEGIN;
12SELECT reffunc2();
13
14      reffunc2
15--------------------
16 <unnamed cursor 1>
17(1 row)
18
19FETCH ALL IN "<unnamed cursor 1>";
20COMMIT;

• 从一个函数中返回多个游标:

 1CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
 2BEGIN
 3    OPEN $1 FOR SELECT * FROM table_1;
 4    RETURN NEXT $1;
 5    OPEN $2 FOR SELECT * FROM table_2;
 6    RETURN NEXT $2;
 7END;
 8$$ LANGUAGE plpgsql;
 9
10-- 需要在一个事务中使用游标。
11BEGIN;
12
13SELECT * FROM myfunc('a', 'b');
14
15FETCH ALL FROM a;
16FETCH ALL FROM b;
17COMMIT;

通过一个游标的结果循环

有一种FOR
语句的变体，它允许通过游标返回的行进行迭代。语法是：

1[ <<label>> ]
2FOR recordvar IN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ] LOOP
3    statements
4END LOOP [ label ];

该游标变量必须在声明时已经被绑定到某个查询，并且它不能已经被打开。FOR
语句会自动打开游标，并且在退出循环时自动关闭游标。当且仅当游标被声明要使用参数时，才必须出现一个实际参数值表达式的列表。这些值会被替换到查询中，采用OPEN
期间的方式。