44.5. 从 PL/Tcl 访问数据库#
在本节中,我们遵循通常的 Tcl 惯例,使用问号而不是方括号来指示语法概要中的可选元素。以下命令可用于从 PL/Tcl 函数的主体访问数据库
spi_exec
?-countn
? ?-arrayname
?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 循环构造类似;特别是continue
和break
在循环体中以通常的方式工作。如果查询结果的列为 null,则其目标变量为 “unset”,而不是被设置。
spi_prepare
query
typelist
准备并保存查询计划以供以后执行。保存的计划将保留在当前会话的生命周期内。
查询可以使用参数,即在实际执行计划时要提供的值的占位符。在查询字符串中,通过符号
$1
...$
引用参数。如果查询使用参数,则必须将参数类型的名称作为 Tcl 列表给出。(如果未使用参数,则为n
typelist
编写一个空列表。)spi_prepare
的返回值是一个查询 ID,用于后续调用spi_execp
。有关示例,请参见spi_execp
。spi_execp
?-countn
? ?-arrayname
? ?-nullsstring
?queryid
?value-list
? ?loop-body
?执行先前使用
spi_prepare
准备的查询。queryid
是spi_prepare
返回的 ID。如果查询引用参数,则必须提供value-list
。这是参数实际值的 Tcl 列表。该列表必须与先前提供给spi_prepare
的参数类型列表长度相同。如果查询没有参数,则省略value-list
。-nulls
的可选值是一个空格和'n'
字符串,告诉spi_execp
哪些参数是 null 值。如果给出,它必须与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) ]} { # 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_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 命令字符串的一部分进行解析。elog
level
msg
发出日志或错误消息。可能的级别为
DEBUG
、LOG
、INFO
、NOTICE
、WARNING
、ERROR
和FATAL
。ERROR
引发错误条件;如果周围的 Tcl 代码未捕获到此错误,则错误将传播到调用查询,从而导致中止当前事务或子事务。这实际上与 Tclerror
命令相同。FATAL
中止事务并导致当前会话关闭。(在 PL/Tcl 函数中使用此错误级别可能没有充分的理由,但出于完整性考虑提供了此错误级别。)其他级别仅生成不同优先级的消息。向客户端报告特定优先级的消息、写入服务器日志或同时进行这两项操作由 log_min_messages 和 client_min_messages 配置变量控制。有关更多信息,请参阅 第 20 章 和 第 44.8 节。