postgresql - 如何从 PostgreSQL 存储过程中获取结果集？

Question

我在 PostgreSQL 11 中创建了一个存储过程来执行 CRUD 操作，它适用于 1. 创建 2. 更新 3. 删除，但是当我通过将 Condition = 4 传递给选择一个结果集，出现以下错误。

我已经使用 PostgreSQL 函数获取结果集，它对我有用，但我需要使用 PostgreSQL 存储过程获取结果。

这是我的存储过程代码:

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

Answer 1

大多数人会建议，在将 MS SQL Server 存储过程迁移到 PostgreSQL 时，如果该过程返回一组数据(行和列)，则将存储过程更改为函数，因为函数本质上返回集合数据。但是，从 Postgres 11 开始，您可以使用游标从 PostgreSQL 过程返回结果集，尽管迭代结果可能很乏味。

下面演示了如何使用 INOUT 游标从 PostgreSQL 过程返回一组数据:

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;
$$;

要使用，在事务中调用过程

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

PostgreSQL 还支持使用Begin/End:

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

示例结果来自 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 |
+---+---+---+--------+---------------------|

要更高级地处理结果，请使用 anonymous code block迭代结果

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
$$;

示例结果来自 DBeaver (查看服务器输出，使用 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

示例结果来自 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

一个过程也可以返回两个结果集，使用单独的 refcursor 参数:

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;
$$;

检索结果:

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

或者使用扩展的 anonymous code block查看结果

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
$$;

输出:

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)

另一种设计模式，特别是对于通常添加/更新数据但偶尔需要预览结果的过程，是使用 RAISE INFO 语句。例如:

    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);

示例用法(完整过程在 GitHub 上的 PNNL-Comp-Mass-Spec/DBSchema_PgSQL_DMS 存储库中):

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

示例结果:

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

输出窗口内容:

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