s4553711
10/31/2014 - 7:43 AM

以下昰一個Recursive query的範例,只使用到一個表node,其中以id及parent組成其樹狀目錄結構的資訊。

以下昰一個Recursive query的範例,只使用到一個表node,其中以id及parent組成其樹狀目錄結構的資訊。

CREATE TABLE node
(
	id integer NOT NULL DEFAULT nextval('nodefolder_id_seq'::regclass),
	name character varying(20) NOT NULL,
	parent integer,
	type integer NOT NULL
)
WITH RECURSIVE node_rec(id, name, parent, type) as (
	-- Non-recursive term
	(
		select id, name, parent, type from node where id = 566
	)
	UNION ALL
	-- Recursive Term
	-- Here I use 'UNION ALL' statement so that the duplicate result will be included.
	-- You can choose to use 'UNION' instead of 'UNION ALL' to get distinct data.
	(
		select c.id, c.name , c.parent, c.type from node_rec AS p, node AS c 
		where c.parent = p.id
	)
)
select id, name, parent, type from node_rec;