get multiple result set for a Procedure in PostgreSQL

Question

My requirement is that I want to create a generic function where I can pass any other function and its params and it should return appropriate output (i.e It may be a table result or single result etc.) and it should be with in single statement there.

This Is what I have searched and tried but I don't want to run any multiple statements.

CREATE FUNCTION CustomerWithOrdersByState() RETURNS SETOF refcursor AS $$
    DECLARE
      ref1 refcursor;           -- Declare cursor variables
      ref2 refcursor;                             
    BEGIN
      OPEN ref1 FOR  SELECT * FROM "table1" limit 10;
    RETURN NEXT ref1;                                                                              

    OPEN ref2 FOR   SELECT * FROM "table2" limit 10;
    RETURN NEXT ref2; 

    END;
    $$ LANGUAGE plpgsql;

==================================================================

    begin;
    select * from CustomerWithOrdersByState();
       FETCH ALL FROM "<unnamed portal 31>";
      -- FETCH ALL FROM "<unnamed portal 30>";
    commit;

I am using Postgres 11.4 version..

I think in pgAdmin you have no other choice than to run the fetch all from... part manually. — user330315
– user330315, Commented Jul 22, 2019 at 13:34
Hi Yes you are right but there is new feature has been added in Postgres 11 to create procedure, so is it also not possible by using procedures as we can achieve the same in SSMS ? — Ajay
– Ajay, Commented Jul 22, 2019 at 13:40
Postgres is not SQL Server - you can't blindly adopt best practices from one platform to a completely different platform. I have been working with Oracle for over 20 years and with Postgres for over 15 years now - I never had the urge to create a (generic) stored procedure that returns multiple arbitrary results (that I don't know in advance). What is the actual, underlying problem you are trying to solve with this? Procedures are not intended to return results - that's what functions are for. — user330315
– user330315, Commented Jul 22, 2019 at 13:42

Damien Sawyer · Accepted Answer · 2019-09-20 05:51:12Z

1

I've had what I believe is a similar issue where I wanted a way to execute a script with multiple result sets in a single batch.

As pointed out above, PGAdmin4 (and many other clients I've tried) only seem to process a single command at a time, meaning that you have to select a row, execute, select the next, execute... etc.

One quick way I found which appears to work is to save the script as a single file, then execute it on the CLI via PSQL.

So, for an example, I created a file called myscript.sql as follows:

DROP TABLE IF EXISTS sampledata;
CREATE TABLE if not exists sampledata as select x,1 as c2,2 as c3, md5(random()::text) from generate_series(1,5) x;
  CREATE OR REPLACE FUNCTION GET_RECORDS(ref refcursor) RETURNS REFCURSOR AS $$
    BEGIN
      OPEN ref FOR SELECT * FROM SAMPLEDATA;   -- OPEN A CURSOR
      RETURN ref;                           -- RETURN THE CURSOR TO THE CALLER
    END;
    $$ LANGUAGE PLPGSQL;



/*
In PGManage, you would need to execute this commands one at a time (ie, 4 times).
*/
BEGIN;
    SELECT get_records('r1');
    FETCH ALL IN "r1";
COMMIT;

I then created a bash script (runscript.sh) which allowed for easy execution of different files.

#!/bin/bash
# Can be used to execute scripts. 
# Like this   ./runfile.sh hello.sql
psql -U xuser -d postgres < "$1"

I set the script to be executable:

chmod a+x runscript.sh

And then execute as follows:

./runscript.sh myscript.sql

The script executes and I see the results in the CLI. I can iterate quickly on the file, save it and execute it in the shell.

answered Sep 20, 2019 at 5:51

Damien Sawyer

6,0677 gold badges49 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ajay Over a year ago

Thanks for your answer but what in the case where I want to return multiple select in single function..

Damien Sawyer Over a year ago

I could be wrong, but I believe that this set is four individual 'statements'. BEGIN; SELECT get_records('r1'); FETCH ALL IN "r1"; COMMIT; In PGAdmin, you can run these one at a time and it works. It's a hassle though as you have to highlight each line and hit f5. This way you can run them all in one batch. Much more like what I was used to in SQL SMSS (but I'm not going back to SQL! Postgres rocks!!)

Collectives™ on Stack Overflow

get multiple result set for a Procedure in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related