Thread: 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