On 01.02.22 15:40, Peter Eisentraut wrote: > On 12.01.22 11:20, Julien Rouhaud wrote: >> Since you mentioned that this patch depends on the SHOW_ALL_RESULTS >> psql patch >> which is still being worked on, I'm not expecting much activity here >> until the >> prerequirements are done. It also seems better to mark this patch as >> Waiting >> on Author as further reviews are probably not really needed for now. > > Well, a review on the general architecture and approach would have been > useful. But I understand that without the psql work, it's difficult for > a reviewer to even get started on this patch. It's also similarly > difficult for me to keep updating it. So I'll set it to Returned with > feedback for now and take it off the table. I want to get back to it > when the prerequisites are more settled.
Now that the psql support for multiple result sets exists, I want to revive this patch. It's the same as the last posted version, except now it doesn't require any psql changes or any weird test modifications anymore.
(Old news: This patch allows declaring a cursor WITH RETURN in a procedure to make the cursor's data be returned as a result of the CALL invocation. The procedure needs to be declared with the DYNAMIC RESULT SETS attribute.)
I did a quick test of this patch, and it is working pretty well.
I have two ideas.
1. there can be possibility to set "dynamic result sets" to unknown. The behaviour of the "dynamic result sets" option is a little bit confusing. I expect the number of result sets should be exactly the same as this number. But the warning is raised only when this number is acrossed. For this implementation the correct name should be like "max dynamic result sets" or some like this. At this moment, I see this feature "dynamic result sets" more confusing, and because the effect is just a warning, then I don't see a strong benefit. I can see some benefit if I can declare so CALL will be without dynamic result sets, or with exact number of dynamic result sets or with unknown number of dynamic result sets. And if the result is not expected, then an exception should be raised (not warning).
2. Unfortunately, it doesn't work nicely with pagers. It starts a pager for one result, and waits for the end, and starts pager for the second result, and waits for the end. There is not a possibility to see all results at one time. The current behavior is correct, but I don't think it is user friendly. I think I can teach pspg to support multiple documents. But I need a more robust protocol and some separators - minimally an empty line (but some ascii control char can be safer). As second step we can introduce new psql option like PSQL_MULTI_PAGER, that can be used when possible result sets is higher than 1
