From: Andreas Joseph Krogh <andreak@officenet.no>
Organization: OfficeNet AS
To: pgsql-sql@postgresql.org
Subject: Order of "WITH RECURSIVE" output
Date: Tue, 7 Jul 2009 03:44:35 +0200
User-Agent: KMail/1.12.0 (Linux/2.6.28-13-generic; KDE/4.2.95; i686; ; )
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Message-Id: <200907070344.36084.andreak@officenet.no>

Hi all.
Take this example from src/test/regress/sql/with.sql:

WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
(
	-- non recursive term
	SELECT 1, * FROM department WHERE name =3D 'A'

	UNION ALL

	-- recursive term
	SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
		WHERE d.parent_department =3D sd.id
)
SELECT * FROM subdepartment ORDER BY name;

 level | id | parent_department | name
=2D------+----+-------------------+------
     1 |  1 |                 0 | A
     2 |  2 |                 1 | B
     3 |  3 |                 2 | C
     3 |  4 |                 2 | D
     4 |  6 |                 4 | F
(5 rows)

If I omit "ORDER BY", is the output *guaranteed* (according to some standar=
d) to be ordered in "hierarchy"-order, meaning each parent-department is al=
ways output before its respective child, or do I have to calculate a "level=
" column (like in the example above) and specify "ORDER BY LEVEL" to be 100=
%?

I'm using WITH RECURSIVE as sub-queries to build up arrays of parents in ea=
ch output-row and would like as little overhead as possible and hence avoid=
 unnecessary sorting.

=2D-=20
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / CTO
=2D-----------------------+---------------------------------------------+
OfficeNet AS            | The most difficult thing in the world is to |
Rosenholmveien 25       | know how to do a thing and to watch         |
1414 Troll=C3=A5sen          | somebody else doing it wrong, without       |
NORWAY                  | comment.                                    |
                        |                                             |
Tlf:    +47 24 15 38 90 |                                             |
=46ax:    +47 24 15 38 91 |                                             |
Mobile: +47 909  56 963 |                                             |
=2D-----------------------+---------------------------------------------+