WITH RECURSIVE from 2 or more tables.

Поиск
Список
Период
Сортировка
От Капралов Александр
Тема WITH RECURSIVE from 2 or more tables.
Дата
Msg-id CAJqqVEVczazz16Exsj9sExgYnNoMZiOjBbiNfNOa5GDz7v8dWg@mail.gmail.com
обсуждение исходный текст
Список 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.


В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: pg_upgrade: out of memory
Следующее
От: Merlin Moncure
Дата:
Сообщение: Re: Passing row set into PL/pgSQL function.