postgresql - 如何将递归转换为plpgsql中的函数？

Question

我有该工作代码，但我需要将其转换为具有动态属性 tid=1645 的函数，其中数字 1645 将始终更改。

with recursive r as (
    select tid, boss from titles where tid=1645
    union
    select titles.tid, titles.boss from titles join r on titles.tid = r.boss
)
select * from r

现在，我有同样的:

DROP FUNCTION bosses_of_rsd_tids(integer);
CREATE OR REPLACE FUNCTION public.bosses_of_rsd_tids(rsd_tid int)
    RETURNS table (c_tid int, c_boss int)
    LANGUAGE plpgsql
AS $function$
    begin
        with recursive r as (
            select tid, boss from titles where tid=rsd_tid
            union
            select titles.tid, titles.boss from titles join r on titles.boss = r.tid
        )

        select c_tid, c_boss;
    end;
 $function$
;

因此我需要结果表...我试图return select c_tid, c_boss;但是有错误:error near return

Answer 1

CREATE OR REPLACE FUNCTION public.bosses_of_rsd_tids(rsd_tid int)
  RETURNS TABLE (c_tid int, c_boss int) AS
$func$
BEGIN
   RETURN QUERY
   WITH RECURSIVE r AS (
      SELECT tid, boss
      FROM   titles
      WHERE  tid = rsd_tid

      UNION ALL                           -- ?!
      SELECT t.tid, t.boss
      FROM   r
      JOIN   titles t ON t.tid = r.boss   -- !
      )
   TABLE r;                               -- !
END
$func$  LANGUAGE plpgsql;

您需要 UNION ALL 而不是 UNION 因为尝试折叠攀登层次结构的重复项没有意义。 (重复会引发无限循环。)

TABLE r 是 SELECT * FROM r 的缩写。你的组织。 select c_tid, c_boss 错误。见:

• Is there a shortcut for SELECT * FROM?

也可以是更简单的SQL函数:

CREATE OR REPLACE FUNCTION public.bosses_of_rsd_tids(rsd_tid int)
  RETURNS TABLE (c_tid int, c_boss int) AS
$func$
   WITH RECURSIVE r AS (
      SELECT tid, boss
      FROM   titles
      WHERE  tid = rsd_tid

      UNION ALL
      SELECT t.tid, t.boss
      FROM   r
      JOIN   titles t ON t.tid = r.boss
      )
   TABLE r;
$func$  LANGUAGE sql;

参见:

• Difference between language sql and language plpgsql in PostgreSQL functions