Thread: Querying Hierarchical Data
Hi, How do I access hierarchical data under PostgreSQL? Does it have SQL command similar to Oracle's CONNECT BY? Any help is appreciated Eric
On Sun, 2003-03-02 at 21:27, Eric wrote:
> Hi,
>
> How do I access hierarchical data under PostgreSQL?
> Does it have SQL command similar to Oracle's CONNECT BY?
>
> Any help is appreciated
Hey,
I don't think PostgreSQL has a CONNECT BY command.
But, if your hierarchical data is stored in an adjecency list model
table then you can use the following function (i wrote it as a proof of
concept and don't know how well it would scale on a VERY large dataset,
but you are welcome to try ;-)
CREATE TYPE tree_result AS (id int4, level int4, name varchar);
CREATE FUNCTION render(int4, int4) RETURNS SETOF tree_result AS '
DECLARE current_level tree_result%ROWTYPE; stack_level int4 := 1; start_level ALIAS FOR $1; limit_level ALIAS
FOR$2; stack_oid int4;
BEGIN
SELECT INTO stack_oid oid FROM pg_class WHERE relname = ''stack''; IF NOT FOUND THEN CREATE TEMPORARY TABLE
stack(id int4, level int4, name varchar); END IF;
INSERT INTO stack (id, level, name) (SELECT child AS id, stack_level, name FROM tree WHERE
CASE WHEN start_level IS NULL OR start_level = 0 THEN parent IS NULL ELSE parent =
start_level END);
WHILE stack_level > 0 LOOP SELECT INTO current_level * FROM stack WHERE level = stack_level
ORDER BY name LIMIT 1; IF current_level.id IS NOT NULL THEN RETURN NEXT current_level; DELETE
FROMstack WHERE id = current_level.id; IF stack_level+1 <= limit_level THEN INSERT INTO stack
(id,level, name) (SELECT child AS id, stack_level+1 AS level, name FROM tree WHERE
parent= current_level.id); IF FOUND THEN stack_level := stack_level + 1;
ENDIF; END IF; ELSE stack_level := stack_level - 1; END IF; END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';
To give an example, consider this data:
CREATE TABLE tree (child int4, parent int4, name varchar);
INSERT INTO tree VALUES (1, NULL, 'lex');
INSERT INTO tree VALUES (2, NULL, 'marina');
INSERT INTO tree VALUES (3, 1, 'oles');
INSERT INTO tree VALUES (4, 1, 'marina');
INSERT INTO tree VALUES (5, 3, 'peter');
INSERT INTO tree VALUES (6, 3, 'elvira');
INSERT INTO tree VALUES (7, 6, 'peter');
INSERT INTO tree VALUES (8, 6, 'natasha');
INSERT INTO tree VALUES (9, 4, 'valja');
INSERT INTO tree VALUES (10, 9, 'tosja');
INSERT INTO tree VALUES (11, 4, 'vitja');
INSERT INTO tree VALUES (12, 11, 'eda');
And these queries:
SELECT repeat(' ', level)||name AS display FROM render(0, 100); display
----------------- lex marina valja tosja vitja eda oles elvira natasha
peter peter marina
(12 rows)
SELECT id, repeat(' ', level)||name AS display FROM render(3, 100);id | display
----+------------- 6 | elvira 8 | natasha 7 | peter 5 | peter
(4 rows)
SELECT id, repeat(' ', level)||name AS display FROM render(1, 2);id | display
----+------------ 4 | marina 9 | valja11 | vitja 3 | oles 6 | elvira 5 | peter
I hope this helps. If you do use it, I would be very interested to know
what kind of performance you get.
thanks and good luck!!
- lex
the URL below writes somthing abt what u are looking for. http://gppl.terminal.ru/index.eng.html regds mallah. On Monday 03 March 2003 07:57 am, Eric wrote: > Hi, > > How do I access hierarchical data under PostgreSQL? > Does it have SQL command similar to Oracle's CONNECT BY? > > Any help is appreciated > > Eric > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Regds Mallah ---------------------------------------- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.
* Eric <someone@somewhere.com> [03.03.2003 17:10]: > Hi, > > How do I access hierarchical data under PostgreSQL? > Does it have SQL command similar to Oracle's CONNECT BY? > > Any help is appreciated I've have the same problem recently. I've combined nested sets with adjacency list model, perfect solution, IMHO. Read more about nested sets here: http://www.brasileiro.net:8080/postgres/cookbook/view-recipes.adp?section_id=310&format=long -- Victor Yegorov
Altho i think the genealogical arrays implementation is solid, fast and intuitive, for doit-yourselfers, i must definately point out the tree module on http://www.sai.msu.su/~megera/postgres/gist/ by the GiST team. On Mon, 3 Mar 2003, Rajesh Kumar Mallah wrote: > > the URL below writes somthing abt what u are looking for. > http://gppl.terminal.ru/index.eng.html > > > > regds > mallah. > > On Monday 03 March 2003 07:57 am, Eric wrote: > > Hi, > > > > How do I access hierarchical data under PostgreSQL? > > Does it have SQL command similar to Oracle's CONNECT BY? > > > > Any help is appreciated > > > > Eric > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > -- > > Regds > Mallah > > ---------------------------------------- > Rajesh Kumar Mallah, > Project Manager (Development) > Infocom Network Limited, New Delhi > phone: +91(11)6152172 (221) (L) ,9811255597 (M) > > Visit http://www.trade-india.com , > India's Leading B2B eMarketplace. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-210-8981112 fax: +30-210-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr
Eric wrote: > How do I access hierarchical data under PostgreSQL? > Does it have SQL command similar to Oracle's CONNECT BY? > In addition to all the other good suggestions, if you are using Postgres version 7.3.x, take a look at contrib/tablefunc for a function called connectby(). HTH, Joe
On Sun, 2003-03-02 at 21:27, Eric wrote: > Hi, > > How do I access hierarchical data under PostgreSQL? > Does it have SQL command similar to Oracle's CONNECT BY? > > Any help is appreciated > > Eric > Seems like it would be worth mentioning Joe Conway's "tablefunc" module in contrib, which has a connectby function. Robert Treat
Eric, > How do I access hierarchical data under PostgreSQL? > Does it have SQL command similar to Oracle's CONNECT BY? Joe Conway wrote CONNECT BY as a function. It's in /contrib in your PostgreSQL source for versions 7.3.0 and above, in /contrib/tablefunc I think. There is also a different tree implementation in /contrib/ltree. If that doesn't work for you, there are a number of different solutions to the tree structure problem, and sample implementations of all of them on PostgreSQL can be found through the web. Joe Celko covers three of them in "SQL for Smarties". -- Josh Berkus Aglio Database Solutions San Francisco