PostgreSQL返回表函数改造：将递归查询改为带参数形式

我来帮你修正这个函数，你的写法主要存在几个语法细节问题，下面是调整后的可用版本：

CREATE OR REPLACE FUNCTION childs(x INT)
RETURNS TABLE(parent_node INT, node INT, filename TEXT) -- 必须明确返回表的列结构
LANGUAGE SQL
AS $$
WITH RECURSIVE EXPL (parent_node, node, filename) AS (
    SELECT ROOT.parent_node, ROOT.node, ROOT.filename
    FROM filesystem ROOT
    WHERE ROOT.parent_node = x
    UNION ALL
    SELECT CHILD.parent_node, CHILD.node, CHILD.filename
    FROM EXPL PARENT
    JOIN filesystem CHILD ON PARENT.node = CHILD.parent_node -- 用显式JOIN替代隐式连接，逻辑更清晰
)
SELECT DISTINCT parent_node, node, filename
FROM EXPL
ORDER BY parent_node, node;
$$;

关键修正点说明： #

• 返回结构定义：RETURNS TABLE后面必须明确列出每一列的名称和对应数据类型，PostgreSQL需要这个信息确定函数的返回格式。
• 函数体包裹：用AS $$ ... $$包裹函数体是PostgreSQL的标准写法，能避免字符串单引号冲突的问题。
• 显式连接语法：把原来的隐式表连接改成JOIN语句，让SQL逻辑更直观，也符合现代SQL编码规范。
• 语言指定：添加LANGUAGE SQL，因为这个函数只包含纯SQL逻辑，不需要PL/pgSQL的流程控制能力。

如果后续需要扩展函数逻辑（比如添加条件判断），也可以用PL/pgSQL版本：

CREATE OR REPLACE FUNCTION childs(x INT)
RETURNS TABLE(parent_node INT, node INT, filename TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    WITH RECURSIVE EXPL (parent_node, node, filename) AS (
        SELECT ROOT.parent_node, ROOT.node, ROOT.filename
        FROM filesystem ROOT
        WHERE ROOT.parent_node = x
        UNION ALL
        SELECT CHILD.parent_node, CHILD.node, CHILD.filename
        FROM EXPL PARENT
        JOIN filesystem CHILD ON PARENT.node = CHILD.parent_node
    )
    SELECT DISTINCT parent_node, node, filename
    FROM EXPL
    ORDER BY parent_node, node;
END;
$$;

调用示例 #

直接通过表函数的方式调用即可：

SELECT * FROM childs(1); -- 传入你需要查询的父节点ID

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