Hi,
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;
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;
Why does the non-working code return a
NULL value for the second column when the query itself returns two valid columns?
My second question is tangentially related: how do you use PREPARE inside of a stored procedure? I thought I could wrap the SELECT statement from the above two examples into a PREPARE and EXECUTE. However, since the PREPARE is session-based, I received errors about the PREPARE statement not existing, or already existing. The code was similar to:
-- Start of stored procedure.
BEGIN
PREPARE x_prepare( integer, integer, integer ) AS
SELECT
row_number() OVER (ORDER BY taken)::integer,
avg( amount )::double precision
FROM
x_function( $1, 25 ) ca,
x_table m
WHERE
m.category_id = 1 AND
m.station_id =
ca.id AND
extract( month from m.taken ) = $2 AND
extract( day from m.taken ) = $3
GROUP BY
m.taken
ORDER BY
m.taken;
FOR r, a IN EXECUTE x_prepare( 4, 1, 1 ) LOOP
-- Code
END LOOP;
END;
Thank you for any insights on these problems.
Dave
