public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Jan Behrens <[email protected]>
Cc: [email protected]
Subject: Re: (When) can a single SQL statement return multiple result sets?
Date: Wed, 10 Apr 2024 19:02:48 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

Jan Behrens <[email protected]> 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

view thread (8+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: (When) can a single SQL statement return multiple result sets?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox