Postgresql 中文操作指南

43.6. Control Structures #

控制结构可能是 PL/pgSQL 中最有用的(也是最重要的)部分。使用 PL/pgSQL 的控制结构,你可以以非常灵活和强大的方式操作 PostgreSQL 数据。

43.6.1. Returning from a Function #

有两个命令允许你从函数中返回数据:RETURNRETURN 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 and RETURN QUERY #

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

当某个 PL/pgSQL 函数声明为返回 SETOF _sometype_ 时,应当采取略有不同的过程。在该情况下,要返回的各个项由一系列 RETURN NEXTRETURN QUERY 命令指定,然后使用不带参数的最终 RETURN 命令来指示函数执行已完成。 RETURN NEXT 可以与标量和复合数据类型一起使用;而使用复合结果类型时,将返回完整一“表”的结果。 RETURN QUERY 将执行查询的结果追加到函数的结果集中。 RETURN NEXTRETURN QUERY 可以在单个返回集函数中随意混合使用,在这种情况下,它们的结果将被串联。

RETURN NEXTRETURN QUERY 实际上不会从函数返回——它们只是将零个或多个行追加到函数的结果集中。然后,执行继续进行 PL/pgSQL 函数中的下一条语句。随着依次执行 RETURN NEXTRETURN 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);

Note

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

43.6.2. Returning from a Procedure #

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

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

43.6.3. Calling a Procedure #

一个 PL/pgSQL 函数、过程或 DO 块可以使用 CALL 调用过程。输出参数的处理方式与 CALL 在普通 SQL 中的工作方式不同。过程的每个 OUTINOUT 参数都必须对应 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. Conditionals #

IFCASE 语句让你基于特定条件执行替代命令。PL/pgSQL 有三种形式的 IF

和两种形式的 CASE

43.6.4.1. IF-THEN #

IF boolean-expression THEN
    statements
END IF;

IF-THEN 语句是 IF 最简单的形式。如果条件为真,则将执行 THENEND 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 条件都将 not 进行测试。)如果 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. Simple 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. Searched 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;

除了对被省略的 ELSE 子句执行产生会错误而不是不执行任何操作的规则外,此 CASE 形式与 IF-THEN-ELSIF 完全相同。

43.6.5. Simple Loops #

通过 LOOPEXITCONTINUEWHILEFORFOREACH 语句,您可以安排 PL/pgSQL 函数重复一系列命令。

43.6.5.1. LOOP #

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

LOOP 定义一个无条件循环,它无限期地重复,直到由 EXITRETURN 语句终止。在嵌套循环中的 EXITCONTINUE 语句可以使用可选的 label 来指定这些语句所引用的循环。

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 块匹配。(这是 PostgreSQL 8.4 之前版本的一个更改,那些版本允许未标记的 EXITBEGIN 块匹配。)

示例:

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 (Integer Variant) #

[ <<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;

如果下限大于上限(或者小于或等于 upper REVERSE),则根本不执行循环体。不会引发任何错误。

如果 label 附加到 FOR 循环,则可以在限定的名称中使用整数循环变量,并使用 label

43.6.6. Looping through Query Results #

使用其他类型的 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 是最常见的情况,但您也可以将 INSERTUPDATEDELETERETURNING 子句一起使用。某些实用命令(如 EXPLAIN)也可以使用。

PL/pgSQL 变量被查询参数替换,并且查询计划被缓存起来以便可能重新使用,如 Section 43.11.1Section 43.11.2 中详细讨论的。

FOR-IN-EXECUTE 语句是另一种迭代行的办法:

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

这类似于前面的形式,但源查询指定为字符串表达式,它在 FOR 循环的每个条目中计算并重新计划。这允许程序员根据普通 EXECUTE 语句选择预计划查询的速度或动态查询的灵活性。与 EXECUTE 一样,可以将参数值通过 USING 插入动态命令。

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

43.6.7. Looping through Arrays #

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. Trapping Errors #

默认情况下,PL/pgSQL 函数中发生的任何错误都会中止函数的执行和周围的事务。您可以使用带有 EXCEPTION 子句的 BEGIN 块来捕获错误并从中恢复。语法是 normal 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 名称可以是 Appendix A 中显示的任何名称。类别名称匹配其类别中的任何错误。特殊条件名称 OTHERS 匹配除 QUERY_CANCELEDASSERT_FAILURE 之外的每种错误类型。(按名称捕获这两个错误类型是可能的,但通常不明智。)条件名称不区分大小写。此外,错误条件可以通过 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

Example 43.2. Exceptions with UPDATE/INSERT

此示例使用异常处理来执行 UPDATEINSERT,视情况而定。建议应用程序将 INSERTON 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. Obtaining Information about an Error #

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

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

在异常处理程序中,还可以使用格式为 GET STACKED DIAGNOSTICSGET STACKED DIAGNOSTICS 命令来检索有关当前异常的信息:

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

每个 item 都是一个关键字,用于识别要分配给指定的 variable 的状态值(应为接受它的正确数据类型)。当前可用的状态项目显示在 Table 43.2 中。

Table 43.2. Error Diagnostics Items

Name

Type

Description

RETURNED_SQLSTATE

text

异常的 SQLSTATE 错误代码

COLUMN_NAME

text

与异常相关的列的名称

CONSTRAINT_NAME

text

与异常相关的约束的名称

PG_DATATYPE_NAME

text

与异常相关的数据类型的名称

MESSAGE_TEXT

text

异常主要消息的文本

TABLE_NAME

text

与异常相关的表的名称

SCHEMA_NAME

text

与异常相关的架构名称

PG_EXCEPTION_DETAIL

text

如果有,异常详细信息消息的文本

PG_EXCEPTION_HINT

text

如果有,异常提示消息的文本

PG_EXCEPTION_CONTEXT

text

异常发生时描述调用堆栈的文本行(请参阅 Section 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. Obtaining Execution Location Information #

GET DIAGNOSTICS 命令(之前在 Section 43.5.5 中描述过)检索有关当前执行状态的信息(而上面讨论的 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 返回相同类型的堆栈跟踪,不过它描述的是检测到错误的位置,而不是当前的位置。