FAQ
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

Search Discussions

  • Tom Lane at May 31, 2011 at 1:39 am

    Thangalin writes:
    The following code works as expected, returning two columns of data (a row
    number and a valid value):
    ...
    The following code does not work as expected; the first column is a row
    number, the second column is NULL.
    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.

    (BTW, PG 9.0 will throw an error by default when there's an ambiguity of
    this type.)
    My second question is tangentially related: how do you use PREPARE inside of
    a stored procedure?
    There is no need to use PREPARE, because every SQL query in plpgsql is
    effectively prepared automatically.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedMay 29, '11 at 9:47p
activeMay 31, '11 at 1:39a
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Thangalin: 1 post Tom Lane: 1 post

People

Translate

site design / logo © 2022 Grokbase