Redrock Postgres 搜索 英文
版本: 9.3 / 9.4 / 9.5 / 9.6 / 10 / 11 / 12 / 13 / 14

43.5. 从 PL/Tcl 访问数据库

下面的命令可以用来从 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 循环结构。特别是continuebreak的动作方式与在循环体中的通常方式相同。

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

spi_prepare query typelist

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

查询可以使用参数,也就是占位符。在计划真正被执行时将会为占位符提供值。在查询字符串中,可以用符号$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准备的查询。queryidspi_prepare返回的 ID。如果查询引用参数,则必须提供一个value-list。这是一个参数实际值的 Tcl 列表。这个列表必须和之前传给spi_prepare的参数类型列表具有相同的长度。如果查询没有参数则可省略value-list

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

除了指定查询及其参数的方法,spi_execpspi_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 变量替换。

spi_lastoid

返回最后一次spi_exec或者spi_execp插入的行的 OID(如果该命令是一个单行INSERT且被修改表包含 OID。否则将会得到零)。

subtransaction command

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

quote string

在给定的字符串中双写所有单引号和反斜线字符。这可以被用来引用字符串,以便它们能被安全地插入到传给spi_exec或者spi_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

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