递归PostgreSQL查询转函数报错42601：无结果数据目标的解决

这个42601错误的核心原因是：PL/pgSQL是过程化语言，不能像普通SQL那样直接执行查询就自动返回结果。你在函数里写了递归查询，但没有明确指定查询结果的去向——要么把结果存入变量（用INTO子句），要么把结果返回给调用者（用RETURN QUERY/RETURN NEXT）。数据库不知道该怎么处理这个查询的输出，所以抛出了"query has no destination for result data"的错误。

递归查询通常需要返回多行多列的结果集，所以我们需要定义函数的返回类型为表结构，并用RETURN QUERY来执行递归查询，将结果返回给调用方。

示例1：无参数的递归路径查询函数 #

假设你原来的递归SQL是查询树形结构的全路径（比如分类表）：

WITH RECURSIVE cte AS (
    SELECT id, parent_id, name, ARRAY[name] AS path
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.parent_id, c.name, cte.path || c.name
    FROM categories c
    JOIN cte ON c.parent_id = cte.id
)
SELECT id, name, path FROM cte;

转换为PL/pgSQL函数的正确写法：

CREATE OR REPLACE FUNCTION path1()
RETURNS TABLE(id INT, name VARCHAR(50), path TEXT[]) AS $$
BEGIN
    -- 用RETURN QUERY指定将递归查询的结果返回
    RETURN QUERY
    WITH RECURSIVE cte AS (
        SELECT id, parent_id, name, ARRAY[name] AS path
        FROM categories WHERE parent_id IS NULL
        UNION ALL
        SELECT c.id, c.parent_id, c.name, cte.path || c.name
        FROM categories c
        JOIN cte ON c.parent_id = cte.id
    )
    SELECT id, name, path FROM cte;
END;
$$ LANGUAGE plpgsql;

调用函数的方式：

SELECT * FROM path1();

示例2：带参数的递归路径查询函数 #

如果需要根据指定节点ID查询其下属的路径，可以给函数加参数：

CREATE OR REPLACE FUNCTION path1(root_id INT)
RETURNS TABLE(id INT, name VARCHAR(50), path TEXT[]) AS $$
BEGIN
    RETURN QUERY
    WITH RECURSIVE cte AS (
        SELECT id, parent_id, name, ARRAY[name] AS path
        FROM categories WHERE id = root_id
        UNION ALL
        SELECT c.id, c.parent_id, c.name, cte.path || c.name
        FROM categories c
        JOIN cte ON c.parent_id = cte.id
    )
    SELECT id, name, path FROM cte;
END;
$$ LANGUAGE plpgsql;

调用方式：

-- 查询ID为1的节点及其下属的路径
SELECT * FROM path1(1);

补充说明 #

如果你的递归查询只返回单个值（比如某条路径的长度），可以用INTO将结果存入变量，再用RETURN返回：

CREATE OR REPLACE FUNCTION get_path_length(node_id INT)
RETURNS INT AS $$
DECLARE
    path_len INT;
BEGIN
    WITH RECURSIVE cte AS (
        SELECT id, parent_id, 1 AS depth
        FROM categories WHERE id = node_id
        UNION ALL
        SELECT c.id, c.parent_id, cte.depth + 1
        FROM categories c
        JOIN cte ON c.parent_id = cte.id
    )
    SELECT MAX(depth) INTO path_len FROM cte;
    
    RETURN path_len;
END;
$$ LANGUAGE plpgsql;

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