37.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语句,那么SELECT的结果列的数值将放在用各字段名命名的 Tcl 变量中。 如果给出了 -array 选项,那么字段值将放到这个命名的相关数组中,字段名用做数组索引。

如果查询是SELECT语句并且没有给出 loop-body 脚本, 那么只有结果的头一行会存储到 Tcl 变量中;如果还有其他行的话, 将会被忽略。如果查询没有返回任何行, 那么不会存储什么数据(这个情况可以通过检查 spi_exec 的结果来判断)。 比如,

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

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

如果给出了可选的 loop-body 参数, 那么它就是一小段 Tcl 脚本,它会为 SELECT 结果中的每一行执行一次 (注意:如果给出的查询不是 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 写一个空列表。) 目前,参数类型必需和 pg_type 里显示的内部类型名一样; 比如是int4 而不是 integer

spi_prepare 的返回值是一个可以在随后的 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 哪些参数是 NULL。如果给出, 那么它必需和 value-list 的长度相同。 如果没有给出,那么所有参数值都是非 NULL。

除了查询及其参数声明的方式之外,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 的变量代换替换掉。

spi_lastoid

如果该查询是单行 INSERT 并且被修改的表包含 OID, 则返回最后的 spi_exec 或者 spi_execp 查询插入的行的 OID。(如果不是,你收到零。)

quote string

在给出的字串里将所由单引号和反斜杠字符复制成双份。 它可以用于安全地处理那些要输入到 spi_exec 或者 spi_prepare 中的 SQL 命令中的引号包围字串。 比如,假如一个 SQL 命令看起来象这样

"SELECT '$val' AS ret"

这里的 Tcl 变量val实际上包含 doesn't。 这样最后的命令字串会是这样

SELECT $q$doesn't$q$ 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 抛出一个错误条件:该行数进一步的执行将中止, 同时退出当前事务。 FATAL 退出当前事务并且导致当前会话关闭(可能在 PL/Tcl 函数里没有什么理由使用这个错误级别, 提供它主要是为了完整)。 某个优先级别的信息是报告给客户端还是写到服务器日志, 还是两个都做是由 log_min_messagesclient_min_messages 配置变量控制的。 参阅 Chapter 17 获取更多细节。