postgresql - 意外的 SQL 结果 : string vs. 直接 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 函数的一部分，所以很难说)，但我猜你遇到了上述问题的变体。