如何编写PostgreSQL FUNCTION实现首个SELECT无结果时执行第二个查询

原代码存在的问题 #

• 返回值类型错误：原声明返回event仅支持返回单行结果，实际需要返回多条匹配的活动记录，需改为SETOF event
• 变量类型错误：bigserial是自增序列类型，无法存储多个用户ID集合，需改为数组类型bigint[]
• 缺失必要参数：第二个查询用到的半径参数radius没有在函数入参中声明
• 关键字未转义：user是PostgreSQL保留关键字，作为表名使用时必须加双引号包裹为"user"
• 变量引用错误：plpgsql中直接使用声明的参数/变量名即可，不需要加:前缀
• 逻辑不符合需求：原写法用UNION ALL会同时返回两个查询的结果，没有实现「第一个查询无结果才执行第二个」的逻辑

正确实现代码 #

CREATE OR REPLACE FUNCTION all_nearby_events(my_school_id INT, radius NUMERIC) 
RETURNS SETOF event AS $$
DECLARE
    v_user_ids bigint[]; -- 存储目标学校下的所有用户ID
    v_first_query_count INT; -- 统计第一个查询的结果数量
BEGIN
    -- 先获取目标学校的所有用户ID
    SELECT array_agg(u.id) INTO v_user_ids 
    FROM "user" u 
    WHERE u.school_id = my_school_id;

    -- 先检查第一个查询是否有结果
    SELECT COUNT(*) INTO v_first_query_count
    FROM event e
    WHERE e.organizer_id = ANY(v_user_ids)
       OR e.id IN (SELECT i.event_id FROM invite i WHERE i.user_id = ANY(v_user_ids));

    -- 第一个查询有结果则直接返回第一个查询的内容
    IF v_first_query_count > 0 THEN
        RETURN QUERY
        SELECT e.* FROM event e
        WHERE e.organizer_id = ANY(v_user_ids)
           OR e.id IN (SELECT i.event_id FROM invite i WHERE i.user_id = ANY(v_user_ids));
    ELSE
        -- 无结果则返回第二个查询的周边活动
        RETURN QUERY
        SELECT e.* FROM event e
        INNER JOIN "user" u on u.id = e.organizer_id
        INNER JOIN school s on u.school_id = s.id
        WHERE u.school_id = my_school_id 
          AND ST_DWithin(ST_Transform(e.geom, 2163), ST_Transform(s.geom, 2163), radius * 1609.34);
    END IF;
END;
$$ LANGUAGE plpgsql STABLE;

使用说明 #

• 入参说明：my_school_id是目标学校ID，radius是搜索半径（单位为英里，代码中1609.34是英里转米的换算系数，如果你需要用公里作为单位可以改为1000）
• 调用示例：SELECT * FROM all_nearby_events(9, 5); 代表查询学校ID为9的相关活动，无结果时返回学校周边5英里范围内的活动
• 函数加了STABLE修饰符，代表相同入参多次调用返回结果一致，可以提升查询性能

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