Postgresql 中文操作指南

43.9. Errors and Messages #

43.9.1. Reporting Errors and Messages #

使用 RAISE 语句报告消息并引发错误。

Use the RAISE statement to report messages and raise errors.

RAISE [ level ] 'format' [, expression [, ... ]] [ USING option = expression [, ... ] ];
RAISE [ level ] condition_name [ USING option = expression [, ... ] ];
RAISE [ level ] SQLSTATE 'sqlstate' [ USING option = expression [, ... ] ];
RAISE [ level ] USING option = expression [, ... ];
RAISE ;

level_选项指定错误严重性。允许的级别为_DEBUG，LOG，INFO，NOTICE，WARNING_和_EXCEPTION，其中_EXCEPTION_为默认值。_EXCEPTION_引发错误（通常会中止当前事务）；其他级别仅生成不同优先级的消息。是否将特定优先级的信息报告给客户端、写入服务器日志或两者，由 log_min_messages和 client_min_messages配置变量控制。有关更多信息，请参见 Chapter 20。

The level option specifies the error severity. Allowed levels are DEBUG, LOG, INFO, NOTICE, WARNING, and EXCEPTION, with EXCEPTION being the default. EXCEPTION raises an error (which normally aborts the current transaction); the other levels only generate messages of different priority levels. Whether messages of a particular priority are reported to the client, written to the server log, or both is controlled by the log_min_messages and client_min_messages configuration variables. See Chapter 20 for more information.

在 level（如果有）后，你可以指定一个 format 字符串（可以是简单的字符串文本，不能是表达式）。格式字符串指定要报告的错误消息文本。格式字符串后面可以接可选参数表达式，将其插入到消息中。在格式字符串中，% 将被下一个可选参数值的字符串表示所替换。请写 %% 来发出一个文本 %。参数数量必须与格式字符串中的 % 占位符数量匹配，否则会在函数编译期间引发错误。

After level if any, you can specify a format string (which must be a simple string literal, not an expression). The format string specifies the error message text to be reported. The format string can be followed by optional argument expressions to be inserted into the message. Inside the format string, % is replaced by the string representation of the next optional argument’s value. Write %% to emit a literal %. The number of arguments must match the number of % placeholders in the format string, or an error is raised during the compilation of the function.

在此示例中，v_job_id 的值将替换字符串中的 %：

In this example, the value of v_job_id will replace the % in the string:

RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;

可以通过编写 USING 后跟 option = expression 项，附加附加信息到错误报告。每个 expression 可以是任何字符串值表达式。允许的 option 关键字有：

You can attach additional information to the error report by writing USING followed by option = expression items. Each expression can be any string-valued expression. The allowed option key words are:

  • MESSAGE #

    • Sets the error message text. This option can’t be used in the form of RAISE that includes a format string before USING.

  • DETAIL #

    • Supplies an error detail message.

  • HINT #

    • Supplies a hint message.

  • ERRCODE #

    • Specifies the error code (SQLSTATE) to report, either by condition name, as shown in Appendix A, or directly as a five-character SQLSTATE code.

  • COLUMN_CONSTRAINT_DATATYPE_TABLE_SCHEMA #

    • Supplies the name of a related object.

此示例将使用给定的错误消息和提示终止事务：

This example will abort the transaction with the given error message and hint:

RAISE EXCEPTION 'Nonexistent ID --> %', user_id
      USING HINT = 'Please check your user ID';

这两个示例展示了设置 SQLSTATE 的等效方式：

These two examples show equivalent ways of setting the SQLSTATE:

RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';

其中第二个 RAISE 语法的主参数是要报告的条件名称或 SQLSTATE，例如：

There is a second RAISE syntax in which the main argument is the condition name or SQLSTATE to be reported, for example:

RAISE division_by_zero;
RAISE SQLSTATE '22012';

在此语法中，USING 可用于提供自定义错误消息、详情或提示。另一种执行以上示例的方式是

In this syntax, USING can be used to supply a custom error message, detail, or hint. Another way to do the earlier example is

RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;

