How to get result set from PostgreSQL stored procedure?

Question

I created a stored procedure in PostgreSQL 11 to perform CRUD operation, and it works fine for 1. Create 2. Update 3. Delete, but while I run read command by passing Condition = 4 to select a result set, I get below error.

I have used PostgreSQL function to get result set it works for me, but I need to get result using the PostgreSQL stored procedure.

Here is my code for stored procedure:

CREATE OR REPLACE PROCEDURE public.testSpCrud(
    fnam text,
    lnam text,
    id integer,
    condition integer)
LANGUAGE 'plpgsql'

AS $BODY$
declare
    countOfDisc int; 
BEGIN
if condition=1 then

INSERT INTO public.employee(
    employeeid, lname, fname, securitylevel, employeepassword, hphonearea, hphone, cphonearea, cphone, street, city, state, zipcode, extzip, name, email, groomerid, type, commission, inactive, carrierid, notoallemployees, languageid, isdogwalker, ispetsitter, ismobilegroomer, ssma_timestamp)
    VALUES (4,  'Test', 'Test', 2, 2, 32, 32, 32, 32, 32, 32,32, 32, 32, 22, 22, 2, 2, 2, false, 223,true, 223, true, true, true, '2019-08-27');
end if;
 if condition =2 then
    delete from Employee where employeeid=id;
    end if;
     if condition =3 then
    update Employee set fname='Test' where employeeid=id;
    end if;
     if condition =4 then
         Select * from Employee;
    end if;
    END;
$BODY$;

ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function testspcrud(text,text,integer,integer) line 22 at SQL statement
SQL state: 42601

Why do you need a procedure instead of a function? Are you using transactions inside the procedure? The only way to get anything back from a procedure is to use INOUT variables. — Jeremy, Oct 22 '19 at 15:52
Actually I am moving my database from MS Sql to PostgreSQL and I have already created all of stored procedures in PostgreSQL so now I need to utilize them. And yes I am using all of my transactions inside Stored Procedures. — Nafees Sardar, Oct 22 '19 at 15:57
You want something like ```RETURN QUERY```, but this is just impossibe in procedures. You should switch to functions. Perhaps you can get the transactional behavior using ```BEGIN/EXCEPTION```. — Islingre, Oct 22 '19 at 19:58

Alchemistmatt · Answer 1 · 2020-02-15T18:24:01.053

Most people will suggest that when migrating MS SQL Server stored procedures to PostgreSQL, if the procedure returns a set of data (rows and columns), you change the stored procedure into a function, since functions, by nature, return sets of data. However, as of Postgres 11, you can return result sets from a PostgreSQL Procedure using cursors, though it can be tedious to iterate over the results.

The following demonstrates how to return a set of data from a PostgreSQL procedure using an INOUT cursor:

CREATE OR REPLACE PROCEDURE test_get_data_single(
    _itemID int, 
    INOUT _message text = '', 
    INOUT _result_one refcursor = 'rs_resultone',
    INOUT _returnCode text = '')
LANGUAGE plpgsql
AS
$$
BEGIN
    _message := 'Test message for item ' || COALESCE(_itemID, 0);
    _returnCode := '';

  open _result_one for 
    SELECT * 
    FROM (values (1,2,3, 'fruit', current_timestamp - INTERVAL '5 seconds'), 
                 (4,5,6, 'veggie', current_timestamp)) as t(a,b,c,d,e);

END;
$$;

To use, call the procedure within a transaction

BEGIN;
    CALL test_get_data_single(1);
    FETCH ALL FROM "rs_resultone";
COMMIT;

PostgreSQL also supports using Begin / End:

BEGIN;
    CALL test_get_data_single(2);
    FETCH ALL FROM "rs_resultone";
END;

Example results from DBeaver

+--------------------------+--------------+-------------+
|    _message              | _result_one  | _returncode |
+--------------------------+--------------+-------------+
| Test message for item 2  | rs_resultone |             |
+--------------------------+--------------+-------------+

