Postgresql 中文操作指南

44.5. Database Access from 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 脚本片段，针对查询结果中的每一行执行一次。( loop-body 如果给定的命令不是 SELECT ，则会忽略。) 当前行的列值在每次迭代前存储到 Tcl 变量或数组元素中。例如：

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

  • 将为 pg_class 的每一行打印一个日志消息。此特性与其他 Tcl 循环构造的工作方式类似；特别是在 continue 和 break 在循环体内以通常方式工作。

  • 如果查询结果的列为空值，则它的目标变量将“取消设置”，而不是被设置。

    • spi_prepare query typelist

  • 准备并保存查询计划，以便稍后执行。已保存的计划将保留在当前会话的期限内。

  • 该查询可以使用参数，即用于在实际执行计划时提供值的占位符。在查询字符串中，按符号 $1 …​ $_n_ 引用参数。如果查询使用参数，则必须以 Tcl 列表的形式给出参数类型名称。(如果未使用参数，则为 typelist 编写一个空 list。)

  • spi_prepare 的返回值是将在对 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 哪些参数为 null 值。如果给出，它必须与 value-list 的长度完全相同。如果没有给出，则所有参数值都是非空值。

  • 除了指定查询及其参数的方式之外，spi_execp 的工作方式与 spi_exec 完全相同。-count、-array 和 loop-body 选项相同，结果值也是如此。

  • voici un exemple d’une fonction PL/Tcl utilisant un plan préparé :

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;

  • nous avons besoin de barres obliques inverses à l’intérieur de la chaîne de requête fournie à spi_prepare pour garantir que les marqueurs $_n_ seront transmis à spi_prepare en l’état, et non remplacés par une substitution de variable Tcl.

    • subtransaction command

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

    • quote string

  • 在给定的字符串中对所有单引号和反斜杠字符出现的位置进行加倍。这可用于安全地引用要插入到给定 spi_exec 或 spi_prepare 的 SQL 命令中的字符串。例如，考虑如下 SQL 命令字符串：

"SELECT '$val' AS ret"

  • là où la variable Tcl val contient en fait doesn&#8217;t . Cela produirait la chaîne de commande finale :

SELECT 'doesn't' AS ret

  • 会导致在 spi_exec 或 spi_prepare 期间发生解析错误。若要正确运行，提交的命令应包含：

SELECT 'doesn''t' AS ret

  • 这可以使用以下命令在 PL/Tcl 中形成：

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

  • l’un des avantages de spi_execp est que vous n’avez pas besoin de guillemeter les valeurs des paramètres comme cela, car les paramètres ne sont jamais analysés en tant que partie d’une chaîne de commande SQL.

    • elog level msg

  • émet un message de journal ou d’erreur. les niveaux possibles sont DEBUG , LOG , INFO , NOTICE , WARNING , ERROR et FATAL . ERROR déclenche une condition d’erreur ; si cela n’est pas intercepté par le code Tcl environnant, l’erreur se propage vers la requête appelante, entraînant l’annulation de la transaction ou de la sous-transaction actuelle. Cela revient effectivement à la commande Tcl error . FATAL annule la transaction et provoque l’arrêt de la session actuelle. (il n’y a probablement aucune bonne raison d’utiliser ce niveau d’erreur dans les fonctions PL/Tcl, mais il est fourni pour être complet.) Les autres niveaux ne génèrent que des messages de différents niveaux de priorité. Si les messages d’une priorité particulière sont signalés au client, écrits dans le journal du serveur ou les deux est contrôlé par les variables de configuration log_min_messages et client_min_messages . Voir Chapter 20 et Section 44.8 pour plus d’informations.