另一种变体是写入 RAISE USING 或 RAISE _level USING_ 并将所有其他内容放入 USING 列表。

Still another variant is to write RAISE USING or RAISE _level USING_ and put everything else into the USING list.

RAISE 的最后一个变量根本没有参数。此表单只能在 BEGIN 块的 EXCEPTION 子句中使用；它导致当前正在处理的错误重新抛出。

The last variant of RAISE has no parameters at all. This form can only be used inside a BEGIN block’s EXCEPTION clause; it causes the error currently being handled to be re-thrown.

Note

在 PostgreSQL 9.1 之前，RAISE 未带有参数，会被解释为重新抛出包含活动异常处理程序的块的错误。因此，嵌套在该处理程序内的 EXCEPTION 子句无法捕获它，即使 RAISE 在嵌套 EXCEPTION 子句的块中也是如此。这被认为是令人惊讶的，并且与 Oracle 的 PL/SQL 不兼容。

Before PostgreSQL 9.1, RAISE without parameters was interpreted as re-throwing the error from the block containing the active exception handler. Thus an EXCEPTION clause nested within that handler could not catch it, even if the RAISE was within the nested EXCEPTION clause’s block. This was deemed surprising as well as being incompatible with Oracle’s PL/SQL.

如果在 RAISE EXCEPTION 命令中没有指定条件名称或 SQLSTATE，则默认使用 raise_exception (P0001)。如果未指定消息文本，则默认将条件名称或 SQLSTATE 用作消息文本。

If no condition name nor SQLSTATE is specified in a RAISE EXCEPTION command, the default is to use raise_exception (P0001). If no message text is specified, the default is to use the condition name or SQLSTATE as message text.

Note

当通过 SQLSTATE 代码指定错误代码时，您不仅限于预定义的错误代码，还可以选择除 00000 之外的由五个数字和/或大写 ASCII 字母组成的任何错误代码。建议您避免抛出以三个零结尾的错误代码，因为这些是类别代码，只能通过捕获整个类别来捕获。

When specifying an error code by SQLSTATE code, you are not limited to the predefined error codes, but can select any error code consisting of five digits and/or upper-case ASCII letters, other than 00000. It is recommended that you avoid throwing error codes that end in three zeroes, because these are category codes and can only be trapped by trapping the whole category.

43.9.2. Checking Assertions #

ASSERT 语句是将调试检查插入到 PL/pgSQL 函数中的方便简写。

The ASSERT statement is a convenient shorthand for inserting debugging checks into PL/pgSQL functions.

ASSERT condition [ , message ];

condition 是预计始终计算为真的布尔表达式；如果计算为真，则 ASSERT 语句不执行任何进一步的操作。如果结果为假或 null，则会引发 ASSERT_FAILURE 异常。（如果在计算 condition 时发生错误，则将其报告为一个普通错误）。

The condition is a Boolean expression that is expected to always evaluate to true; if it does, the ASSERT statement does nothing further. If the result is false or null, then an ASSERT_FAILURE exception is raised. (If an error occurs while evaluating the condition, it is reported as a normal error.)

如果提供了可选的 message，它是一个表达式，如果其结果（如果非空）替换了默认的错误消息文本“断言失败”，则 condition 失败。在断言成功完成的正常情况下，不会计算 message 表达式。

If the optional message is provided, it is an expression whose result (if not null) replaces the default error message text “assertion failed”, should the condition fail. The message expression is not evaluated in the normal case where the assertion succeeds.

可以通过配置参数 plpgsql.check_asserts 启用或禁用断言测试，此参数采用布尔值；默认值为 on。如果此参数为 off，那么 ASSERT 语句将不执行任何操作。

Testing of assertions can be enabled or disabled via the configuration parameter plpgsql.check_asserts, which takes a Boolean value; the default is on. If this parameter is off then ASSERT statements do nothing.

请注意，ASSERT_用于检测程序缺陷，而不是报告普通错误状况。为此，请使用上面描述的_RAISE 语句。

Note that ASSERT is meant for detecting program bugs, not for reporting ordinary error conditions. Use the RAISE statement, described above, for that.