Unexpected SQL results: string vs. direct SQL

Working SQL

The following code works as expected, returning two columns of data (a row number and a valid value):

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;

Non-Working SQL

The following code does not work as expected; the first column is a row number, the second column is 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;

Question

Why does the non-working code return a NULL value for the second column when the query itself returns two valid columns? (This question is mostly academic; if there is a way to express the query without resorting to wrapping it in a text string, that would be great to know.)

开发者_如何学CFull Code

http://pastebin.com/hgV8f8gL

Software

PostgreSQL 8.4

Thank you.

---

The two statements aren't strictly equivalent.

Assuming id = 4, the first one gets planned/prepared on each pass, and behaves like:

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

The other gets planned/prepared on the first pass only, and behaves more like:

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

(The loop will actually make it prepare a cursor for the above, but that's besides the point for our sake.)

A number of factors can make the two yield different results.

• Search path changes before calling the procedure will be ignored by the second call. In particular if this makes x_table point to something different.
• Constants of all kinds and calls to immutable functions are "hard-wired" in the second call's plan.

Consider this as an illustration of these side-effects:

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

Why the two aren't returning the same results in your case would depend on the context (you only posted part of your pl/pgsql function, so it's hard to tell), but my guess is you're running into a variation of the above kind of problem.

---

From Tom Lane:

I think the problem is that you're assuming "amount" will refer to a table column of the query, when actually it's a local variable of the plpgsql function. The second interpretation will take precedence unless you qualify the column reference with the table's name/alias.

Note: PG 9.0 will throw an error by default when there is an ambiguity of this type.