Dynamic result sets from procedures

From: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Dynamic result sets from procedures
Date: 2017-10-31 21:08:47
Message-ID: 4580ff7b-d610-eaeb-e06f-4d686896b93b@2ndquadrant.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

This patch is more of a demo of what could be done, not my primary
focus, but if there is interest and some assistance, maybe we can make
something out of it. This patch also goes on top of "SQL procedures"
version 1.

The purpose is to return multiple result sets from a procedure. This
is, I think, a common request when coming from MS SQL and DB2. MS SQL
has a completely different procedure syntax, but this proposal is
compatible with DB2, which as usual was the model for the SQL standard.
So this is what it can do:

CREATE PROCEDURE pdrstest1()
LANGUAGE SQL
AS $$
DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM cp_test2;
DECLARE c2 CURSOR WITH RETURN FOR SELECT * FROM cp_test3;
$$;

CALL pdrstest1();

and that returns those two result sets to the client.

That's all it does for now. Things get more complex when you consider
nested calls. The SQL standard describes additional facilities how an
outer procedure can accept a called procedure's result sets, or not. In
the thread on transaction control, I mentioned that we might need some
kind of procedure call stack. Something like that would be needed here
as well. There are also probably some namespacing issues around the
cursors that need more investigation.

A more mundane issue is how we get psql to print multiple result sets.
I have included here a patch that does that, and you can see that new
result sets start popping up in the regression tests already. There is
also one need error that needs further investigation.

We need to think about how the \timing option should work in such
scenarios. Right now it does

start timer
run query
fetch result
stop timer
print result

If we had multiple result sets, the most natural flow would be

start timer
run query
while result sets
fetch result
print result
stop timer
print time

but that would include the printing time in the total time, which the
current code explicitly does not. We could also temporarily save the
result sets, like

start timer
run query
while result sets
fetch result
stop timer
foreach result set
print result

but that would have a lot more overhead, potentially.

Thoughts?

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment Content-Type Size
v1-0001-psql-Display-multiple-result-sets.patch text/plain 5.6 KB
v1-0002-Dynamic-result-sets-from-procedures.patch text/plain 13.9 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Pavel Stehule 2017-10-31 21:10:43 Re: proposal: schema variables
Previous Message Serge Rielau 2017-10-31 21:08:18 Re: proposal: schema variables