How convert recursive to function in plpgsql?

Question

i have that working code, but i need to convert it to function with dynamic attribute tid=1645 where number 1645 will always change.

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

Now, i have the same:

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$
;

As a result i need table of results... I tried to return select c_tid, c_boss; but have error: error near return

@LaurenzAlbe the tree of employee. two columns: one is your number, the other is the number of the boss. I need to get the entire hierarchy by the parameter — mepihindeveloper, May 07 '19 at 14:34
Then follow @S-Man's advice. Add the error you get, plus your updated code, to the question. — Laurenz Albe, May 07 '19 at 15:43

Erwin Brandstetter · Accepted Answer · 2019-05-07T15:44:05.340

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;

You want UNION ALL instead of UNION as it doesn't make sense to try and fold duplicates climbing the hierarchy. (A duplicate would initiate an endless loop.)

TABLE r is short for SELECT * FROM r. Your org. select c_tid, c_boss was wrong. See:

Is there a shortcut for SELECT * FROM?

Can also be a simpler SQL function:

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;

See:

Difference between language sql and language plpgsql in PostgreSQL functions

Rémy Baron · Answer 2 · 2019-05-07T15:29:23.127

You have to use "return query" on all the query (with included)

You forgot the "from r" in the main select

/* EDIT */ in you example you select c_tid and c_boss instead of tid and boss and the test of the join is inverted

request updated:

  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
       return query 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.tid = r.boss        )

    select tid, boss from r;
end;
$function$
;

In the second case (worked) a strange result is issued. If no function is used, then the values `1645 15169; 15169 1731; 1731 13602; 13602 NULL`, and if the first, then a huge number of entries. No function - right — mepihindeveloper, May 07 '19 at 14:33

How convert recursive to function in plpgsql?

2 Answers2