WITH RECURSIVE from 2 or more tables.

From: Капралов Александр <alnkapa(at)gmail(dot)com>
To: pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: WITH RECURSIVE from 2 or more tables.
Date: 2012-09-20 15:01:49
Message-ID: CAJqqVEVczazz16Exsj9sExgYnNoMZiOjBbiNfNOa5GDz7v8dWg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello.

I have 2 tables:

CREATE TABLE "group"
(
id serial NOT NULL
"name" character varying(23) NOT NULL
id_user integer NOT NULL DEFAULT 0,
parent integer DEFAULT 0,
CONSTRAINT group_user_fkey FOREIGN KEY (id_user) REFERENCES "user"
(id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE,
)

and

CREATE TABLE "user"
(
id serial NOT NULL
"login" character varying(12) NOT NULL
parent integer DEFAULT 0
)
Can I get a tree of records in a single query, and their two tables
using "WITH RECURSIVE".
tree one table I made, but how to combine these queries do not understand.

WITH RECURSIVE gg(id,parent,level,path,cycle) AS (
SELECT id,parent,0,ARRAY[id],false FROM web."group" WHERE id=899
UNION ALL
SELECT g.id,g.parent,level + 1,path||g.id,g.id=ANY(path) FROM
web."group" as g,gg WHERE g.parent=gg.id AND NOT cycle
)
SELECT u.id,u.name,path FROM web."group" as u, gg WHERE gg.id=u.id;

WITH RECURSIVE uu(id,parent,level,path,cycle) AS (
SELECT id,id_user,0,ARRAY[id],false FROM web."user" WHERE id=71
UNION ALL
SELECT u.id,u.id_user,level + 1,path||u.id,u.id=ANY(path) FROM
web."user" as u,uu WHERE u.id_user=uu.id AND NOT cycle
)
SELECT u.id,u.login,path FROM web."user" as u, uu WHERE uu.id=u.id;

Could you please help me.

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2012-09-20 15:22:13 Re: Passing row set into PL/pgSQL function.
Previous Message Alan Millington 2012-09-20 14:56:16 Using psql -f to load a UTF8 file