PostgreSQl 函数返回多个动态结果集 [英] PostgreSQl function return multiple dynamic result sets

问题描述

我有一个旧的 MSSQL 过程需要移植到 PostgreSQL 函数.基本上，SQL 过程包含在选择语句上的 CURSOR 中.对于每个游标实体，我有三个基于当前游标输出的选择语句.

I have an old MSSQL procedure that needs to be ported to a PostgreSQL function. Basically the SQL procedure consist in a CURSOR over a select statement. For each cursor entity i have three select statements based on the current cursor output.

FETCH NEXT FROM @cursor INTO @entityId
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT * FROM table1 WHERE col1 = @entityId
SELECT * FROM table2 WHERE col2 = @entityId
SELECT * FROM table3 WHERE col3 = @entityId
END

SELECT 语句中的表具有不同的列.

The tables from the SELECT statements have different columns.

我知道 PostgreSQL 使用 refcursor 来返回多个结果集，但问题是是否可以在循环内打开并返回多个动态引用?

I know that the PostgreSQL use refcursor in order to return multiple result sets but the question is if is possible to open and return multiple dynamic refcursors inside of a loop?

Npgsql .NET 数据提供程序用于处理结果.

The Npgsql .NET data provider is used for handling the results.

循环内只有 1 个光标的 Postgres 测试代码:

CREATE OR REPLACE FUNCTION "TestCursor"(refcursor) 
RETURNS SETOF refcursor AS
$BODY$

DECLARE
    entity_id integer;  
BEGIN

    FOR entity_id IN SELECT "FolderID" from "Folder"
    LOOP
        OPEN $1 FOR SELECT * FROM "FolderInfo" WHERE "FolderID" = entity_id;
        RETURN NEXT $1;
        CLOSE $1;   
    END LOOP;
END;

$BODY$
LANGUAGE 'plpgsql' VOLATILE;

然后是测试代码:

BEGIN;
SELECT * FROM "TestCursor"('c');
FETCH ALL IN c;
COMMIT;

SELECT * FROM "TestCursor"('c'); 输出就像截图一样:然后当我尝试获取数据时出现错误:ERROR: cursor "c" does not exist

The SELECT * FROM "TestCursor"('c'); output is like on screenshot: Then when i try to fetch data i get the error: ERROR: cursor "c" does not exist

推荐答案

您可以通过 SETOF refcursor 模拟它.但这不是个好主意.这种 T-SQL 模式在 Postgres 中没有得到很好的支持，应该在可能的情况下被禁止.PostgreSQL 支持函数 - 函数可以返回标量、向量或关系.就这些.通常有 90% 可以重写 T-SQL 程序来清理 PostgreSQL 函数.

You can emulate it via SETOF refcursor. But it is not good idea. This T-SQL pattern is not supported well in Postgres, and should be prohibited when it is possible. PostgreSQL support functions - function can return scalar, vector or relation. That is all. Usually in 90% is possible to rewrite T-SQL procedures to clean PostgreSQL functions.

这篇关于PostgreSQl 函数返回多个动态结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！