Re: return setof record from function with dynamic query

On Wed, 18 Dec 2002, Toby Tremayne wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: MD5
>
> Hi all,
>
> I've been beating my head against the desk for days on this, and
> I've been completely unable to find docs on it. I have a function
> (code below) that creates and executes a dynamic query. I want to
> pass the results of that query as a recordset to the caller - I can
> do it as a refcursor (but via odbc a refcursor just appears as an
> empty recordset, no use at all.), but when I use the function code
> below I get the error:
>
> ERROR: A column definition list is required for functions returning RECORD

When you use the function, you need to say something like:
select * from func() as foo(col1 int, col2 varchar, ...);

Since it's an arbitrary record type, it doesn't know what the types are,
so you need to provide it at select time.