PostgreSQL 教程

Appearance

43.6. 控制结构# ​

43.6.1. 从函数返回
43.6.2. 从过程返回
43.6.3. 调用过程
43.6.4. 条件
43.6.5. 简单循环
43.6.6. 循环遍历查询结果
43.6.7. 循环遍历数组
43.6.8. 捕获错误
43.6.9. 获取执行位置信息

控制结构可能是PL/pgSQL中最有用的（也是最重要的）部分。使用PL/pgSQL的控制结构，您可以非常灵活且高效地操作PostgreSQL数据。

43.6.1. 从函数返回# ​

有两个命令可用于从函数返回数据：RETURN和RETURN NEXT。

43.6.1.1.RETURN# ​

RETURN expression;

RETURN带表达式将终止函数，并将*expression*的值返回给调用方。此形式用于不返回集合的PL/pgSQL函数。

在返回标量类型的函数中，表达式的结果将自动转换为函数的返回类型，如分配中所述。但要返回复合（行）值，您必须编写一个表达式，准确地传递请求的列集。这可能需要使用显式转换。

如果您使用输出参数声明函数，只需编写RETURN，而不要编写表达式。将返回输出参数变量的当前值。

如果您声明函数返回void，可以使用RETURN语句提前退出函数；但不要在RETURN后编写表达式。

不能将函数的返回值留空。如果控制到达函数顶级块的末尾，而没有遇到RETURN语句，则会发生运行时错误。但是，此限制不适用于具有输出参数的函数和返回void的函数。在这些情况下，如果顶级块完成，则会自动执行RETURN语句。

一些示例

-- functions returning a scalar type
RETURN 1 + 2;
RETURN scalar_var;

-- functions returning a composite type
RETURN composite_type_var;
RETURN (1, 2, 'three'::text);  -- must cast columns to correct types

43.6.1.2.RETURN NEXT和RETURN QUERY# ​

RETURN NEXT expression;
RETURN QUERY query;
RETURN 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的函数示例

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 get_all_foo() 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 get_all_foo();

下面是一个使用RETURN QUERY的函数示例

CREATE FUNCTION get_available_flightid(date) RETURNS SETOF integer AS
$BODY$
BEGIN
    RETURN QUERY SELECT flightid
                   FROM flight
                  WHERE flightdate >= $1
                    AND flightdate < ($1 + 1);

    -- Since execution is not finished, we can check whether rows were returned
    -- and raise exception if not.
    IF NOT FOUND THEN
        RAISE EXCEPTION 'No flight at %.', $1;
    END IF;

    RETURN;
 END;
$BODY$
LANGUAGE plpgsql;

-- Returns available flights or raises exception if there are no
-- available flights.
SELECT * FROM get_available_flightid(CURRENT_DATE);

注意

如上所述，RETURN NEXT和RETURN QUERY的当前实现会在从函数返回之前存储整个结果集。这意味着如果PL/pgSQL函数生成一个非常大的结果集，则性能可能会很差：数据将被写入磁盘以避免内存耗尽，但函数本身在整个结果集生成之前不会返回。未来的PL/pgSQL版本可能会允许用户定义没有此限制的集合返回函数。目前，开始将数据写入磁盘的点由work_mem配置变量控制。有足够内存来在内存中存储较大结果集的管理员应考虑增加此参数。

43.6.2. 从过程返回# ​

过程没有返回值。因此，过程可以在没有RETURN语句的情况下结束。如果您希望使用RETURN语句提前退出代码，只需编写RETURN，而无需表达式。

如果过程具有输出参数，则输出参数变量的最终值将返回给调用方。

43.6.3. 调用过程# ​

PL/pgSQL函数、过程或DO块可以使用CALL调用过程。输出参数的处理方式与CALL在普通 SQL 中的工作方式不同。过程的每个OUT或INOUT参数都必须对应于CALL语句中的一个变量，并且过程返回的任何内容都会在返回后重新分配给该变量。例如

CREATE PROCEDURE triple(INOUT x int)
LANGUAGE plpgsql
AS $$
BEGIN
    x := x * 3;
END;
$$;

DO $$
DECLARE myvar int := 5;
BEGIN
  CALL triple(myvar);
  RAISE NOTICE 'myvar = %', myvar;  -- prints 15
END;
$$;

对应于输出参数的变量可以是简单变量或复合类型变量的字段。目前，它不能是数组的元素。

43.6.4. 条件# ​

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

43.6.4.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;

43.6.4.2.IF-THEN-ELSE# ​

IF boolean-expression THEN
    statements
ELSE
    statements
END IF;

