Recursive Parent ID PGSQL

gistfile1.txt

# https://wiki.postgresql.org/wiki/Getting_list_of_all_children_from_adjacency_tree
# https://www.depesz.com/2009/03/23/getting-list-of-all-children-in-adjacency-list-tree-structure/
CREATE OR REPLACE FUNCTION get_all_children_array(use_parent INT4) RETURNS INT4[] AS
$$
DECLARE
  process_parents INT4[] := ARRAY [ use_parent ];
  children        INT4[] := '{}';
  new_children    INT4[];
BEGIN
  WHILE (array_upper(process_parents, 1) IS NOT NULL)
    LOOP
      new_children :=
          ARRAY(SELECT id FROM ferramentas WHERE parent_id = ANY (process_parents) AND id <> ALL (children));
      children := children || new_children;
      process_parents := new_children;
    END LOOP;
  RETURN children;
END;
$$ LANGUAGE plpgsql;