PostgreSQL WITH RECURSIVE example

with_recursive_tree_examples.sql

DROP TABLE IF EXISTS my_tree;
CREATE TABLE my_tree (id integer, parent integer);
INSERT INTO my_tree VALUES (1, null), (2, 1), (3, 1), (4, 3);
-- 
--   1
--  / \
--  2 3
--    |
--    4

WITH RECURSIVE r AS (
	SELECT * FROM my_tree WHERE id = 1
	UNION ALL
	SELECT my_tree.* FROM my_tree, r WHERE my_tree.parent = r.id
)
SELECT * FROM r ORDER BY id;

-- http://lets.postgresql.jp/documents/technical/with_recursive