Select rows from table using tree order

Question

I have table witch contains fields: id, parent_id, name (etc.)

i want to order this table in "tree travel order" ie.

id  parent_id
1,  0
3,  1
5,  1

2,  0
8,  2

4,  0
9,  4

(...)

in short describe: take root node, append all children, take next root node append children etc.

Answer 1

By your description I assume you mean breadth-first order, which could be easly done using a WITH RECURSIVE query (PostgreSQL 8.4+):

WITH RECURSIVE tree 
AS 
(
    SELECT 
        node_name, id, parent_id, NULL::varchar AS parent_name 
    FROM foo 
    WHERE parent_id IS NULL 
    UNION
    SELECT 
        node_name, f1.id, f1.parent_id, tree.node_name AS parent_name 
    FROM 
        tree 
        JOIN foo f1 ON f1.parent_id = tree.id
) 
SELECT node_name, empno, parent_id, node_name FROM tree;

You could also use depth-first order using the following SQL:

WITH RECURSIVE tree 
AS 
(
    SELECT 
        node_name, id, parent_id, NULL::varchar AS parent_name, id::text AS path 
    FROM foo WHERE parent_id IS NULL 
    UNION
    SELECT 
        node_name, f1.id, f1.parent_id, tree.node_name AS parent_name, tree.path || '-' || f1.id::text AS path 
    FROM 
        tree 
        JOIN foo f1 ON f1.parent_id = tree.id
) 
SELECT node_name, empno, parent_id, node_name, path FROM tree ORDER BY path;

Answer 2

As noticed by synergetic, the solution for depth-first order provided by Diogo Biazus won't work for id's with different number of digits.

But you can use this solution instead, that uses arrays of integer :

WITH RECURSIVE tree 
AS 
(
    SELECT 
        node_name, id, parent_id, NULL::varchar AS parent_name, array[id] AS path 
    FROM foo WHERE parent_id IS NULL 
    UNION
    SELECT 
        node_name, f1.id, f1.parent_id, tree.node_name AS parent_name, tree.path || f1.id AS path 
    FROM 
        tree 
        JOIN foo f1 ON f1.parent_id = tree.id
) 
SELECT node_name, empno, parent_id, node_name, path FROM tree ORDER BY path;