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

Re: return query with set-returning functions

From: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: return query with set-returning functions
Date: 2008-08-12 02:48:36
Message-ID: 200808112248.36182.xzilla@users.sourceforge.net (view raw or flat)
Thread:
Lists: pgsql-bugs
On Monday 11 August 2008 21:38:38 Tom Lane wrote:
> Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> writes:
> > 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,'inoc
> >tets','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
>
> Works here after loading your schema dump, or at least it doesn't
> complain:
>
> treat-#
> stratcon.fetch_dataset('cfe2aad7-71e5-400b-8418-a6d5834a0386'::uuid,'inocte
>ts','2008-08-04 treat'# 15:01:13-04','2008-08-11 15:01:13-04',500,'t');
>  sid | name | rollup_time | count_rows | avg_value
> -----+------+-------------+------------+-----------
> (0 rows)
>
> It's possible that I'm missing the failure for lack of suitable test
> data, but right offhand I'd bet that the problem is that there are
> dropped columns in your copy of stratcon.rollup_matrix_numeric_5m.
> plpgsql has some shortcomings in dealing with rowtypes that contain
> dropped columns ...
>

Ah, right, should have mentioned that.... I had suspected that too, and 
reloaded the functions after finding a couple, thinking that would have been 
enough, but I guess something more brute force is needed... do I have to 
drop/recreate the table, or is there something a bit more friendly that can 
be done. 

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

In response to

pgsql-bugs by date

Next:From: Matthew DennisDate: 2008-08-12 03:35:26
Subject: different results based solely on existence of index (no, seriously)
Previous:From: Tom LaneDate: 2008-08-12 02:39:11
Subject: Re: BUG #4350: 'select' acess given to views containing "union all" even though user has no grants

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