Re: [HACKERS] Dynamic result sets from procedures

2017-11-04 Thread Daniel Verite
Peter Eisentraut wrote: > > CREATE PROCEDURE test() > > LANGUAGE plpgsql > > AS $$ > > RETURN QUERY EXECUTE 'SELECT 1 AS col1, 2 AS col2'; > > END; > > $$; > > > > Or is that not possible or not desirable? > > RETURN means the execution ends there, so how would you return

Re: [HACKERS] Dynamic result sets from procedures

2017-11-03 Thread Peter Eisentraut
On 11/2/17 16:40, Daniel Verite wrote: > But instead of having procedures not return anything, > couldn't they return whatever resultset(s) they want to > ("no resultset" being just a particular case of "anything"), > so that we could leave out cursors and simply write: We could in general design

Re: [HACKERS] Dynamic result sets from procedures

2017-11-03 Thread Peter Eisentraut
On 11/1/17 22:40, Robert Haas wrote: > That seems like it is at least arguably a wire protocol break. Today, > if you send a string containing only one command, you will only get > one answer. The wire protocol already supports this. And the wire protocol doesn't really know about statements,

Re: [HACKERS] Dynamic result sets from procedures

2017-11-03 Thread Peter Eisentraut
On 11/1/17 06:23, Pavel Stehule wrote: > We need to think about how the \timing option should work in such > scenarios.  Right now it does > Has the total time sense  in this case? > > should not be total time related to any fetched result? The \timing option in psql measures from the

Re: [HACKERS] Dynamic result sets from procedures

2017-11-02 Thread Daniel Verite
Peter Eisentraut wrote: > 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. If

Re: [HACKERS] Dynamic result sets from procedures

2017-11-01 Thread Robert Haas
On Wed, Nov 1, 2017 at 2:38 AM, Peter Eisentraut wrote: > 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; >

Re: [HACKERS] Dynamic result sets from procedures

2017-11-01 Thread Pavel Stehule
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" >

Re: [HACKERS] Dynamic result sets from procedures

2017-10-31 Thread Craig Ringer
On 1 November 2017 at 05:08, Peter Eisentraut wrote: > 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(); FWIW,

[HACKERS] Dynamic result sets from procedures

2017-10-31 Thread Peter Eisentraut
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