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.
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 |