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

Solution 1

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?

Solution 2

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