Getting list of all children from adjacency tree

From PostgreSQL wiki

Jump to: navigation, search

Library Snippets

Getting list of all children from adjacency tree

Works with PostgreSQL

Any version

Written in

Pl/pgSQL

Depends on

Nothing


Description

Assuming you have Adjacency List based tree - i.e. table with structure similar to this: 

                           Table "public.test"
  Column   |  Type   |                     Modifiers
-----------+---------+---------------------------------------------------
 id        | integer | not null default nextval('test_id_seq'::regclass)
 parent_id | integer |
 x         | text    |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "test_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES test(id)

you might want to get all children of given element.

To do it, you can use this function: 

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

Usage 

# select get_all_children_array(3);
 get_all_children_array
------------------------
 {5,6,9}
(1 row)
 
 
# select * from test where id = any( get_all_children_array(3) );
 id | parent_id | x
----+-----------+---
  5 |         3 | e
  6 |         3 | f
  9 |         5 | i
(3 rows)
Retrieved from "http://wiki.postgresql.org/wiki/Getting_list_of_all_children_from_adjacency_tree"
Personal tools