Re: usage for 'with recursive'?

hubert depesz lubaczewski wrote:
> there have been a discussions about how posdtgresql needs 'with
> recursive' queries.
>
> not that i would like to object the idea (new feature is always  a
> good thing), but is anybody able to show me real usage of this kind of
> queries?
> as i see it the only usage for 'with recursive' is when one have a
> tree-structure stored as:
> create table objects (id serial primary key, parent_id int references
> objects (id), ...)
> and one want to do some "deep queries" without client-side
> recursion/loops.
>
> is it the only thing 'with recursive' is useful for? i mean it sounds
> unrealistic given that better data-structures for tree hierarchies
> have been proposed and implemented.

Better?  I think perhaps different.  There is materialized path, which
requires a very problematic unlimited-length column to hold the path,
and there is upper/lower bounds, which again requires client-side
row-by-row processing.  Both have the unpleasant problem that changes to
one row may affect many others.

AFAIK, the "WITH RECURSE" allows the simplest data structure, being
key/parent_key.  The best benefit of this method is that it is a simple
foreign key and no action on a row ever affects another row, unlike the
other two.  If we could query out a list using WITH RECURSE it would
become very powerful.

>
> best regards,
>
> depesz
>


--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com / www.andromeda-project.org
Office: 631-689-7200   Cell: 631-379-0010

::Think you may have a problem with programming? Ask yourself this
::question: do you worry about how to throw away a garbage can?