Skip to content

44.5. 从 PL/Tcl 访问数据库#

在本节中,我们遵循通常的 Tcl 惯例,使用问号而不是方括号来指示语法概要中的可选元素。以下命令可用于从 PL/Tcl 函数的主体访问数据库

spi_exec ?-count n? ?-array name? command ?loop-body?

执行给定为字符串的 SQL 命令。命令中的错误会导致引发错误。否则,spi_exec 的返回值是命令处理的行数(选择、插入、更新或删除),或者如果命令是实用程序语句,则为零。此外,如果命令是 SELECT 语句,则所选列的值将按如下所述放入 Tcl 变量中。

可选的 -count 值告诉 spi_exec 在检索到 n 行后停止,就像查询包含 LIMIT 子句一样。如果 n 为零,则查询运行到完成,与省略 -count 时相同。

如果命令是 SELECT 语句,则结果列的值将放入以列命名的 Tcl 变量中。如果给出了 -array 选项,则列值将存储到命名的关联数组的元素中,列名用作数组索引。此外,结果中的当前行号(从零开始计数)将存储到名为 .tupno 的数组元素中,除非该名称在结果中用作列名。

如果命令是 SELECT 语句,并且没有给出 loop-body 脚本,则只有第一行结果存储到 Tcl 变量或数组元素中;剩余行(如果有)将被忽略。如果查询没有返回任何行,则不会进行存储。(可以通过检查 spi_exec 的结果来检测这种情况。)例如

spi_exec "SELECT count(*) AS cnt FROM pg_proc"

将 Tcl 变量 $cnt 设置为 pg_proc 系统目录中的行数。

如果给出了可选的 loop-body 参数,则它是一段 Tcl 脚本,对查询结果中的每一行执行一次。(如果给定的命令不是 SELECT,则忽略 loop-body。)在每次迭代之前,当前行的列值都存储到 Tcl 变量或数组元素中。例如

spi_exec -array C "SELECT * FROM pg_class" {
    elog DEBUG "have table $C(relname)"
}

将打印 pg_class 的每一行的日志消息。此功能与其他 Tcl 循环构造类似;特别是 continuebreak 在循环体中以通常的方式工作。

如果查询结果的列为 null,则其目标变量为 unset,而不是被设置。

spi_prepare query typelist

准备并保存查询计划以供以后执行。保存的计划将保留在当前会话的生命周期内。

查询可以使用参数,即在实际执行计划时要提供的值的占位符。在查询字符串中,通过符号 $1 ... $n 引用参数。如果查询使用参数,则必须将参数类型的名称作为 Tcl 列表给出。(如果未使用参数,则为 typelist 编写一个空列表。)

spi_prepare 的返回值是一个查询 ID,用于后续调用 spi_execp。有关示例,请参见 spi_execp

spi_execp ?-count n? ?-array name? ?-nulls string? queryid ?value-list? ?loop-body?

执行先前使用 spi_prepare 准备的查询。 queryidspi_prepare 返回的 ID。如果查询引用参数,则必须提供 value-list。这是参数实际值的 Tcl 列表。该列表必须与先前提供给 spi_prepare 的参数类型列表长度相同。如果查询没有参数,则省略 value-list

-nulls 的可选值是一个空格和 'n' 字符串,告诉 spi_execp 哪些参数是 null 值。如果给出,它必须与 value-list 长度完全相同。如果未给出,则所有参数值均为非空。

除了指定查询及其参数的方式外,spi_execp 的工作方式与 spi_exec 完全相同。 -count-arrayloop-body 选项相同,结果值也相同。

下面是一个使用已准备计划的 PL/Tcl 函数示例

CREATE FUNCTION t1_count(integer, integer) RETURNS integer AS $$
    if {![ info exists GD(plan) ]} {
        # prepare the saved plan on the first call
        set GD(plan) [ spi_prepare \
                "SELECT count(*) AS cnt FROM t1 WHERE num >= \$1 AND num <= \$2" \
                [ list int4 int4 ] ]
    }
    spi_execp -count 1 $GD(plan) [ list $1 $2 ]
    return $cnt
$$ LANGUAGE pltcl;

我们需要在提供给 spi_prepare 的查询字符串中使用反斜杠,以确保 $n 标记将按原样传递给 spi_prepare,而不是被 Tcl 变量替换。

subtransaction command

包含在 command 中的 Tcl 脚本在 SQL 子事务中执行。如果脚本返回错误,则在将错误返回到周围的 Tcl 代码之前,将回滚整个子事务。有关更多详细信息和示例,请参阅 第 44.9 节

quote string

对给定字符串中出现的所有单引号和反斜杠字符加倍。这可用于安全地引用要插入到提供给 spi_execspi_prepare 的 SQL 命令中的字符串。例如,考虑如下 SQL 命令字符串

"SELECT '$val' AS ret"

其中 Tcl 变量 val 实际上包含 doesn't。这将导致最终命令字符串

SELECT 'doesn't' AS ret

这将在 spi_execspi_prepare 期间导致解析错误。要正常工作,提交的命令应包含

SELECT 'doesn''t' AS ret

可以使用以下方式在 PL/Tcl 中形成

"SELECT '[ quote $val ]' AS ret"

spi_execp 的一个优点是,您不必像这样引用参数值,因为参数永远不会作为 SQL 命令字符串的一部分进行解析。

elog level msg

发出日志或错误消息。可能的级别为 DEBUGLOGINFONOTICEWARNINGERRORFATALERROR 引发错误条件;如果周围的 Tcl 代码未捕获到此错误,则错误将传播到调用查询,从而导致中止当前事务或子事务。这实际上与 Tcl error 命令相同。 FATAL 中止事务并导致当前会话关闭。(在 PL/Tcl 函数中使用此错误级别可能没有充分的理由,但出于完整性考虑提供了此错误级别。)其他级别仅生成不同优先级的消息。向客户端报告特定优先级的消息、写入服务器日志或同时进行这两项操作由 log_min_messagesclient_min_messages 配置变量控制。有关更多信息,请参阅 第 20 章第 44.8 节