Re: returning multiple result sets from a stored procedure

On 9/5/2010 2:05 PM, Heikki Linnakangas wrote:
> On 04/09/10 17:16, Merlin Moncure wrote:
>> Curious: is mulitset handling as you see it supported by the current
>> v3 protocol?
>
> The manual says:
>
>> The response to a SELECT query (or other queries that return row sets, such as
>> EXPLAIN or SHOW) normally consists of RowDescription, zero or more DataRow
>> messages, and then CommandComplete. COPY to or from the frontend invokes
>> special protocol as described in Section 46.2.5. All other query types
>> normally produce only a CommandComplete message.
>>
>> Since a query string could contain several queries (separated by semicolons),
>> there might be several such response sequences before the backend finishes
>> processing the query string. ReadyForQuery is issued when the entire string
>> has been processed and the backend is ready to accept a new query string.
>
> If a multiple return sets from a procedure are returned just like multiple
> return sets from multiple queries, that's already covered by the protocol.
>

Just as a side note, libpqtypes can emulate this using composite arrays; a
feature we abuse internally. It is actually the primary justification we had
for developing that portion of libpqtypes; initially we stayed clear of arrays
and composites.

create table fork_t (fork_id, rev_id, size, block_ids int8[], ...)
create table rev_t (rev_id, blah, blah, fork_t[]);

/* this is my favorite part of libpqtypes */
PGarray arr;
PQgetf(result, tup_num, "%rev_t[]", field_num, &arr);

Now loop the array "arr" and getf(arr.res) for each rev_t, which allows you to
getf each fork_t in the fork_t[], etc....

I *know* it is not pure multiset'n, but it sure gets the job done (in a
completely different way, I know). However, I'm sure those reading this list
can see the possiblities ;)

Andrew Chernow
eSilo, LLC.