Postgresql 中文操作指南

44.8. Error Handling in PL/Tcl #

Tcl 代码或从 PL/Tcl 函数调用的 Tcl 代码可引发错误，方法是执行某些无效操作或使用 Tcl error 命令或 PL/Tcl 的 elog 命令生成错误。可以使用 Tcl catch 命令在 Tcl 中捕获此类错误。如果错误未被捕获，但允许传播到 PL/Tcl 函数执行的顶级，则将其报告为函数的调用查询中的 SQL 错误。

Tcl code within or called from a PL/Tcl function can raise an error, either by executing some invalid operation or by generating an error using the Tcl error command or PL/Tcl’s elog command. Such errors can be caught within Tcl using the Tcl catch command. If an error is not caught but is allowed to propagate out to the top level of execution of the PL/Tcl function, it is reported as an SQL error in the function’s calling query.

相反，在 PL/Tcl 的 spi_exec、spi_prepare 和 spi_execp 命令中发生的 SQL 错误报告为 Tcl 错误，因此可由 Tcl 的 catch 命令捕获。（这些 PL/Tcl 命令中的每个命令都在子事务中运行其 SQL 操作，并在出错时回滚，以便自动清理所有部分完成的操作。）同样，如果错误传播到顶级而未被捕获，则会变成 SQL 错误。

Conversely, SQL errors that occur within PL/Tcl’s spi_exec, spi_prepare, and spi_execp commands are reported as Tcl errors, so they are catchable by Tcl’s catch command. (Each of these PL/Tcl commands runs its SQL operation in a subtransaction, which is rolled back on error, so that any partially-completed operation is automatically cleaned up.) Again, if an error propagates out to the top level without being caught, it turns back into an SQL error.

Tcl 提供一个 errorCode_变量，它可以用一种对 Tcl 程序易于解释的形式表示有关错误的附加信息。内容采用 Tcl 列表格式，第一个词标识报告错误的子系统或库；此后内容留给各个子系统或库。对于 PL/Tcl 命令报告的数据库错误，第一个词是 _POSTGRES，第二个词是 PostgreSQL 版本号，其他词是提供错误详细信息的字段名/值对。字段 SQLSTATE、condition_和 _message_始终提供（前两个表示错误代码和条件名称，如 Appendix A所示）。可能存在的字段包括 _detail、hint、context、schema、table、column、datatype、constraint、statement、cursor_position、filename、lineno_和 _funcname。

Tcl provides an errorCode variable that can represent additional information about an error in a form that is easy for Tcl programs to interpret. The contents are in Tcl list format, and the first word identifies the subsystem or library reporting the error; beyond that the contents are left to the individual subsystem or library. For database errors reported by PL/Tcl commands, the first word is POSTGRES, the second word is the PostgreSQL version number, and additional words are field name/value pairs providing detailed information about the error. Fields SQLSTATE, condition, and message are always supplied (the first two represent the error code and condition name as shown in Appendix A). Fields that may be present include detail, hint, context, schema, table, column, datatype, constraint, statement, cursor_position, filename, lineno, and funcname.

使用 PL/Tcl 的 errorCode 信息的一种便捷方法是将其加载到数组中，以便字段名称成为数组下标。相关代码可能如下所示

A convenient way to work with PL/Tcl’s errorCode information is to load it into an array, so that the field names become array subscripts. Code for doing that might look like

if {[catch { spi_exec $sql_command }]} {
    if {[lindex $::errorCode 0] == "POSTGRES"} {
        array set errorArray $::errorCode
        if {$errorArray(condition) == "undefined_table"} {
            # deal with missing table
        } else {
            # deal with some other type of SQL error
        }
    }
}

（双冒号明确指定 errorCode 是一个全局变量。）

(The double colons explicitly specify that errorCode is a global variable.)