Re: Returning arbitrary row sets from a function

Gerard Mason wrote:
> how do I declare return types that are a SETOF rows containing
> columns from an arbitrary query?

Declare the function to return "setof record" and then specify the
rowtype at runtime. See:
http://techdocs.postgresql.org/guides/SetReturningFunctions
for a good tutorial.

> For example, suppose I want a function that returns a
> display-formatted organisation, by joining with countries so that the
> country appears as a name rather than an integer. What is currently
> happening is that the client is sending the query:
>
> SELECT o.name, c.name AS country, o.phone, o.mobile, o.fax, o.email,
>  o.website FROM organisations o, countries c WHERE c.id = o.country
>
> What I'd *like* to happen is that the client calls, say,
> get_org_long() and retrieves the same data. But what would the return
> type be? The only way I can think to do it at the moment would be to
> create a view and return a setof that view's rowtype, but that seems
> to be using a sledgehammer to crack a nut.

Why? At some point you have to let the planner/optimizer know what the
data types are. There's no way around it. If you don't want to use a
view, see CREATE TYPE (which is more appropriate anyway):
http://www.postgresql.org/docs/current/static/sql-createtype.html

In any case, why not just use a view -- it doesn't sound like you need a
function, at least not from your example.


> Also my first attempt at defining a test function that just returns
> rows from the organisations table fails with the message:
>
> ERROR:  parser: parse error at or near "%"
>
> It looks like this:
>
> CREATE FUNCTION gems_test() RETURNS SETOF organisations%ROWTYPE AS

I can't see where you'd get that impression.

> That seems to be what the documentation is suggesting the return type
> should be (the examples are very incomplete!), but it doesn't work
> (7.1.3, and I can't upgrade without buying a new machine, which I
    ^^^^^

That seems to be your problem. You need to be on 7.3 at least.

Joe