Re: WITH RECURSIVE clause -- all full and partial paths

On Fri, Jun 12, 2009 at 10:14:21PM +0200, Harald Fuchs wrote:
> In article <aaf543e90906120856r5219cf9cv7f13ba0d37494378(at)mail(dot)gmail(dot)com>,
> aryoo <howaryoo(at)gmail(dot)com> writes:
>
> > Dear list,
> > In reference to the message below posted on the 'pgsql-hackers' list regarding
> > 'iterative' queries,
> > could anyone help me write the queries that return all full and all partial
> > paths from the root?
>
> Probably you want to use the following query:
>
> WITH RECURSIVE subdepartment AS (
> SELECT id, parent_department, name AS path
> FROM department
> WHERE name = 'A'
> UNION ALL
> SELECT d.id, d.parent_department, sd.path || '.' || d.name
> FROM department d
> JOIN subdepartment sd ON sd.id = d.parent_department
> )
> SELECT id, path
> FROM subdepartment;

This is much easier as:

WITH RECURSIVE subdepartment AS (
SELECT id, parent_department, ARRAY[name] AS "path"
FROM department
WHERE name = 'A'
UNION ALL
SELECT d.id, d.parent_department, sd."path" || d.name
FROM department d
JOIN subdepartment sd ON (
sd.id = d.parent_department
AND
d.name NOT IN(sd."path") /* Make sure there are no cycles */
)
SELECT id, path
FROM subdepartment;

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