PostgreSQL函数报错：无法将SELECT查询作为游标打开 求解决方案

嘿，我来帮你搞定这个问题！你遇到的SSQL Error [42P11]: ERROR: cannot open SELECT query as cursor错误，根源是**RETURN QUERY和INTO子句不能一起用**——RETURN QUERY是用来直接向函数调用者返回结果集的，而INTO是用来把查询结果存储到变量或表中的，这俩放在一起就会触发这个光标错误。

另外，你直接在函数里创建普通表的做法也不太稳妥，容易引发并发冲突，最好改用临时表（会话结束后自动销毁，不会污染数据库）来存储中间结果。

下面是修正后的完整函数代码，我已经帮你调整了关键部分：

CREATE OR REPLACE FUNCTION claims_rez_ram(calc_date date) 
RETURNS TABLE (
    Serie varchar(50),
    Numar varchar(50),
    Claim_File_Index integer,
    Claim_File varchar(30),
    Claim_Date date,
    Claim_Notif_Date date,
    Rez_Const_Lei double precision,
    Plata_Lei double precision,
    Rez_Ram double precision
) as $func$ 
BEGIN
    -- 创建临时表存储支付数据（替代原来的into子句）
    CREATE TEMP TABLE IF NOT EXISTS claims_paid AS
    SELECT 
        p."PSN",
        p."PNR",
        d_p."Index" as claim_file_index,
        d_p."DAMFileNr" as claim_file,
        d_p."DAMDate" as claim_date,
        d_p."DAMAvizDate" as claim_notif_date,
        ROUND(CAST(SUM(CASE 
            WHEN d_pay."EID"='10' THEN d_pay."PAYVal" 
            ELSE d_pay."PAYVal"*exch_p."EXCValue" 
        end) as NUMERIC),2) as plata_LEI,
        pr."Index" as index_pr,
        r."Index" as index_risk
    FROM "DAM" as d_p
    JOIN "DAMPay" as d_pay on d_p."Index"=d_pay."DID"
    JOIN "Pols" as p on p."Index" = d_p."PID"
    JOIN "PolsRisc" as pr on d_pay."PRID" = pr."Index"
    JOIN "Riscs" as r on r."Index" = pr."RID"
    JOIN "EXCValues" exch_p ON exch_p."EID"=d_pay."EID"
    WHERE 
        (d_pay."EID"='10' or exch_p."AtDate"=calc_date) 
        AND d_pay."PAYDate" <= calc_date
    GROUP BY 
        p."PSN", p."PNR", d_p."Index", d_p."DAMFileNr", 
        d_p."DAMDate", d_p."DAMAvizDate", pr."Index", r."Index";

    -- 创建临时表存储准备金数据
    CREATE TEMP TABLE IF NOT EXISTS claims_rez_const AS
    SELECT 
        p."PSN",
        p."PNR",
        d_r."Index" as claim_file_index,
        d_r."DAMFileNr" as claim_file,
        d_r."DAMDate" as claim_date,
        d_r."DAMAvizDate" as claim_notif_date,
        ROUND(CAST(SUM(CASE 
            WHEN d_rez."EID"='10' THEN d_rez."RDAM" 
            ELSE d_rez."RDAM"*exch_r."EXCValue" 
        end) as NUMERIC),2) as rez_const_LEI,
        pr."Index" as index_pr,
        r."Index" as index_risk
    FROM "DAM" as d_r
    JOIN "DAMRez" as d_rez on d_r."Index"=d_rez."DID"
    JOIN "Pols" as p on p."Index" = d_r."PID"
    JOIN "PolsRisc" as pr on d_rez."PRID" = pr."Index"
    JOIN "Riscs" as r on r."Index" = pr."RID"
    JOIN "EXCValues" exch_r ON exch_r."EID"=d_rez."EID"
    WHERE 
        (d_rez."EID"='10' or exch_r."AtDate"=calc_date) 
        AND d_rez."RDAMDate" <= calc_date
        AND (d_r."IsClass" = 'false' or (d_r."IsClass" = 'true' and d_r."ClassDate" >=(calc_date + interval '1 day')))
        AND (d_r."IsClosed" = 'false' or (d_r."IsClosed" = 'true' and d_r."ClosedDate" >=(calc_date + interval '1 day')))
    GROUP BY 
        p."PSN", p."PNR", d_r."Index", d_r."DAMFileNr", 
        d_r."DAMDate", d_r."DAMAvizDate", pr."Index", r."Index";

    -- 返回最终合并后的结果
    RETURN QUERY
    SELECT 
        r_c."PSN",
        r_c."PNR",
        r_c."claim_file_index",
        r_c."claim_file",
        r_c."claim_date",
        r_c."claim_notif_date",
        r_c."rez_const_LEI" as Rez_Const_Lei,
        COALESCE(r_p."plata_LEI",0) as Plata_Lei,
        COALESCE(r_c."rez_const_LEI" - COALESCE(r_p."plata_LEI",0),0) as Rez_Ram
    FROM claims_rez_const r_c
    LEFT JOIN claims_paid r_p on r_c."claim_file_index"=r_p."claim_file_index"
    WHERE COALESCE(r_c."rez_const_LEI" - COALESCE(r_p."plata_LEI",0),0) != 0;

    -- 可选：如果需要多次调用函数，可以手动清理临时表，不过临时表会自动随会话销毁
    -- DROP TABLE IF EXISTS claims_paid;
    -- DROP TABLE IF EXISTS claims_rez_const;
