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.