sql - 返回表的 sql 中的函数 (Postgresql)

Question

我有以下查询返回一个表:

WITH RECURSIVE EXPL (parent_node, node, filename) AS
     (
      SELECT ROOT.parent_node, ROOT.node, ROOT.filename
      FROM   filesystem   ROOT
      WHERE  ROOT.parent_node = 1

      UNION ALL

      SELECT CHILD.parent_node, CHILD.node, CHILD.filename
      FROM   EXPL PARENT, filesystem CHILD
      WHERE  PARENT.node = CHILD.parent_node
     )

     SELECT   DISTINCT parent_node, node, filename
     FROM     EXPL
     ORDER BY parent_node, node;
END

但我想要一个将 WHERE ROOT.parent_node = 1 替换为 WHERE ROOT.parent_node = x 的函数。 X是一个整数，是函数的参数。

我想过，但一点都不正确:

CREATE FUNCTION childs(x INT) RETURNS TABLE 
RETURN
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, filesystem CHILD
      WHERE  PARENT.node = CHILD.parent_node
     )

     SELECT   DISTINCT parent_node, node, filename
     FROM     EXPL
     ORDER BY parent_node, node;
END;

Answer 1

您的函数定义在 RETURN 之后缺少 BEGIN 和 QUERY

CREATE OR REPLACE FUNCTION childs(x int) 
RETURNS TABLE (parent_node integer, node integer, filename varchar(255)) AS
$function$
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, filesystem child
      WHERE  parent.node = child.parent_node
     )

     SELECT   DISTINCT expl.parent_node, expl.node, expl.filename
     FROM     expl
     ORDER BY parent_node, node;
END 
$function$ LANGUAGE plpgsql;

像这样使用函数:

SELECT * FROM childs(1)