Jan Behrens <jbe-mlist@xxxxxxxxxxxxx> 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
