Re: return query with set-returning functions

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