PostgreSQL 13.1 中文入门教程 PostgreSQL 从 PL/Tcl 访问数据库

2024-02-25 开发教程 PostgreSQL 13.1 中文入门教程匿名 3℃

下面的命令可以用来从 PL/Tcl 函数体中访问数据库：

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

执行一个以字符串给出的 SQL 命令。命令中错误将会导致错误发生。否则，spi_exec的返回值是被命令处理的行数（选择、插入、更新或者删除），如果命令是一条功能性语句则返回零。此外，如果命令是一条SELECT语句，被选中的列的值会被放在上文所述的 Tcl 变量中。

可选的-count值告诉spi_exec命令中要处理的最大行数。这种效果类似于用游标建立一个查询然后使用FETCH n。

如果命令是一条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 循环结构。特别是continue和break的动作方式与在循环体中的通常方式相同。

如果一个查询结果的一列为空，为它准备的目标变量不会被建立，而是会被“unset”。

spi_preparequerytypelist

为后面的执行准备并且保存一个查询计划。保存下来的计划将在当前会话的生命期内保持存在。

查询可以使用参数，也就是占位符。在计划真正被执行时将会为占位符提供值。在查询字符串中，可以用符号$1... $n引用参数。如果查询使用了参数，参数类型的名称必须以一个 Tcl 列表的形式给出（如果不使用参数，可以为typelist写一个空列表）。

从spi_prepare返回的值是一个查询 ID，在后续的spi_execp调用中需要用到这个 ID。例子可见spi_execp。

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

执行一个之前用spi_prepare准备的查询。queryid是spi_prepare返回的 ID。如果查询引用参数，则必须提供一个value-list。这是一个参数实际值的 Tcl 列表。这个列表必须和之前传给 spi_prepare的参数类型列表具有相同的长度。如果查询没有参数则可省略value-list。

-nulls的值可选，它是一个空格和'n'字符构成的串，它告诉spi_execp哪些参数是空值。如果给出这个值，它必须正好和value-list长度相等。如果没有给出这个值，所有的参数值都是空。

除了指定查询及其参数的方法，spi_execp和spi_exec很像。-count、-array以及loop-body选项是相同的，并且结果值也一样。

这里是一个使用预备计划的 PL/Tcl 函数的例子：

CREATE FUNCTION t1_count(integer, integer) RETURNS integer AS $$
    if {![ info exists GD(plan) ]} {
        # 第一次调用时准备保存的计划
        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 变量替换。

subtransactioncommand

command中包含的Tcl脚本会被在一个SQL子事务中执行。如果该脚本返回一个错误，那么整个子事务会在把错误返回到外围的Tcl代码之前就回滚。更多细节和例子请参考第 43.9 节。

quotestring

在给定的字符串中双写所有单引号和反斜线字符。这可以被用来引用字符串，以便它们能被安全地插入到传给spi_exec或者spi_prepare的 SQL 命令字符串中。例如，考虑这样的 SQL 命令字符串：

"SELECT '$val' AS ret"

这里的 Tcl 变量val实际上包含doesn't。这将会导致最终的命令串：

SELECT 'doesn't' AS ret

这种命令串会导致spi_exec或spi_prepare期间的解析错误。要正确地工作，提交的命令应该包含：

SELECT 'doesn''t' AS ret

在 PL/Tcl 中可以这样做：

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

spi_execp的一个好处是你不必这样引用参数值，因为参数值不会被作为 SQL 命令串的一部分被解析。

eloglevelmsg

发出一段日志或者错误消息。可能的级别是DEBUG、LOG、INFO、NOTICE、WARNING、ERROR以及FATAL。 ERROR产生一个错误情况。如果周围的 Tcl 代码没有捕捉它，错误会传播到调用查询，导致当前事务或者子事务被中止。这实际上与 Tcl 的error命令相同。FATAL中止事务并且导致当前会话关闭（可能在 PL/Tcl 函数中没有很好的理由来使用这种错误级别，但是为了完整性还是提供了这种级别）。其他级别只产生不同优先级的消息。一个特定级别的消息是被报告给客户端、写入到服务器日志或者两者都做，是由配置变量 log_min_messages和client_min_messages所控制。详见第 19 章和第 43.8 节。

上一节： PostgreSQL PL/Tcl 中的全局数据

下一节： PostgreSQL PL/Tcl 中的触发器函数

"PostgreSQL 13.1 中文入门教程"的目录

PostgreSQL 13.1 中文入门教程 PostgreSQL 从 PL/Tcl 访问数据库