IF-THEN-ELSE语句通过允许您指定一组备用语句（如果条件不为真，则应执行该语句）来添加到IF-THEN中。（请注意，这包括条件评估为 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;

43.6.4.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繁琐得多。

43.6.4.4. 简单CASE# ​

CASE search-expression
    WHEN expression [, expression [ ... ]] THEN
      statements
  [ WHEN expression [, expression [ ... ]] THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

CASE的简单形式基于操作数的相等性提供条件执行。search-expression（一次）进行评估，并依次与WHEN子句中的每个expression进行比较。如果找到匹配项，则执行相应的statements，然后控制权将传递给END CASE之后的下一条语句。（后续WHEN表达式不会进行评估。）如果未找到匹配项，则执行ELSE``statements；但如果不存在ELSE，则会引发CASE_NOT_FOUND异常。

以下是一个简单的示例

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

43.6.4.5. 已搜索CASE# ​

CASE
    WHEN boolean-expression THEN
      statements
  [ WHEN boolean-expression THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

CASE的已搜索形式基于布尔表达式的真值提供条件执行。每个WHEN子句的boolean-expression会依次进行评估，直到找到一个产生true的子句。然后执行相应的statements，然后控制权将传递给END CASE之后的下一条语句。（后续WHEN表达式不会进行评估。）如果未找到真结果，则执行ELSE``statements；但如果不存在ELSE，则会引发CASE_NOT_FOUND异常。

以下是一个示例

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;

此形式的CASE与IF-THEN-ELSIF完全等效，除了以下规则：达到一个被省略的ELSE子句会导致一个错误，而不是什么都不做。

43.6.5. 简单循环# ​

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

43.6.5.1.LOOP# ​

[ <<label>> ]
LOOP
    statements
END LOOP [ label ];

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

43.6.5.2.EXIT# ​

EXIT [ label ] [ WHEN boolean-expression ];

如果没有给出*label，则最内层循环将被终止，并且END LOOP之后的语句将被执行。如果给出了label*，则它必须是当前或某些外部嵌套循环或块的标签。然后，命名的循环或块将被终止，并且控制权将继续执行循环/块的相应END之后的语句。

如果指定了WHEN，则仅当*boolean-expression*为 true 时，才会退出循环。否则，控制权将传递给EXIT之后的语句。

EXIT可用于所有类型的循环；它不限于与无条件循环一起使用。

当与BEGIN块一起使用时，EXIT会将控制权传递给块末尾之后的下一条语句。请注意，必须为此目的使用标签；未标记的EXIT永远不会被视为与BEGIN块匹配。（这是PostgreSQL8.4 之前的版本的变化，这些版本允许未标记的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;

43.6.5.3.CONTINUE# ​

CONTINUE [ label ] [ WHEN boolean-expression ];

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

如果指定了WHEN，则仅当*boolean-expression*为 true 时，才会开始循环的下一个迭代。否则，控制权将传递给CONTINUE之后的语句。

CONTINUE可用于所有类型的循环；它不限于与无条件循环一起使用。

示例

LOOP
    -- some computations
    EXIT WHEN count > 100;
    CONTINUE WHEN count < 50;
    -- some computations for count IN [50 .. 100]
END LOOP;

43.6.5.4.WHILE# ​

[ <<label>> ]
WHILE boolean-expression LOOP
    statements
END LOOP [ label ];

WHILE语句会重复一系列语句，只要*boolean-expression*求值为 true。该表达式在每次进入循环体之前都会被检查。

例如

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
    -- some computations here
END LOOP;

WHILE NOT done LOOP
    -- some computations here
END LOOP;

43.6.5.5.FOR（整数变体）# ​

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

此形式的FOR创建一个循环，对一系列整数值进行迭代。变量*name*自动定义为类型integer，并且仅存在于循环内（循环内忽略变量名称的任何现有定义）。进入循环时，给出范围下限和上限的两个表达式将被评估一次。如果未指定BY子句，则迭代步长为 1，否则为BY子句中指定的值，该值在循环进入时再次被评估一次。如果指定了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情况下小于上限），则根本不会执行循环体。不会引发任何错误。

如果*label附加到FOR循环，则可以使用限定名称（使用该label*）引用整数循环变量。

43.6.6. 遍历查询结果# ​

使用不同类型的FOR循环，您可以遍历查询结果并相应地处理该数据。语法如下

[ <<label>> ]
FOR target IN query LOOP
    statements
END LOOP [ label ];

*target是记录变量、行变量或标量变量的逗号分隔列表。将target逐个分配给query*产生的每一行，并针对每一行执行循环体。以下是一个示例

CREATE FUNCTION refresh_mviews() RETURNS integer AS $$
DECLARE
    mviews RECORD;
BEGIN
    RAISE NOTICE 'Refreshing all materialized views...';

    FOR mviews IN
       SELECT n.nspname AS mv_schema,
              c.relname AS mv_name,
              pg_catalog.pg_get_userbyid(c.relowner) AS owner
         FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
        WHERE c.relkind = 'm'
     ORDER BY 1
    LOOP

        -- Now "mviews" has one record with information about the materialized view

        RAISE NOTICE 'Refreshing materialized view %.% (owner: %)...',
                     quote_ident(mviews.mv_schema),
                     quote_ident(mviews.mv_name),
                     quote_ident(mviews.owner);
        EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I', mviews.mv_schema, mviews.mv_name);
    END LOOP;

    RAISE NOTICE 'Done refreshing materialized views.';
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

如果循环因EXIT语句而终止，则仍可以在循环后访问最后分配的行值。

此类FOR语句中使用的*query*可以是向调用者返回行的任何 SQL 命令：SELECT是最常见的情况，但您还可以使用INSERT、UPDATE或DELETE，并带有RETURNING子句。某些实用命令（如EXPLAIN）也可以使用。

PL/pgSQL变量将被查询参数替换，并且查询计划将被缓存以备可能重新使用，如第 43.11.1 节和第 43.11.2 节中详细讨论的。

FOR-IN-EXECUTE语句是遍历行的另一种方式

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

这类似于之前的形式，不同之处在于源查询指定为一个字符串表达式，该表达式在每次进入FOR循环时进行求值和重新规划。这允许程序员选择预规划查询的速度或动态查询的灵活性，就像使用普通EXECUTE语句一样。与EXECUTE一样，可以通过USING将参数值插入动态命令。

指定应迭代其结果的查询的另一种方法是将其声明为游标。这在第 43.7.4 节中进行了描述。

43.6.7. 遍历数组# ​

FOREACH循环与FOR循环非常相似，但它不遍历 SQL 查询返回的行，而是遍历数组值中的元素。（通常，FOREACH用于遍历复合值表达式的组件；除了数组之外，未来可能会添加用于遍历复合体的变体。）用于遍历数组的FOREACH语句是

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

如果没有SLICE，或者如果指定了SLICE 0，则循环将遍历通过求值*expression*生成的数组的各个元素。*target*变量按顺序分配给每个元素值，并且为每个元素执行循环体。下面是一个遍历整数数组元素的示例

CREATE FUNCTION sum(int[]) RETURNS int8 AS $$
DECLARE
  s int8 := 0;
  x int;
BEGIN
  FOREACH x IN ARRAY $1
  LOOP
    s := s + x;
  END LOOP;
  RETURN s;
END;
$$ LANGUAGE plpgsql;

元素按存储顺序访问，而不管数组维数是多少。尽管*target*通常只是一个变量，但在遍历复合值（记录）数组时，它可以是变量列表。在这种情况下，对于每个数组元素，变量将从复合值的连续列中分配。

如果SLICE值为正，则FOREACH将遍历数组的切片，而不是单个元素。SLICE值必须是不大于数组维数的整数常量。*target*变量必须是一个数组，它接收数组值的连续切片，其中每个切片由SLICE指定的维数指定。下面是一个遍历一维切片的示例

CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$
DECLARE
  x int[];
BEGIN
  FOREACH x SLICE 1 IN ARRAY $1
  LOOP
    RAISE NOTICE 'row = %', x;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT scan_rows(ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]);

NOTICE:  row = {1,2,3}
NOTICE:  row = {4,5,6}
NOTICE:  row = {7,8,9}
NOTICE:  row = {10,11,12}

43.6.8. 捕获错误# ​

默认情况下，PL/pgSQL函数中发生的任何错误都会中止函数和周围事务的执行。您可以使用带有BEGIN块和EXCEPTION子句来捕获错误并从中恢复。语法是BEGIN块的正常语法的扩展

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
EXCEPTION
    WHEN condition [ OR condition ... ] THEN
        handler_statements
    [ WHEN condition [ OR condition ... ] THEN
          handler_statements
      ... ]
END;

如果没有发生错误，这种形式的块只会执行所有*语句，然后控制权会传递给END之后的下一条语句。但是，如果语句中发生错误，则会放弃对语句的进一步处理，并且控制权会传递给EXCEPTION列表。该列表将搜索第一个与发生的错误匹配的条件。如果找到匹配项，则会执行相应的处理程序语句*，然后控制权会传递给END之后的下一条语句。如果找不到匹配项，则错误将传播出去，就好像根本没有EXCEPTION子句一样：错误可以被带有EXCEPTION的封闭块捕获，或者如果没有，它将中止函数的处理。

*条件*名称可以是附录 A中显示的任何名称。类别名称与类别中的任何错误匹配。特殊条件名称OTHERS匹配除QUERY_CANCELED和ASSERT_FAILURE之外的每种错误类型。（可以按名称捕获这两个错误类型，但这通常不明智。）条件名称不区分大小写。此外，可以通过SQLSTATE代码指定错误条件；例如，这些是等效的

WHEN division_by_zero THEN ...
WHEN SQLSTATE '22012' THEN ...

如果在选定的*处理程序语句*中发生新错误，则此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。

提示

包含EXCEPTION子句的块比没有EXCEPTION子句的块进入和退出要昂贵得多。因此，不要在不需要时使用EXCEPTION。

示例 43.2. 使用UPDATE/INSERT的异常

此示例使用异常处理来执行UPDATE或INSERT（视情况而定）。建议应用程序使用INSERT和ON CONFLICT DO UPDATE，而不是实际使用此模式。此示例主要用于说明PL/pgSQL控制流结构的使用

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');

此编码假设unique_violation错误是由INSERT引起的，而不是由表上的触发器函数中的INSERT引起的。如果表上有多个唯一索引，它也可能出现异常，因为它会重试操作，而不管哪个索引导致了错误。通过使用接下来讨论的功能来检查捕获的错误是否为预期的错误，可以获得更高的安全性。

43.6.8.1. 获取有关错误的信息# ​

异常处理程序经常需要识别发生的特定错误。在PL/pgSQL中有两种方法可以获取有关当前异常的信息：特殊变量和GET STACKED DIAGNOSTICS命令。

在异常处理程序中，特殊变量SQLSTATE包含与引发的异常相对应的错误代码（有关可能的错误代码列表，请参阅表 A.1）。特殊变量SQLERRM包含与异常关联的错误消息。这些变量在异常处理程序之外未定义。

在异常处理程序中，还可以使用GET STACKED DIAGNOSTICS命令检索有关当前异常的信息，其形式为

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

每个*item是一个关键字，用于标识要分配给指定variable*（应为正确的数据类型以接收它）的状态值。当前可用的状态项显示在表 43.2中。

表 43.2. 错误诊断项

名称类型描述
RETURNED_SQLSTATE文本异常的 SQLSTATE 错误代码
COLUMN_NAME文本与异常相关的列的名称
CONSTRAINT_NAME文本与异常相关的约束的名称
PG_DATATYPE_NAME文本与异常相关的数据类型的名称
MESSAGE_TEXT文本异常主要消息的文本
TABLE_NAME文本与异常相关的表的名称
SCHEMA_NAME文本与异常相关的模式的名称
PG_EXCEPTION_DETAIL文本异常的详细信息消息的文本（如果有）
PG_EXCEPTION_HINT文本异常的提示消息的文本（如果有）
PG_EXCEPTION_CONTEXT文本异常发生时的调用堆栈描述文本行（参见第 43.6.9 节）

如果异常未为某个项目设置值，则将返回一个空字符串。

以下是一个示例

DECLARE
  text_var1 text;
  text_var2 text;
  text_var3 text;
BEGIN
  -- some processing which might cause an exception
  ...
EXCEPTION WHEN OTHERS THEN
  GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
                          text_var2 = PG_EXCEPTION_DETAIL,
                          text_var3 = PG_EXCEPTION_HINT;
END;

43.6.9. 获取执行位置信息# ​

之前在第 43.5.5 节中描述过的GET DIAGNOSTICS命令检索有关当前执行状态的信息（而上面讨论的GET STACKED DIAGNOSTICS命令报告有关先前错误的执行状态的信息）。其PG_CONTEXT状态项目对于识别当前执行位置非常有用。PG_CONTEXT返回一个文本字符串，其中包含描述调用堆栈的文本行。第一行引用当前函数和当前执行的GET DIAGNOSTICS命令。第二行和任何后续行引用调用堆栈中更上层的调用函数。例如

CREATE OR REPLACE FUNCTION outer_func() RETURNS integer AS $$
BEGIN
  RETURN inner_func();
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$
DECLARE
  stack text;
BEGIN
  GET DIAGNOSTICS stack = PG_CONTEXT;
  RAISE NOTICE E'--- Call Stack ---\n%', stack;
  RETURN 1;
END;
$$ LANGUAGE plpgsql;

SELECT outer_func();

NOTICE:  --- Call Stack ---
PL/pgSQL function inner_func() line 5 at GET DIAGNOSTICS
PL/pgSQL function outer_func() line 3 at RETURN
CONTEXT:  PL/pgSQL function outer_func() line 3 at RETURN
 outer_func
 ------------
           1
(1 row)

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