Re:Recursive Postgres Queries
by dragonwriter on Monday June 24, 2013 @01:41PM
(#5934456)
Recursive CTEs are a portable solution; they are part of the SQL:1999 standard and are supported by current versions of Postgres, DB2, Oracle, SQL Server, Firebird, etc.
The only two widely used SQL-using relational databases I can think of off the top of my head that don't support them are SQLite and MySQL.
(PostgreSQL arrays aren't portable, though in other DBs I've seen strings used for similar purposes, though that is truly an abomination.)
One way I've done it before is to have a field that contains the path (eg node1/sub_n/sub_n2 etc) and another field containing the ordering (in my case I did it across the whole tree). You can then use simple single selects to get ancestors / descendants / subtrees / siblings / all sorts of things you need (eg where path like '/path/to/subtree/%s'). Obviously not totally efficient but easy to work with and fine for small trees.
The trade-off is that the operations to manipulate the tree are more fiddly - they make for a great little brain teaser though :)