意外的 SQL 结果：字符串与直接 SQL

Question

工作 SQL

以下代码按预期工作，返回两列数据（行号和有效值）：

sql_amounts := '
  SELECT
    row_number() OVER (ORDER BY taken)::integer,
    avg( amount )::double precision
  FROM
    x_function( '|| id || ', 25 ) ca,
    x_table m
  WHERE
    m.category_id = 1 AND
    m.location_id = ca.id AND
    extract( month from m.taken ) = 1 AND
    extract( day from m.taken ) = 1
  GROUP BY
    m.taken
  ORDER BY
    m.taken';

FOR r, amount IN EXECUTE sql_amounts LOOP
  SELECT array_append( v_row, r::integer ) INTO v_row;
  SELECT array_append( v_amount, amount::double precision ) INTO v_amount;
END LOOP;

非工作 SQL

以下代码不按预期工作；第一列是行号，第二列是NULL。

FOR r, amount IN
  SELECT
    row_number() OVER (ORDER BY taken)::integer,
    avg( amount )::double precision
  FROM
    x_function( id, 25 ) ca,
    x_table m
  WHERE
    m.category_id = 1 AND
    m.location_id = ca.id AND
    extract( month from m.taken ) = 1 AND
    extract( day from m.taken ) = 1
  GROUP BY
    m.taken
  ORDER BY
    m.taken
LOOP
  SELECT array_append( v_row, r::integer ) INTO v_row;
  SELECT array_append( v_amount, amount::double precision ) INTO v_amount;
END LOOP;

问题

当查询本身返回两个有效列时，为什么非工作代码会为第二列返回 NULL 值？ （这个问题主要是学术性的；如果有一种方法可以表达查询而不用将其包装在文本字符串中，那就太好了。）

完整代码

http://pastebin.com/hgV8f8gL

软件

PostgreSQL 8.4

谢谢。

Answer 1

这两种说法并不严格等同。

假设 id = 4，第一个在每次传递时都会计划/准备，其行为类似于：

prepare dyn_stmt as '... x_function( 4, 25 ) ...'; execute dyn_stmt;

另一个仅在第一遍时计划/准备，并且行为更像：（

prepare stc_stmt as '... x_function( $1, 25 ) ...'; execute stc_stmt(4);

循环实际上会使其为上面的内容准备一个游标，但这不是我们的重点。）

许多因素可以使两者产生不同的结果。

• 调用该过程之前的搜索路径更改将被第二次调用忽略。特别是如果这使得 x_table 指向不同的东西。
• 各种常量和对不可变函数的调用在第二次调用的计划中是“硬连线”的。

将此视为这些副作用的说明：

deallocate all;
begin;
prepare good as select now();
prepare bad as select current_timestamp;
execute good; -- yields the current timestamp
execute bad;  -- yields the current timestamp
commit;
execute good; -- yields the current timestamp
execute bad;  -- yields the timestamp at which it was prepared

为什么两者在您的情况下没有返回相同的结果取决于上下文（您只发布了 pl/pgsql 函数的一部分，因此很难说），但是我的我猜测您遇到了上述问题的变体。

Answer 2

来自汤姆·莱恩：

我认为问题在于您假设“amount”将引用查询的表列，而实际上它是 plpgsql 函数的局部变量。除非您使用表的名称/别名限定列引用，否则第二种解释将优先。

注意：当存在这种类型的歧义时，PG 9.0 将默认抛出错误。