+---+---+---+--------+---------------------|
| a | b | c | d      | e                   | 
+---+---+---+--------+---------------------|
| 1 | 2 | 3 | fruit  | 2020-02-15 10:12:09 | 
| 4 | 5 | 6 | veggie | 2020-02-15 10:12:09 |
+---+---+---+--------+---------------------|

For more advanced handling of the results, use an anonymous code block to iterate over the results

DO
$$
DECLARE
    _message text = '';
    _returnCode text = '';
    _result_one refcursor;
    _result_single refcursor;
    _currentRow record;
    _i int;
BEGIN

    CALL test_get_data_single(1, _message => _message, _result_one => _result_single, _returnCode => _returnCode);

    RAISE info 'Cursor _result_single from test_get_data_single: %', _result_single;

    _i := 0;
    WHILE TRUE
    Loop
        FETCH NEXT FROM _result_single INTO _currentRow;

        IF _currentRow IS NULL Then
            EXIT;
        END IF;

        _i := _i + 1;
        RAISE info '%, array: %', _i, _currentRow;
        RAISE info '%, values: %  %  %  %', _i, _currentRow.a, _currentRow.b, _currentRow.c, _currentRow.d;
    END LOOP;

End
$$;

Example results from DBeaver (look in the Server Output, opened with Ctrl+Shift+O):

Cursor _result_single from test_get_data_single: <unnamed portal 261>
1, array: (1,2,3,fruit,"2020-02-14 17:19:29.612822-08")
1, values: 1  2  3  fruit
2, array: (4,5,6,veggie,"2020-02-14 17:19:34.612822-08")
2, values: 4  5  6  veggie

Example results from psql:

INFO:  Cursor _result_single from test_get_data_single: <unnamed portal 4>
INFO:  1, array: (1,2,3,fruit,"2020-02-14 17:22:50.81671-08")
INFO:  1, values: 1  2  3  fruit
INFO:  2, array: (4,5,6,veggie,"2020-02-14 17:22:55.81671-08")
INFO:  2, values: 4  5  6  veggie

A procedure can also return two result sets, using separate refcursor arguments:

CREATE OR REPLACE PROCEDURE test_get_data(
    _itemID int, 
    INOUT _message TEXT = '', 
    INOUT _result_one refcursor = 'rs_resultone', 
    INOUT _result_two refcursor = 'rs_resulttwo', 
    INOUT _returnCode TEXT = '')
LANGUAGE plpgsql
AS
$$
BEGIN
    _message := 'Test message for item ' || COALESCE(_itemID, 0);
    _returnCode := '';

  open _result_one for 
    SELECT * 
    FROM (values (1,2,3, 'fruit', current_timestamp - INTERVAL '5 seconds'), (4,5,6, 'veggie', current_timestamp)) as t(a,b,c,d,e);

  open _result_two for 
    SELECT * 
    FROM (values ('one'), ('two'), ('three'), ('four')) as p(name);

END;
$$;

Retrieve results with:


BEGIN;
    CALL test_get_data(1);
    FETCH ALL FROM "rs_resultone";
    FETCH ALL FROM "rs_resulttwo";
END;

Or use an expanded anonymous code block for viewing the results

DO
$$
DECLARE
    _message text = '';
    _returnCode text = '';
    _result_one refcursor;
    _result_two refcursor;
    _result_single refcursor;
    _currentRow record;
    _i int;
BEGIN

    CALL test_get_data(1, _message => _message, _result_one => _result_one, _result_two => _result_two, _returnCode => _returnCode);

    RAISE info '%', _message;

    RAISE info '';
    RAISE info 'Cursor _result_one: %', _result_one;

    _i := 0;
    WHILE TRUE
    Loop
        FETCH NEXT FROM _result_one INTO _currentRow;

        IF _currentRow IS NULL Then
            EXIT;
        END IF;

        _i := _i + 1;
        RAISE info '%, array: %', _i, _currentRow;
        RAISE info '%, values: %  %  %  %', _i, _currentRow.a, _currentRow.b, _currentRow.c, _currentRow.d;

    END LOOP;

    RAISE info '';
    RAISE info 'Cursor _result_two: %', _result_two;

    _i := 0;
    WHILE TRUE
    Loop
        FETCH NEXT FROM _result_two INTO _currentRow;

        IF _currentRow IS NULL Then
            EXIT;
        END IF;

        _i := _i + 1;
       RAISE info '%: %', _i, _currentRow;

    END LOOP;
