©
本文檔使用 php中文網手册 發布
39.6. 控制结构
控制结构可能是PL/pgSQL中最有用的(以及最重要)的部分了。 利用PL/pgSQL的控制结构，你可以以非常灵活而且强大的方法操纵PostgreSQL的数据。
39.6.1. 从函数返回
有两个命令可以用来从函数中返回数据：RETURN和RETURN NEXT。
39.6.1.1. RETURN
RETURN expression;
带表达式的RETURN用于终止函数并把expression的值返回给调用者。 这种形式用于不返回集合的PL/pgSQL函数。
如果返回标量类型，那么可以使用任何表达式。 表达式的类型将被自动转换成函数的返回类型，就像在赋值中描述的那样。 要返回一个复合(行)数值，你必须写一个记录或者行变量的expression。
如果你声明函数带输出参数，那么就只需要写无表达式的RETURN。 那么输出参数变量的当前值将被返回。
如果你声明函数返回void，那么一个RETURN语句可以用于提前退出函数； 但是不要在RETURN后面写一个表达式。
一个函数的返回值不能是未定义。 如果控制到达了函数最顶层的块而没有碰到一个RETURN语句，那么它就会发生一个错误。 不过，这个限制不适用于带输出参数的函数以及那些返回void的函数。 在这些例子里，如果顶层的块结束，则自动执行一个RETURN语句。
39.6.1.2. RETURN NEXT和RETURN QUERY
RETURN NEXT expression;
RETURN QUERY query;
RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ];
如果一个PL/pgSQL函数声明为返回SETOFsometype，那么遵循的过程则略有不同。 在这种情况下，要返回的独立项是在 RETURN NEXT或RETURN QUERY命令里声明的， 然后最后有一个不带参数的RETURN命令用于告诉这个函数已经完成执行了。 RETURN NEXT可以用于标量和复合数据类型；对于复合类型，将返回一个完整的结果"table"。 RETURN QUERY命令将一条查询的结果追加到一个函数的结果集中。 RETURN NEXT和RETURN QUERY RETURN QUERY appends the results of executing a query to the function's result set. RETURN NEXT and RETURN QUERY can be freely intermixed in a single set-returning function, in which case their results will be concatenated.
RETURN NEXT和RETURN QUERY实际上不会从函数中返回，它们是将0或者多个行追加到函数的结果集中。 然后执行继续执行PL/pgSQL函数里的下一条语句。 随着后继的RETURN NEXT和RETURN QUERY命令的执行，结果集就建立起来了。 最后一个RETURN应该没有参数， 它导致控制退出该函数(或者你可以简单地让控制到达函数的结尾)。
RETURN QUERY has a variant RETURN QUERY EXECUTE, which specifies the query to be executed dynamically. Parameter expressions can be inserted into the computed query string via USING, in just the same way as in the EXECUTE command.
If you declared the function with output parameters, write just RETURN NEXT with no expression. On each execution, the current values of the output parameter variable(s) will be saved for eventual return as a row of the result. Note that you must declare the function as returning SETOF record when there are multiple output parameters, or SETOF sometype when there is just one output parameter of type sometype, in order to create a set-returning. function with output parameters. 如果你声明函数带有输出参数，那么就只需要写不带表达式的 RETURN NEXT 。 输出参数的当前值将被保存，用于最终返回。 请注意如果有多个输出参数， 比如声明函数为返回 SETOF record 或者是在只有一个类型为 sometype 的输出参数时声明为 SETOF sometype ， 这样才能创建一个带有输出参数的返回集合的函数。
下面是一个使用RETURN NEXT的函数例子:
CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');
CREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF foo AS
$body$
DECLARE
r foo%rowtype;
BEGIN
FOR r IN SELECT * FROM foo
WHERE fooid > 0
LOOP
-- can do some processing here
RETURN NEXT r; -- return current row of SELECT
END LOOP;
RETURN;
END
$body$
LANGUAGE 'plpgsql' ;
SELECT * FROM getallfoo();
Note: 目前RETURN NEXT和RETURN QUERY实现在从函数返回之前把整个结果集都保存起来，就像上面描述的那样。 这意味着如果一个PL/pgSQL函数生成一个非常大的结果集，性能可能会很差： 数据将被写到磁盘上以避免内存耗尽，但是函数在完成整个结果集的生成之前不会退出。 将来的PL/pgSQL版本可能会允许用户定义没有这样限制的返回集合的函数。 目前，数据开始向磁盘里写的时刻是由配置变量work_mem控制的。 拥有足够内存的管理员如果想在内存里存储更大的结果集，则可以考虑把这个参数增大一些。
39.6.2. 条件
IF和CASE语句让你可以根据某种条件执行命令。PL/pgSQL有三种形式的IF：
IF ... THEN
IF ... THEN ... ELSE
IF ... THEN ... ELSIF ... THEN ... ELSE
以及两种形式的CASE:
CASE ... WHEN ... THEN ... ELSE ... END CASE
CASE WHEN ... THEN ... ELSE ... END CASE
39.6.2.1. IF-THEN
IF boolean-expression THEN
statements
END IF;
IF-THEN语句是IF的最简单形式。 如果条件为真，在THEN和END IF之间的语句将被执行。 否则，将忽略它们。
例子：
IF v_user_id <> 0 THEN
UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;
39.6.2.2. IF-THEN-ELSE
IF boolean-expression THEN
statements
ELSE
statements
END IF;
IF-THEN语句增加了add to，你可以声明在条件为假的时候执行的语句（包括条件为NULL的情况）。
例子：
IF parentid IS NULL OR parentid = ''
THEN
RETURN fullname;
ELSE
RETURN hp_true_filename(parentid) || '/' || fullname;
END IF;
IF v_count > 0 THEN
INSERT INTO users_count (count) VALUES (v_count);
RETURN 't';
ELSE
RETURN 'f';
END IF;
39.6.2.3. IF-THEN-ELSIF
IF boolean-expression THEN
statements
[ ELSIF boolean-expression THEN
statements
[ ELSIF boolean-expression THEN
statements
...]]
[ ELSE
statements ]
END IF;
有时不知两个选择。IF-THEN-ELSIF反过来提供了一个简便的方法来检查选择条件。 IF判断会陆续检查，直到找到第一个为真的，然后执行相关声明，如此，直到END IF（不会检测IF子查询）。 如果没有一个条件符合IF判断，那么会接着执行ELSE判断。
例子：
IF number = 0 THEN
result := 'zero';
ELSIF number > 0 THEN
result := 'positive';
ELSIF number < 0 THEN
result := 'negative';
ELSE
-- hmm, the only other possibility is that number is null
result := 'NULL';
END IF;
ELSIF关键字也可以写成ELSEIF.
另一个可以实现该目的的方法是使用IF-THEN-ELSE声明，如下：
IF demo_row.sex = 'm' THEN
pretty_sex := 'man';
ELSE
IF demo_row.sex = 'f' THEN
pretty_sex := 'woman';
END IF;
END IF;
然而，这个方法需要为每个IF写END IF，因此当有很多选择时，这种方法明显比ELSIF繁琐。
39.6.2.4. Simple CASE
CASE search-expression
WHEN expression [, expression [ ... ]] THEN
statements
[ WHEN expression [, expression [ ... ]] THEN
statements
... ]
[ ELSE
statements ]
END CASE;
The simple form of CASE provides conditional execution based on equality of operands. The search-expression is evaluated (once) and successively compared to each expression in the WHEN clauses. If a match is found, then the corresponding statements are executed, and then control passes to the next statement after END CASE. (Subsequent WHEN expressions are not evaluated.) If no match is found, the ELSE statements are executed; but if ELSE is not present, then a CASE_NOT_FOUND exception is raised.
例如：
CASE x
WHEN 1, 2 THEN
msg := 'one or two';
ELSE
msg := 'other value than one or two';
END CASE;
39.6.2.5. Searched CASE
CASE
WHEN boolean-expression THEN
statements
[ WHEN boolean-expression THEN
statements
... ]
[ ELSE
statements ]
END CASE;
The searched form of CASE provides conditional execution based on truth of Boolean expressions. Each WHEN clause's boolean-expression is evaluated in turn, until one is found that yields true. Then the corresponding statements are executed, and then control passes to the next statement after END CASE. (Subsequent WHEN expressions are not evaluated.) If no true result is found, the ELSE statements are executed; but if ELSE is not present, then a CASE_NOT_FOUND exception is raised.
Here is an example:
CASE
WHEN x BETWEEN 0 AND 10 THEN
msg := 'value is between zero and ten';
WHEN x BETWEEN 11 AND 20 THEN
msg := 'value is between eleven and twenty';
END CASE;
This form of CASE is entirely equivalent to IF-THEN-ELSIF, except for the rule that reaching an omitted ELSE clause results in an error rather than doing nothing.
39.6.3. 简单循环
使用LOOP, EXIT,CONTINUE, WHILE,HE FOR语句， 可以控制PL/pgSQL函数重复一系列命令。
39.6.3.1. 循环
[ <<label>> ] LOOP statements END LOOP [ label ];
LOOP定义一个无条件的循环，无限循环，直到由EXIT或RETURN语句终止。 可选的label可以由EXIT和CONTINUE语句使用，用于在嵌套循环中声明应该应用于哪一层循环。
39.6.3.2. 退出
EXIT [ label ] [ WHEN boolean-expression ];
如果没有给出label，那么退出最内层的循环，然后执行跟在END LOOP后面的语句。 如果给出label，那么它必须是当前或者更高层的嵌套循环块或者语句块的标签。 然后该命名块或者循环就会终止，而控制落到对应循环/块的END语句后面的语句上。
如果声明了WHEN，循环退出只有在boolean-expression为真的时候才发生， 否则控制会落到EXIT后面的语句上。
EXIT可以用于在所有的循环类型中，它并不仅仅限制于在无条件循环中使用。
在和BEGIN块一起使用的时候，EXIT把控制交给块结束后的下一个语句。 需要注意的是，一个标签必须用于这个目的；一个没有标记的EXIT永远无法与BEGIN进行匹配。
例如：
LOOP
-- some computations
IF count > 0 THEN
EXIT; -- exit loop
END IF;
END LOOP;
LOOP
-- some computations
EXIT WHEN count > 0; -- same result as previous example
END LOOP;
<<ablock>>
BEGIN
-- some computations
IF stocks > 100000 THEN
EXIT ablock; -- causes exit from the BEGIN block
END IF;
-- computations here will be skipped when stocks > 100000
END;
39.6.3.3. CONTINUE
CONTINUE [ label ] [ WHEN boolean-expression ];
如果没有给出label，那么就开始最内层循环的下一次执行。 也就是说，控制传递回给循环控制表达式(如果有)，然后重新计算循环体。 如果出现了label，它声明即将继续执行的循环的标签。
如果声明了WHEN，那么循环的下一次执行只有在boolean-expression为真的情况下才进行。 否则，控制传递给CONTINUE后面的语句。
CONTINUE可以用于所有类型的循环；它并不仅仅限于无条件循环。
例如：
LOOP
-- 一些计算
EXIT WHEN count > 100;
CONTINUE WHEN count < 50;
-- 一些count数值在[50 .. 100]之间的计算
END LOOP;
39.6.3.4. WHILE
[ <<label>> ] WHILE boolean-expression LOOP statements END LOOP [ label ];
只要条件表达式（boolean-expression）为真，WHILE语句就会不停的在一系列语句上进行循环， 条件是在每次进入循环体的时候检查的。
例如：
WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
-- some computations here
END LOOP;
WHILE NOT done LOOP
-- some computations here
END LOOP;
39.6.3.5. FOR (integer variant)
[ <<label>> ] FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP statements END LOOP [ label ];
这种形式的FOR对一定范围的整数进行迭代的循环。 变量name会自动定义为integer类型并且只在循环里存在(任何该变量名的现存定义在此循环内都将被忽略)。 给出范围上下界的两个表达式在进入循环的时候计算一次。 BY子句指定迭代步长(缺省为 1)，但如果声明了REVERSE步长将变为相应的负值。
一些整数 FOR 循环的例子：
FOR i IN 1..10 LOOP
-- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
END LOOP;
FOR i IN REVERSE 10..1 LOOP
-- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop
END LOOP;
FOR i IN REVERSE 10..1 BY 2 LOOP
-- i will take on the values 10,8,6,4,2 within the loop
END LOOP;
如果下界大于上界(或者是在REVERSE情况下是小于)，那么循环体将完全不被执行。 而且不会抛出任何错误。
If a label is attached to the FOR loop then the integer loop variable can be referenced with a qualified name, using that label.
39.6.4. 遍历命令结果
使用不同类型的FOR循环，你可以遍历一个命令的结果并且对其进行相应的操作。 语法是：
[ <<label>> ] FOR target IN query LOOP statements END LOOP [ label ];
target是一个记录变量、行变量、逗号分隔的标量变量列表 target被连续不断被赋予所有来自query的行，并且循环体将为每行执行一次。 下面是一个例子：
CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$
DECLARE
mviews RECORD;
BEGIN
PERFORM cs_log('Refreshing materialized views...');
FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
-- 现在"mviews"里有了一条来自 cs_materialized_views 的记录
PERFORM cs_log('Refreshing materialized view '
|| quote_ident(mviews.mv_name) || ' ...');
EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);
EXECUTE 'INSERT INTO '
|| quote_ident(mviews.mv_name) || ' '
|| mviews.mv_query;
END LOOP;
PERFORM cs_log('Done refreshing materialized views.');
RETURN 1;
END;
$$ LANGUAGE plpgsql;
如果循环是用一个EXIT语句终止的，那么在循环之后你仍然可以访问最后赋值的行
FOR语句中使用的这种query可以是任何返回行的SQL命令， 通常是SELECT，不过带有RETURNING子句的INSERT, UPDATE,或DELETE也是可以的， 一些诸如EXPLAIN之类的命令也可以
PL/pgSQL variables are substituted into the query text, and the query plan is cached for possible re-use, as discussed in detail in Section 39.10.1 and Section 39.10.2. PL/pgSQL变量 正如在Section 39.10.1和Section 39.10.2中讨论的那样。
FOR-IN-EXECUTE语句是遍历所有行的另外一种方法：
[ <<label>> ] FOR target IN EXECUTE text_expression [ USING expression [, ... ] ] LOOP statements END LOOP [ label ];
这个例子类似前面的形式，只不过源查询语句声明为了一个字符串表达式， 这样它在每次进入FOR循环的时候都会重新计算和生成执行计划。 这样就允许程序员在一个预先规划好了的命令所获得的速度和一个动态命令所获得的灵活性 (就像一个简单的EXECUTE语句那样)之间进行选择。 当使用EXECUTE时，可以通过USING将参数值插入到动态命令中。
对于一个需要将结果迭代的查询，另外一个声明的方法是将它定义为游标（cursor），可参阅Section 39.7.4。
39.6.5. Trapping Errors捕获错误
缺省时，一个在PL/pgSQL函数里发生的错误退出函数的执行， 并且实际上其周围的事务也会退出。 你可以使用一个带有EXCEPTION子句的BEGIN块捕获错误并且从中恢复。 其语法是正常的BEGIN块语法的一个扩展：
[ <<label>> ] [ DECLARE declarations ] BEGIN statements EXCEPTION WHEN condition [ OR condition ... ] THEN handler_statements [ WHEN condition [ OR condition ... ] THEN handler_statements ... ] END;
如果没有发生错误，这种形式的块只是简单地执行所有statements， 然后转到下一个END之后的语句。 但是如果在statements内部发生了一个错误，则对statements的进一步处理将废弃， 然后转到 EXCEPTION 列表。 系统搜索这个列表，寻找匹配错误的第一个condition。 如果找到匹配，则执行对应的handler_statements，然后转到END之后的下一个语句。 如果没有找到匹配，该错误就会广播出去， 就好像根本没有EXCEPTION子句一样：该错误可以被一个包围块用EXCEPTION捕获， 如果没有包围块，则退出函数的处理。
condition的名字可以是 附录A里显示的任何名字。 一个范畴名匹配任意该范畴里的错误。 特殊的条件名OTHERS匹配除了QUERY_CANCELED之外的所有错误类型。 可以用名字捕获QUERY_CANCELED，不过通常是不明智的。条件名是大小写无关的。 同时也可以通过SQLSTATE来声明一个错误条件，例如：
WHEN division_by_zero THEN ... WHEN SQLSTATE '22012' THEN ...
如果在选中的handler_statements里发生了新错误， 那么它不能被这个EXCEPTION子句捕获，而是传播出去。 一个外层的EXCEPTION子句可以捕获它。
如果一个错误被EXCEPTION捕获，PL/pgSQL函数的局部变量保持错误发生时的原值， 但是所有该块中想固化在数据库中的状态都回滚。 作为一个例子，让我们看看下面片断
INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
BEGIN
UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
x := x + 1;
y := x / 0;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'caught division_by_zero';
RETURN x;
END;
当控制到达给y赋值的地方时，它会带着一个division_by_zero错误失败。 这个错误将被EXCEPTION子句捕获。 而在RETURN语句里返回的数值将是x的增量值。 但是UPDATE已经被回滚。然而，在该块之前的INSERT将不会回滚， 因此最终的结果是数据库包含Tom Jones而不是Joe Jones。
Tip: 进入和退出一个包含EXCEPTION子句的块要比不包含的块开销大的多。 因此，不必要的时候不要使用EXCEPTION。
在异常处理器中，SQLSTATE变量包含抛出错误对应的错误代码(参考Table A-1获取可能的错误码的列表)。 SQLSTATE变量包含与异常关联的错误信息。 这些变量在异常处理器外面是未定义的。
Example 39-2. UPDATE/INSERT异常
这个例子根据使用异常处理器执行恰当的UPDATE或INSERT。
CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
LOOP
-- first try to update the key
UPDATE db SET b = data WHERE a = key;
IF found THEN
RETURN;
END IF;
-- not there, so try to insert the key
-- if someone else inserts the same key concurrently,
-- we could get a unique-key failure
BEGIN
INSERT INTO db(a,b) VALUES (key, data);
RETURN;
EXCEPTION WHEN unique_violation THEN
-- do nothing, and loop to try the UPDATE again
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;
SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');