大多数人建议，当将 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 |
+
要更高级地处理结果，可以使用匿名代码块来迭代结果。
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;
或者使用扩展的匿名代码块来查看结果。
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
RETURN QUERY的东西，但在存储过程中这是不可能的。你应该转换为函数。也许你可以使用BEGIN/EXCEPTION来获得事务行为。 - Islingre