2017-10-31 22:08 GMT+01:00 Peter Eisentraut < peter.eisentr...@2ndquadrant.com>:
> 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? > Has the total time sense in this case? should not be total time related to any fetched result? Regards Pavel > -- > Peter Eisentraut http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > >
