主页 / dba / 问题 / 295024
Accepted
bluk
Asked: 2021-06-30 18:55:03 +0800 CST

使用 USING 查看 EXECUTE 中的语句

  • 772

假设使用下面的函数，我需要调试EXECUTE语句中执行的内容。USING（现实中提供动态变量的长动态语句）

CREATE OR REPLACE FUNCTION fn() RETURNS integer LANGUAGE 'plpgsql' 
AS $$
BEGIN
    EXECUTE $x$
        SELECT $1, $2, $3
    $x$ USING 1, '2020-01-01'::date, NULL::date;
    
    RETURN 0;
END;
$$;

似乎在这种情况下，您能做的最好的事情就是取回字符串SELECT $1, $2, $3。有没有办法打印/提高SELECT 1, '2020-01-01'::date, NULL::date？
（我会想象类似的东西format('%1$s', 'xyz')？）

postgresql dynamic-sql

2 个回答

  1. Best Answer

    这比人们预期的要复杂得多。PL/pgSQL 去除EXECUTE注释并解析查询字符串，识别标记。在计划查询之前，只有实际$的 -parameters 会被子句中的值替换。USING不$n包含在注释、字符串或标识符（如-- $1 is required、'My text says $2'或"costs$3"）中。我们必须复制解析器的大部分功能才能保持准确。

    但我过去一直希望有同样的功能。所以这里是：

    Plainreplace()会被任何$n不是实际参数的情况所愚弄。这是一个穷人的实现，应该做得更好。它包括类型数据类型。（但它仍然远非完美！）

    首先是一个小辅助函数：Postgres 提供了几个函数来将字符串格式化为标识符、文字等：

    quote_ident()
quote_literal()
quote_nullable()

    我们需要这个：

    quote_nullable( anyelement) →text

    将给定值转换为文本，然后将其作为文字引用；或者，如果参数为 null，则返回NULL. 嵌入的单引号和反斜杠被适当地加倍。

    但是对原始数据类型进行了附加转换。所以我称之为quote_nullable_typed()：

    CREATE OR REPLACE FUNCTION quote_nullable_typed(anyelement)
  RETURNS text
  LANGUAGE sql PARALLEL SAFE IMMUTABLE AS
$func$
SELECT quote_nullable($1) || '::' || pg_typeof($1);
$func$;

    format()具有内联上述功能的格式说明符quote_*()：%I, %L. 显然，不适用于我们的自定义功能。因此，明确应用它并使用%s. 嵌套在另一个辅助函数中：

    CREATE OR REPLACE FUNCTION f_sql_string_with_params(_sql text, VARIADIC x text[])
  RETURNS text
  LANGUAGE sql PARALLEL SAFE IMMUTABLE AS
$func$
SELECT format(regexp_replace(replace(_sql, '%', '%%')
                           , '(?<=[^[:alnum:]_''"])\$(\d+)(?=[^[:alnum:]_''"])'
                           , '%\1$s'
                           , 'g'
                            )
            , x[1],x[2],x[3],x[4],x[5],x[6],x[7],x[8],x[9],x[10])  -- allow more?
$func$;

    该VARIADIC参数接受可变数量的参数。该函数迭代前 10 个（任意）。如果您需要更多，请展开列表。

    核心功能是：

    regexp_replace(replace(_sql, '%', '%%'), '(?<=[^[:alnum:]_''"])\$(\d+)(?=[^[:alnum:]_''"])', '%\1$s', 'g')

    加倍%准备format()。看：

    正则表达式模式(?<=[^[:alnum:]_''"])\$(\d+)(?=[^[:alnum:]_''"])解释：

    (?<=)... 正向后视
    [^[:alnum:]_''"]... 字符类，不包括单词字符和_'"
    \$... 文字$
    (\d+)... 捕获括号中的一个或多个数字（注意替换）
    (?=)... 正向前瞻

    因此，它仅$n在不在单词字符之间或引号旁边时替换。远非完美。但应涵盖典型案例。

    理想情况下，我们会先删除评论。谁有心情进一步改进它...

    应用于您的原始示例：

    CREATE OR REPLACE FUNCTION fn()
  RETURNS void
  LANGUAGE plpgsql AS
$func$
DECLARE
   _sql text := $q$SELECT $1, $2, $3, 'false $1', format('demo: %I', 'xX')$q$;  -- sql string, extended with potential hazards
   _v1  int  := 1;
   _v2  date := '2020-01-01';
   _v3  date;  -- defaults to NULL
BEGIN
   RAISE NOTICE E'Query string with $-parameters:\n%', _sql;

   RAISE NOTICE E'Query string with typed values:\n%'
              , f_sql_string_with_params(_sql
                                       , quote_nullable_typed(_v1)
                                       , quote_nullable_typed(_v2)
                                       , quote_nullable_typed(_v3)
                                        );
   
   EXECUTE _sql
   USING _v1, _v2, _v3;
END
$func$;

    第二个NOTICE将报告：

    SELECT '1'::integer, '2020-01-01'::date, NULL::date;

    db<>fiddle here - 带有扩展的测试用例

    • 2

  2. RAISE NOTICE应该做的伎俩：

    RAISE NOTICE '$1 = "%", $2 = "%", $3 = "%"', 1, '2020-01-01'::date, NULL::date;

    要插入查询中的值，您可以使用

    replace(
   replace(
      replace('SELECT $1, $2, $3', '$1', '1'),
      '$2', '''2020-01-01''::date'
   ),
   '$3', 'NULL::date'
)
    • 0

相关问题