Jan Behrens <jbe-mlist@magnetkern.de> writes:
> While writing a PostgreSQL client library for Lua supporting
> Pipelining (using PQsendQueryParams), I have been wondering if there
> are any single SQL commands that return multiple result sets.
Right now, I don't think so. I believe the current protocol design
intends to support that, and I think this may trace back to some
ancient idea at Berkeley that if you select from an inheritance
hierarchy where the child tables aren't all alike, you should be
able to see all the child data, which'd require changing tuple
descriptors midstream. But our current interpretation of SQL
SELECT forbids that.
> Here, "DELETE FROM magic" returns multiple result sets, even though it
> is only a single SQL statement.
Right, so it's kind of a case that you have to support. We're not
likely to rip out rules anytime soon, even if they're a bit
deprecated.
> The case outlined above seems to be a somewhat special case. I haven't
> found any other way to return multiple results (other than sending
> several semicolon-separated statements, which is not supported by
> PQsendQueryParams). So is there any (other) case where I reasonably
> should expect several result sets returned by PQgetResult (before
> PQgetResult returns NULL)? Wouldn't it make sense to disallow such
> behavior altogether?
No. For one thing, there's too much overlap between what you're
suggesting and pipelined queries.
> And if not, why can't I write a stored procedure
> or function that returns multiple result sets?
[ shrug... ] Lack of round tuits, perhaps. We don't have any
mechanism today whereby a stored procedure could say "please ship
this resultset off to the client, but I want to continue afterwards".
But you can do that in other RDBMSes and probably somebody will be
motivated to make it possible in Postgres.
regards, tom lane