How to get rid of recursion in the query:

CREATE OR REPLACE FUNCTION accounts.get_group_by_group(_id uuid) RETURNS SETOF uuid AS $BODY$ BEGIN RETURN query WITH RECURSIVE R AS ( SELECT id FROM accounts.groups WHERE id=(_id) UNION ALL SELECT accounts.groups.id FROM accounts.groups JOIN R ON accounts.groups.parent_id = r.id ) SELECT *FROM R; END; $BODY$ LANGUAGE plpgsql VOLATILE
  • Is it permissible to change the tree storage scheme? For example, on a regular ltree? postgresql.org/docs/current/static/ltree.html - Minor
  • If more than one level of nesting in the data, then nothing - Mike
  • But with the help of cycles, somehow you can organize this? The storage scheme was not planned to be changed. the table has id, parent_id, name - Kate
  • Cycle is also possible. But back to the essence of the recursive solution. And the meaning of the replacement? - Shallow
  • Can I see it in code? - Kate

0