End
$$;

Output:

Test message for item 1

Cursor _result_one: <unnamed portal 263>
1, array: (1,2,3,fruit,"2020-02-14 17:25:06.528551-08")
1, values: 1  2  3  fruit
2, array: (4,5,6,veggie,"2020-02-14 17:25:11.528551-08")
2, values: 4  5  6  veggie

Cursor _result_two: <unnamed portal 264>
1: (one)
2: (two)
3: (three)
4: (four)

The alternative design pattern, especially for procedures that normally add/update data, but for where you occasionally want to preview results is to use RAISE INFO statements. For example:


    If _infoOnly <> 0 Then

        _infoHead := format('%-22s %-15s %-20s %-25s %-25s',
                            'State Change Preview',
                            'Parameter Name',
                            'Manager Name',
                            'Manager Type',
                            'Enabled (control_from_website=1)'
                        );

        RAISE INFO '%', _infoHead;

        FOR _previewData IN
            SELECT PV.value || ' --> ' || _newValue AS State_Change_Preview,
                   PT.param_name AS Parameter_Name,
                   M.mgr_name AS manager_name,
                   MT.mgr_type_name AS Manager_Type,
                   M.control_from_website
            FROM mc.t_param_value PV
                 INNER JOIN mc.t_param_type PT
                   ON PV.type_id = PT.param_id
                 INNER JOIN mc.t_mgrs M
                   ON PV.mgr_id = M.mgr_id
                 INNER JOIN mc.t_mgr_types MT
                   ON M.mgr_type_id = MT.mgr_type_id
                 INNER JOIN TmpManagerList U
                   ON M.mgr_name = U.manager_name
            WHERE PT.param_name = 'mgractive' AND
                  PV.value <> _newValue AND
                  MT.mgr_type_active > 0
        LOOP

            _infoData := format('%-22s %-15s %-20s %-25s %-25s',
                                    _previewData.State_Change_Preview,
                                    _previewData.Parameter_Name,
                                    _previewData.manager_name,
                                    _previewData.Manager_Type,
                                    _previewData.control_from_website
                            );

            RAISE INFO '%', _infoData;

        END LOOP;

        _message := format('Would set %s managers to %s; see the Output window for details',
                            _countToUpdate,
                            _activeStateDescription);

Example usage (complete procedure is in the PNNL-Comp-Mass-Spec/DBSchema_PgSQL_DMS repo on GitHub):

CALL mc.EnableDisableManagers(
    _enable => 1,
    _managerTypeID => 11,
    _managerNameList => 'Pub-80%',
    _infoOnly => 1,
    _includeDisabled => 0
);

Example results:

+-----------------------------------+-------------+
|    _message                       | _returnCode |
+-----------------------------------+-------------+
| Would set 8 managers to Active;   |             |
| see the Output window for details |             |
+-----------------------------------+-------------+

Output window contents:

State Change Preview   Parameter Name  Manager Name         Manager Type              Enabled (control_from_website=1)
False --> True         mgractive       Pub-80-1             Analysis Tool Manager     1                        
False --> True         mgractive       Pub-80-2             Analysis Tool Manager     1                        
False --> True         mgractive       Pub-80-3             Analysis Tool Manager     1                        
False --> True         mgractive       Pub-80-4             Analysis Tool Manager     1                        
False --> True         mgractive       Pub-80-5             Analysis Tool Manager     1                        
False --> True         mgractive       Pub-80-6             Analysis Tool Manager     1                        
False --> True         mgractive       Pub-80-7             Analysis Tool Manager     1                        
False --> True         mgractive       Pub-80-8             Analysis Tool Manager     1

