CODEWITHSUNDEEP
postgresqlplpgsqlrecursive-query
mepihindeveloper
mepihindeveloper

Reputation: 195

How convert recursive to function in plpgsql?

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

Upvotes: 0

Views: 196

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658032

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:

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:

Upvotes: 2

Rémy Baron
Rémy Baron

Reputation: 1399

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

Upvotes: 1

Related Questions