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 multiple

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, on

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 ti

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; > $$; > > CALL pdrstest1(); > > and t

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" > v

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, this is similar to the model alread