将SELECT查询转为PL/pgSQL函数时遇结果无目标错误的解决求助

问题背景 #

需要将以下SELECT语句转换为PL/pgSQL函数：

SELECT object_id, brigade_code, date_and_time
FROM purpose
WHERE date_and_time BETWEEN '2022-06-15 16:20:23' AND '2022-10-03 18:12:45';

自行编写的函数代码执行后触发报错：

ERROR: There is no destination for result data in the request
HINT: If you need to discard SELECT results, use PERFORM.
CONTEXT: PL/pgSQL function report_for_period (timestamp without time zone,timestamp without time zone), line 3, SQL statement

原函数代码如下：

Create function report_for_period (date_from timestamp, date_to timestamp)
returns int
language 'plpgsql'
as
$$
    begin
        SELECT date_and_time::timestamp
        from purpose
        WHERE date_and_time between $1 and $2;
    end;
$$

select report_for_period ('2022-06-15 16:20:23'::timestamp,'2022-10-03 18:12:45'::timestamp)

错误原因分析 #

• PL/pgSQL中直接执行SELECT语句时，必须将结果存入变量或返回结果集，否则会抛出"无结果数据目标"的错误
• 原函数声明返回int类型，但实际需要返回多列结果集，返回类型不匹配
• 原查询仅选择了date_and_time字段，与目标查询要求的object_id, brigade_code, date_and_time不符

修正后的函数代码 #

要实现原查询功能，需将函数返回类型改为匹配原查询的表结构，并使用RETURN QUERY返回结果集（请根据purpose表的实际字段类型替换代码中的占位类型）：

CREATE OR REPLACE FUNCTION report_for_period(date_from timestamp, date_to timestamp)
RETURNS TABLE(object_id INT, brigade_code VARCHAR, date_and_time timestamp) -- 替换为实际字段类型
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT object_id, brigade_code, date_and_time
    FROM purpose
    WHERE date_and_time BETWEEN date_from AND date_to;
END;
$$;

-- 调用函数
SELECT * FROM report_for_period('2022-06-15 16:20:23'::timestamp, '2022-10-03 18:12:45'::timestamp);

关键修改点 #

• 将返回类型从int改为TABLE(...)，匹配原查询的返回列及对应数据类型
• 使用RETURN QUERY语句返回SELECT结果集，解决直接执行SELECT无目标的问题
• 修正查询字段，确保与原SELECT语句一致，保留object_id, brigade_code, date_and_time三个字段
• 调用函数时采用SELECT * FROM <函数名>的方式，适配表类型的返回结果

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