Infinity recursive function exception in Postgresql

Question

I have a table Answer and Many to Many table Link (Answer n-n Answer)

Link have 2 column : from_id and to_id reference to answer_id. I want get all descendant of answer by answer_id ( from_id in Link ).

I have written function as below :

CREATE OR REPLACE FUNCTION getAllChild(_answer_id BIGINT)
RETURNS SETOF BIGINT AS $$
DECLARE r link;
BEGIN
  FOR r IN 
    SELECT * FROM link
       WHERE from_id = _answer_id
  LOOP
    RETURN NEXT r.to_id;
    RETURN QUERY SELECT * FROM getAllChild(r.to_id);
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql STRICT;

SELECT * FROM getAllChild(1);

The result is fine if to_id not duplicate with from_id that already got otherwise I will get recursive infinity.

My question is how I can make loop skip the existed to_id to call getAllChild() in RETURN QUERY

IF r.to_id <> r.from_id THEN <recursive call> END? -- With recursive CTEs you could solve this without a function. — pozs
– pozs, Commented Mar 24, 2017 at 8:57
it's not r.to_id and r.from_id in same records. I want r.to_id different with from_id in the results. can you help me with this ? — Ashes Duong
– Ashes Duong, Commented Mar 24, 2017 at 9:28
I see, then you should give recursive CTEs a try (it could be solved with them, like in Gary's answer). Its adaptation for functions could work the same way: a "path" (id array) always needs to be passed around to avoid circles. — pozs
– pozs, Commented Mar 24, 2017 at 9:42

Gary - Stand with Ukraine · Accepted Answer · 2017-03-24 09:33:00Z

2

I'd suggest you do this with a recursive CTE, you could use the same approach in a function though.

You can use an array to keep a track of all the from_id's you've dealt with, and then in the next run through you ignore any records for from_id's already in the results. In the code below I'm using the path array to track all the from_id's already seen.

with recursive t as
(
  select l.from_id,l.to_id, ARRAY[l.from_id] as path, 1 as depth 
  from link l where from_id = 2
union all 
  select l.from_id,l.to_id, array_append(t.path,l.from_id), t.depth+1 
  from link l 
  inner join t on l.from_id = t.to_id
  where not (l.from_id = ANY (t.path))  -- ignore records already processed
)
select * from t;

Fiddle at: http://sqlfiddle.com/#!15/024e80/1

Updated: As a function

CREATE OR REPLACE FUNCTION getAllChild(_answer_id BIGINT)
RETURNS SETOF BIGINT AS $$
BEGIN
  return query 
    with recursive t as
    (
    select l.from_id,l.to_id, ARRAY[l.from_id] as path, 1 as depth from link l where from_id = _answer_id
    union all 
    select l.from_id,l.to_id, array_append(t.path,l.from_id), t.depth+1 from link l 
    inner join t on l.from_id = t.to_id
    where not (l.from_id = ANY (t.path))
    )
    select to_id from t;
END;
$$ LANGUAGE plpgsql STRICT;

Arrays documentation: https://www.postgresql.org/docs/current/static/arrays.html

CTEs: https://www.postgresql.org/docs/current/static/queries-with.html

edited Mar 24, 2017 at 9:33

answered Mar 24, 2017 at 9:08

Gary - Stand with Ukraine

7,6291 gold badge33 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ashes Duong Over a year ago

I need to make it work with function as I'm using with ORM. it's no support recursive CTE :( My problem is I dont know how to select the results to compare it with from_id :(

Ashes Duong Over a year ago

Awesome, I just tested and result is so great. I'm new member and cant upvote the answer but Hope you know you just saved my day.

pozs Over a year ago

LANGUAGE sql may be more appropriate for these kind of functions (plpgsql functions have higher initialization costs).

pozs Over a year ago

@AshesDuong you can accept the answer if it was helpful.

Ashes Duong Over a year ago

@pozs, thanks for you link. I will start digging it from now.

Collectives™ on Stack Overflow

Infinity recursive function exception in Postgresql

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related