Re: functions returning records

Hannu Krosing <hannu(at)tm(dot)ee> writes:
> Alex Pilosov wrote:
>> On Tue, 26 Jun 2001, Reinoud van Leeuwen wrote:
> 1: will it be possible to return multiple result sets? (in Sybase any
> select statement that is not redirected to variables or a table goes
> to the client, so it is quite common to do multiple selects). Does the
> postgresql client library support this?

>> No, libpq protocol cannot support that. This is really a sybasism, as good
>> as it is, no other database supports anything like that.

> IIRC the _protocol_ should support it all right, but the current libpq
> implementation does not (and the sql queries in functions are not sent
> to client either)

Actually, libpq supports it just fine too, but most clients don't.
You have to use PQsendQuery() and a PQgetResult() loop to deal with
multiple resultsets out of one query. It is possible to see this
happening even today:

PQsendQuery(conn, "SELECT * FROM foo ; SELECT * FROM bar");

while ((res = PQgetResult(conn)))
{
...

Whether it would be a *good idea* to allow standalone SELECTs in
functions to be handled that way is another question. I've got strong
doubts about it. The main problem is that the function call would be
nested inside another SELECT, which means you'd have the problem of
suspending a resultset transmission already in progress. That's *not*
in the protocol, much less libpq, and you wouldn't really want clients
forced to buffer incomplete resultsets anyway. But it could be
supported in procedures (not functions) that are called by some kind of
PERFORM statement, so that there's not a SELECT already in progress when
they are invoked.

regards, tom lane