Postgres 11 procedures and result sets
Hello List,
I have a question regarding the new stored procedures in Postgres 11 (I tested
beta4):
I'd like to know if it is somehow possible to get a (or possibly more) result
set from selects within the SP, as it is possible in MariaDB, MySQL, or SQL
Server.
What I found until now is to use inout-Parms. Then I have to define all
columns in the select as inout-Params, but then I get only one line back,
regardless how many lines >0 have been selected.
I could also define an inout refcursor param, and parse that one, but that
would make the new SP identical to the well known functions returning a
refcursor. And when I use that approach, I have an additional step in Qt-Code
for example, since I have to exec the SP, then parse to the refcursor result,
exec the fetch and then parse the cursor output I'm interested in.
Did I miss something?
Thanks in advance!
--
MfG Jan
Jan Kohnert <nospam001-lists@jan-kohnert.de> writes:
I have a question regarding the new stored procedures in Postgres 11 (I tested
beta4):
I'd like to know if it is somehow possible to get a (or possibly more) result
set from selects within the SP, as it is possible in MariaDB, MySQL, or SQL
Server.
Not there as of v11, other than the refcursor approach you already know
about. We hope to have something nicer worked out for v12. There
are a lot of compatibility issues to sort through :-(
regards, tom lane
On Mon, Oct 1, 2018 at 6:57 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Jan Kohnert <nospam001-lists@jan-kohnert.de> writes:
I have a question regarding the new stored procedures in Postgres 11 (I tested
beta4):
I'd like to know if it is somehow possible to get a (or possibly more) result
set from selects within the SP, as it is possible in MariaDB, MySQL, or SQL
Server.Not there as of v11, other than the refcursor approach you already know
about. We hope to have something nicer worked out for v12. There
are a lot of compatibility issues to sort through :-(
There are a few other ways of dealing with this.
If the data being returned isn't very large, you can stuff multiple
'datasets' into a single json. I do this all the time today, with
functions. Yet another tactic is to create temp tables (maybe ON
COMMIT DROP) and refer to those tables after calling the procedure. I
would strongly consider this if the returned data was large and the
function/procedure was not called at a high rate (making system
catalog thrash in issue). I would probably use these tactics,
especially the json style return, even after multi-result style
invocation were to drop.
merlin