return query with set-returning functions - Mailing list pgsql-bugs

we might have found a bug in postgres... first draft of bug report looks like
so

using 8.3.3, i have the following 2 plpgsql functions

reconnoiter=# \df stratcon.fetch_dataset
                                                                    List of
functions
  Schema  |     Name      |            Result data type             |
Argument data types

----------+---------------+-----------------------------------------+-------------------------------------------------------------------------------------
 stratcon | fetch_dataset | setof stratcon.rollup_matrix_numeric_5m | integer,
text, timestamp with time zone, timestamp with time zone, integer, boolean
 stratcon | fetch_dataset | setof stratcon.rollup_matrix_numeric_5m | uuid,
text, timestamp with time zone, timestamp with time zone, integer, boolean


the second function takes a uuid, looks up an integer representation, and then
calls the first function. whenever I run it, I get the following error:


reconnoiter=# select * from
stratcon.fetch_dataset('cfe2aad7-71e5-400b-8418-a6d5834a0386'::uuid,'inoctets','2008-08-04
15:01:13-04','2008-08-11 15:01:13-04',500,'t');
ERROR:  structure of query does not match function result type
CONTEXT:  PL/pgSQL function "fetch_dataset" line 9 at RETURN QUERY


if i run the first function manually though, that function runs fine. looking
at pg_proc, the return types seem like they should be fine (same type, is a
set)


reconnoiter=# select proname, proargtypes, proretset, prorettype from pg_proc
where proname = 'fetch_dataset';
    proname    |       proargtypes       | proretset | prorettype
---------------+-------------------------+-----------+------------
 fetch_dataset |   23 25 1184 1184 23 16 | t         |      16905
 fetch_dataset | 2950 25 1184 1184 23 16 | t         |      16905


i even made a modified version to make sure the return type would match up
with the datatype:

CREATE or replace FUNCTION stratcon.fetch_dataset(in_uuid uuid, in_name text,
in_start_time timestamp with time zone, in_end_time timestamp with time zone,
in_hopeful_nperiods integer, derive boolean) RETURNS SETOF
stratcon.rollup_matrix_numeric_5m
    AS $$
declare
  v_sid int;
  v_record stratcon.rollup_matrix_numeric_5m%rowtype;
begin
  select sid into v_sid from stratcon.map_uuid_to_sid where id = in_uuid;
  if not found then
    return;
  end if;

    for v_record in  select sid, name, rollup_time, count_rows, avg_value from
stratcon.fetch_dataset(v_sid::integer, in_name, in_start_time, in_end_time,
in_hopeful_nperiods, derive) loop
    return next v_record;
    end loop;

---  return query select sid, name, rollup_time, count_rows, avg_value from
stratcon.fetch_dataset(v_sid::integer, in_name, in_start_time, in_end_time,
in_hopeful_nperiods, derive);
  return;
end
$$
    LANGUAGE plpgsql;


in this case, the loop version works fine, even though I get an error with
return query. is there some limitation with return query and set returning
functions, or is this just a bug?

btw, table looks like this:
reconnoiter=# \d stratcon.rollup_matrix_numeric_5m
     Table "stratcon.rollup_matrix_numeric_5m"
   Column    |           Type           | Modifiers
-------------+--------------------------+-----------
 sid         | integer                  | not null
 name        | text                     | not null
 rollup_time | timestamp with time zone | not null
 count_rows  | integer                  |
 avg_value   | numeric                  |
Indexes:
    "rollup_matrix_numeric_5m_pkey" PRIMARY KEY, btree (rollup_time, sid,
name) CLUSTER

the full code for the int version of the function can be found at
https://labs.omniti.com/trac/reconnoiter/browser/trunk/sql/reconnoiter_ddl_dump.sql#L402

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL