Re: WITH RECURSIVE ... simplified syntax?

On Wed, Oct 08, 2008 at 04:11:45PM -0700, Josh Berkus wrote:
> All,
>
> I was discussing WITH RECURSIVE the other day, and realized that one thing
> which we're not getting with this patch is a simplest-case simple syntax
> which 75% of users are looking for. You know, the ones with simple
> proximity trees who just want to find all children of one parent.
>
> Would it be a worth it for us to implement a non-standard simple syntax
> sugar on top of WITH RECURSIVE? Or, at least, something like
> CONNECT_BY()?

No.

The simple syntax really is simple. For an adjacency list consisting
of (id, parent_id) pairs, you do:

WITH RECURSIVE t(id, tree_path) AS (
/* Initial Condition */
SELECT a1.id, ARRAY[a1.id]
FROM adjacency a1
WHERE a1.id = 1

/* De-Cyclifier */
UNION

/* Recursion Step */
SELECT a2.id, t.tree_path || a2.id
FROM
adjacency a2
JOIN
t
ON (a2.parent_id = t.id)
)
SELECT * FROM t ORDER BY path;

While it looks a tad wordy at first, it's really simple. The WITH
part of the query is in two parts. The first is the initial condition
a.k.a. the root node of the tree. The second is the recursion step
which gets all the way to the branches. In between is UNION, which as
of Tom's recent patch now Does The Right Thing(TM) as far as
eliminating cycles.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate