On Wed, 10 Apr 2024 19:02:48 -0400 Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > 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. I thought multiple result sets are supported for commands like PQexec, where "Multiple queries sent in a single PQexec call" are explictly supported, and which then return multiple result set. This, however, doesn't apply to pipelining because PQexec is not available in pipelining mode. > > > 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. As it seems to be a corner case that rarely occurs in practice, I was considering to simply not support this case in my client library. I don't know which SQL error code I could return in that case though. Maybe "0A000" (feature_not_supported) or "21000" (cardinality_violation). Not sure if either of those is a good choice. Any better idea? > > > 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. To which question was "no" the answer to. I'm not sure if I understand. > > regards, tom lane > Regards, Jan Behrens