See also Laurenz Albe's answer at https://stackoverflow.com/a/50941121/1179467 for how to iterate over the rows tracked by a cursor, without an anonymous code block. — Alchemistmatt, Feb 15 '20 at 05:51
You can use refcursors in **function** too, no need to use procedures. Moreover, you can return from function `setof refcursor` - table with names of opened cursors so reader will know them without hardcoding. — Evgeny Nozdrev, Oct 01 '21 at 13:28

Erwin Brandstetter · Accepted Answer · 2020-07-19T16:50:12.037

As of Postgres 13, returning from a PROCEDURE is still very limited. See:

How to return a value from a stored procedure (not function)?

Most likely, you fell for the widespread misnomer "stored procedure" and really want a FUNCTION instead, which can return a value, a row or a set according to its declaration.

In PostgreSQL, what is the difference between a “Stored Procedure” and other types of functions?
How to return result of a SELECT inside a function in PostgreSQL?

Would work like this:

CREATE OR REPLACE FUNCTION public.testSpCrud(
    fnam text,
    lnam text,
    id integer,
    condition integer)
  RETURNS SETOF Employee LANGUAGE plpgsql AS
$func$
BEGIN
   CASE condition
   WHEN 1 THEN
      INSERT INTO public.employee(
       employeeid, lname, fname, securitylevel, employeepassword, hphonearea, hphone, cphonearea, cphone, street, city, state, zipcode, extzip, name, email, groomerid, type, commission, inactive, carrierid, notoallemployees, languageid, isdogwalker, ispetsitter, ismobilegroomer, ssma_timestamp)
       VALUES (4,  'Test', 'Test', 2, 2, 32, 32, 32, 32, 32, 32,32, 32, 32, 22, 22, 2, 2, 2, false, 223,true, 223, true, true, true, '2019-08-27');

   WHEN 2 THEN
      DELETE FROM Employee WHERE employeeid=id;

   WHEN 3 THEN
      UPDATE Employee SET fname='Test' WHERE employeeid=id;

   WHEN 4 THEN
      RETURN QUERY
      SELECT * FROM Employee;

   ELSE
      RAISE EXCEPTION 'Unexpected condition value %!', condition;
   END CASE;
END
$func$;

Simplified with a CASE construct while being at it, and added an ELSE clause. Adapt to your needs.

Call with:

SELECT * FROM public.testSpCrud(...);

Aside: all variable names of a plpgsql block are visible inside nested SQL DML commands. A variable named id is a problem waiting to happen. I suggest a safer naming convention, and / or table-qualify all column names in DML statements. One popular naming convention is to prepend variable names with an underscore. Like: _id.

And consider legal, lower-case identifiers in SQL and PL/pgSQL.

Are PostgreSQL column names case-sensitive?

@Ervin Brandsetter thanks for your Answer it works for me, what in case if I need result form different tables in same function. How can I set return type? — Nafees Sardar, Oct 24 '19 at 13:26
@NafeesSardar: You can't return multiple distinct sets from a single function. You could return multiple [`refcursor`](https://www.postgresql.org/docs/current/plpgsql-cursors.html#id-1.8.8.9.6.9) or create multiple temp tables instead. https://stackoverflow.com/a/15675036/939860 (Then you might as well use a `procedure`.) But best avoid that pattern. https://stackoverflow.com/a/33495327/939860 In case all share the same schema, you could append to the same result set: https://stackoverflow.com/a/7748232/939860 — Erwin Brandstetter, Oct 24 '19 at 14:18
@ErwinBrandstetter how to fetch data from a dynamic table? the table name is passed as a parameter. — Prashant Girase, Dec 30 '22 at 13:08

How to get result set from PostgreSQL stored procedure?

2 Answers2

Linked