如何从PostgreSQL存储过程返回值？执行时遇报错

错误原因 #

PostgreSQL的PROCEDURE（存储过程）设计用于执行事务性操作（如插入、更新、删除或事务管理），它不能直接通过SELECT语句返回查询结果。直接在存储过程中执行SELECT会因为没有指定结果的接收目的地，触发query has no destination for result data错误。

解决方案1：改用函数（推荐） #

如果需要返回查询结果集，使用FUNCTION替代PROCEDURE是更合适的选择，函数天然支持返回数据集。修改后的代码如下：

CREATE OR REPLACE FUNCTION distances(flight_num1 Integer)
RETURNS TABLE (
    -- 需匹配routes表的所有字段，同时新增category字段
    flight_num Integer,
    distance_miles Numeric,
    -- 补充routes表的其他字段，例如origin VARCHAR, destination VARCHAR等
    category Text
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
        SELECT 
            *,
            CASE
                WHEN distance_miles >= 0 AND distance_miles <= 2000 THEN 'short distance travel (SDT)'
                WHEN distance_miles >= 2000 AND distance_miles <= 6500 THEN 'Intermediate distance travel (IDT)'
                WHEN distance_miles > 6500 THEN 'Long distance travel (LDT)'
            END AS category
        FROM
            "routes" 
        WHERE 
            flight_num = flight_num1;
END;
$$;

-- 调用函数
SELECT * FROM distances(1111);

关键说明 #

• 用RETURNS TABLE明确定义返回的结果结构，需包含原表所有字段和新增的category字段；若不想手动列出字段，可改用RETURNS SETOF RECORD，但明确列字段可读性更强。
• 通过RETURN QUERY执行SELECT并将结果集返回。
• 调用函数时使用SELECT * FROM 函数名(参数)，而非存储过程的CALL语法。

解决方案2：使用存储过程返回结果（不推荐用于数据集） #

如果必须使用存储过程，可通过游标或OUT参数返回结果，但步骤更繁琐：

方式A：游标输出（支持多条记录） #

CREATE OR REPLACE PROCEDURE distances(IN flight_num1 Integer, OUT refcursor refcursor)
LANGUAGE plpgsql
AS $$
BEGIN
    OPEN refcursor FOR
        SELECT 
            *,
            CASE
                WHEN distance_miles >= 0 AND distance_miles <= 2000 THEN 'short distance travel (SDT)'
                WHEN distance_miles >= 2000 AND distance_miles <= 6500 THEN 'Intermediate distance travel (IDT)'
                WHEN distance_miles > 6500 THEN 'Long distance travel (LDT)'
            END AS category
        FROM
            "routes" 
        WHERE 
            flight_num = flight_num1;
END;
$$;

-- 调用存储过程
BEGIN;
CALL distances(1111, 'mycursor');
FETCH ALL FROM mycursor;
COMMIT;

方式B：OUT参数（仅适合单条记录） #

若查询确保只返回单条记录，可定义多个OUT参数接收每个字段的值，但这种方式无法处理多条结果。

总结 #

如果核心需求是返回查询结果集，优先使用函数，这是PostgreSQL中处理数据返回的标准方式。存储过程更适合执行无需返回数据集的事务类操作。

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