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