END 
$func$ LANGUAGE plpgsql;

关键修改点说明： #

1. 替换RETURN QUERY ... INTO为临时表：用CREATE TEMP TABLE ... AS SELECT来存储中间结果，这是PL/pgSQL中处理中间数据集的正确方式，避免了光标错误。
2. 使用TEMP TABLE：临时表只在当前会话有效，会话结束后自动删除，不会和其他会话的表冲突，也不会留下冗余数据。
3. 修正结果字段名匹配：确保最终查询的字段名和函数RETURNS TABLE定义的字段名完全对应，避免返回结构不匹配的问题。

额外优化建议： #

如果你不想创建临时表，还可以用**CTE（公共表表达式）**把三个查询合并成一个，这样更高效，不需要中间表：

CREATE OR REPLACE FUNCTION claims_rez_ram(calc_date date) 
RETURNS TABLE (
    Serie varchar(50),
    Numar varchar(50),
    Claim_File_Index integer,
    Claim_File varchar(30),
    Claim_Date date,
    Claim_Notif_Date date,
    Rez_Const_Lei double precision,
    Plata_Lei double precision,
    Rez_Ram double precision
) as $func$ 
BEGIN
    RETURN QUERY
    WITH claims_paid AS (
        -- 第一个查询的内容
        SELECT 
            p."PSN",
            p."PNR",
            d_p."Index" as claim_file_index,
            d_p."DAMFileNr" as claim_file,
            d_p."DAMDate" as claim_date,
            d_p."DAMAvizDate" as claim_notif_date,
            ROUND(CAST(SUM(CASE 
                WHEN d_pay."EID"='10' THEN d_pay."PAYVal" 
                ELSE d_pay."PAYVal"*exch_p."EXCValue" 
            end) as NUMERIC),2) as plata_LEI
        FROM "DAM" as d_p
        JOIN "DAMPay" as d_pay on d_p."Index"=d_pay."DID"
        JOIN "Pols" as p on p."Index" = d_p."PID"
        JOIN "PolsRisc" as pr on d_pay."PRID" = pr."Index"
        JOIN "Riscs" as r on r."Index" = pr."RID"
        JOIN "EXCValues" exch_p ON exch_p."EID"=d_pay."EID"
        WHERE 
            (d_pay."EID"='10' or exch_p."AtDate"=calc_date) 
            AND d_pay."PAYDate" <= calc_date
        GROUP BY 
            p."PSN", p."PNR", d_p."Index", d_p."DAMFileNr", 
            d_p."DAMDate", d_p."DAMAvizDate", pr."Index", r."Index"
    ),
    claims_rez_const AS (
        -- 第二个查询的内容
        SELECT 
            p."PSN",
            p."PNR",
            d_r."Index" as claim_file_index,
            d_r."DAMFileNr" as claim_file,
            d_r."DAMDate" as claim_date,
            d_r."DAMAvizDate" as claim_notif_date,
            ROUND(CAST(SUM(CASE 
                WHEN d_rez."EID"='10' THEN d_rez."RDAM" 
                ELSE d_rez."RDAM"*exch_r."EXCValue" 
            end) as NUMERIC),2) as rez_const_LEI
        FROM "DAM" as d_r
        JOIN "DAMRez" as d_rez on d_r."Index"=d_rez."DID"
        JOIN "Pols" as p on p."Index" = d_r."PID"
        JOIN "PolsRisc" as pr on d_rez."PRID" = pr."Index"
        JOIN "Riscs" as r on r."Index" = pr."RID"
        JOIN "EXCValues" exch_r ON exch_r."EID"=d_rez."EID"
        WHERE 
            (d_rez."EID"='10' or exch_r."AtDate"=calc_date) 
            AND d_rez."RDAMDate" <= calc_date
            AND (d_r."IsClass" = 'false' or (d_r."IsClass" = 'true' and d_r."ClassDate" >=(calc_date + interval '1 day')))
            AND (d_r."IsClosed" = 'false' or (d_r."IsClosed" = 'true' and d_r."ClosedDate" >=(calc_date + interval '1 day')))
        GROUP BY 
            p."PSN", p."PNR", d_r."Index", d_r."DAMFileNr", 
            d_r."DAMDate", d_r."DAMAvizDate", pr."Index", r."Index"
    )
    -- 最终合并查询
    SELECT 
        r_c."PSN" as Serie,
        r_c."PNR" as Numar,
        r_c."claim_file_index" as Claim_File_Index,
        r_c."claim_file" as Claim_File,
        r_c."claim_date" as Claim_Date,
        r_c."claim_notif_date" as Claim_Notif_Date,
        r_c."rez_const_LEI" as Rez_Const_Lei,
        COALESCE(r_p."plata_LEI",0) as Plata_Lei,
        COALESCE(r_c."rez_const_LEI" - COALESCE(r_p."plata_LEI",0),0) as Rez_Ram
    FROM claims_rez_const r_c
    LEFT JOIN claims_paid r_p on r_c."claim_file_index"=r_p."claim_file_index"
    WHERE COALESCE(r_c."rez_const_LEI" - COALESCE(r_p."plata_LEI",0),0) != 0;
END 
$func$ LANGUAGE plpgsql;

这个CTE版本不需要创建任何中间表，直接在一个查询里完成所有逻辑，性能通常也更好。

内容的提问来源于stack exchange，提问作者AlinT