Re: Returning records from function

There are two ways to do it:

a) using cursors, see thread at
http://fts.postgresql.org/db/mw/msg.html?mid=121140

Its non-transparent for the client (you need to know to use fetch, and you
cannot join such a cursor to anything...), but easy to do.

b) using views, see thread at
http://fts.postgresql.org/db/mw/msg.html?mid=120239

Its easy to create a function that returns a single value. Its possible to
create a function that returns a setof of scalar types (see above thread).

Its possible to create a function that returns a single record, but you
can't do much with it other than extract things from it using plpgsql
field-by-field.

But its a _REAL_ pain in the neck to create a function that returns a
setof of nonscalar types. You should look at how dblink is currently
implemented in contrib/dblink.

In short: you will end up having two functions, one will return a pointer
to a record, second one will extract specific fields from that record.

I'm not even sure if you can do that entirely with plpgsql, without
resorting to writing second function in C, like dblink.  Look at dblink
for inspiration.

But the whole thing is mostly broken, as in
http://fts.postgresql.org/db/mw/msg.html?mid=121920
also
http://fts.postgresql.org/db/mw/msg.html?mid=30392

To do it in a clean way, you need to wait for Tom Lane or someone else to
do the function-as-table-source code :)


On Tue, 26 Jun 2001 ryan.a.roemmich@mail.sprint.com wrote:

> I'm moving to PostgreSQL from MS SQL Server.  In MSSQL I usually create
> stored procedures to return result sets based on the passed arguments.
> I guess my question is:  Is it possible to return multi-row data from
> the PL/PGSQL functions of Postgres?  It's rather easy to just return
> one field for a particular row.  I've played with the RECORD datatype
> but it seems you can't return it from a function.
>
> TIA,
> Ryan Roemmich
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>