postgresql - Postgres : "ERROR: RETURN cannot have a parameter in function returning set" in recursive function

Question

我对编写 Postgres 函数非常非常陌生。我正在编写一个函数，通过从 child 的 ID 开始递归地搜索 parent 来为 child 构建物化路径。这是我的函数，但我不断收到错误 ERROR: RETURN cannot have a parameter in function returning set

create or replace function build_mp(child_id text)
returns SETOF text 
language plpgsql
as $$
begin
    select parentid from account where childid = child_id;
    if parentid IS NULL then
        return ARRAY[child_id];
    else
        return build_mp(parentid) || ARRAY[child_id];
    end if;
end $$;

SELECT build_mp('mychild');

我做错了什么？

编辑

这是可行的解决方案。它获取子项的 ID，然后递归搜索其上方的所有父项，为新的子项构建物质路径。

create or replace function build_mp(child_id text)
returns text[] 
language plpgsql
as $$
declare
    parent text;
begin
    execute 'select parentid from account where childid = $1' INTO parent USING child_id;
    if parent IS NULL THEN
        return ARRAY[child_id];
    else
        return build_mp(parent) || ARRAY[child_id];
    end if;
end $$;

SELECT build_mp('mychild') AS mp;

Answer 1

要克服“错误:查询没有结果数据的目的地”错误，您不需要动态 SQL。

您可以 select into a variable directly :

select parentid into parent from account where childid = child_id;

---

但是您可以使用 recursive CTE 来简化您的函数和一个 SQL 函数。这会表现得更好，尤其是在关卡数量较多的情况下:

create or replace function build_mp(child_id text)
  returns text[]
language sql
as 
$$
   with recursive all_levels (childid, parentid, level) as (
      select childid, parentid, 1
      from account
      where childid = child_id
      union all
      select c.childid, c.parentid, p.level + 1
      from account c
       join all_levels p on p.parentid = c.childid
   )
   select array_agg(childid order by level)
   from all_levels;
$$;