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

return query with set-returning functions

From: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: return query with set-returning functions
Date: 2008-08-11 22:27:34
Message-ID: 200808111827.34685.xzilla@users.sourceforge.net (view raw or flat)
Thread:
Lists: 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

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2008-08-12 01:30:39
Subject: Re: BUG #4351: Full text search performance
Previous:From: Lawrence CohanDate: 2008-08-11 21:28:22
Subject: BUG #4351: Full text search performance

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