Skip site navigation (1) Skip section navigation (2)

Unexpected results: string vs. direct SQL

From: Thangalin <thangalin(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Unexpected results: string vs. direct SQL
Date: 2011-05-29 21:47:31
Message-ID: BANLkTi=b4FL+L_QjZ_cZwBQNhyTnDNLG=w@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

Responses

pgsql-novice by date

Next:From: Jean-Yves F. BarbierDate: 2011-05-30 19:29:22
Subject: strange SSL msg
Previous:From: James SmithDate: 2011-05-29 20:27:30
Subject: Create GEOM Column

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group