pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuerying Hierarchical Data
8+ messages / 8 participants
[nested] [flat]
* Querying Hierarchical Data
@ 2003-03-03 02:27 Eric <someone@somewhere.com>
0 siblings, 6 replies; 8+ messages in thread
From: Eric @ 2003-03-03 02:27 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Querying Hierarchical Data
@ 2003-03-03 15:31 Lex Berezhny <LBerezhny@DevIS.com>
parent: Eric <someone@somewhere.com>
5 siblings, 0 replies; 8+ messages in thread
From: Lex Berezhny @ 2003-03-03 15:31 UTC (permalink / raw)
To: Eric <someone@somewhere.com>; +Cc: pgsql-sql
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 FROM stack 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;
END IF;
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 | valja
11 | 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Querying Hierarchical Data
@ 2003-03-03 15:40 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Eric <someone@somewhere.com>
5 siblings, 1 reply; 8+ messages in thread
From: Rajesh Kumar Mallah @ 2003-03-03 15:40 UTC (permalink / raw)
To: Eric <someone@somewhere.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Querying Hierarchical Data
@ 2003-03-03 16:34 Victor Yegorov <viy@pirmabanka.lv>
parent: Eric <someone@somewhere.com>
5 siblings, 0 replies; 8+ messages in thread
From: Victor Yegorov @ 2003-03-03 16:34 UTC (permalink / raw)
To: pgsql-sql
* 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Querying Hierarchical Data
@ 2003-03-03 18:25 Joe Conway <mail@joeconway.com>
parent: Eric <someone@somewhere.com>
5 siblings, 0 replies; 8+ messages in thread
From: Joe Conway @ 2003-03-03 18:25 UTC (permalink / raw)
To: Eric <someone@somewhere.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Querying Hierarchical Data
@ 2003-03-03 18:34 Robert Treat <xzilla@users.sourceforge.net>
parent: Eric <someone@somewhere.com>
5 siblings, 0 replies; 8+ messages in thread
From: Robert Treat @ 2003-03-03 18:34 UTC (permalink / raw)
To: Eric <someone@somewhere.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Querying Hierarchical Data
@ 2003-03-03 18:34 Josh Berkus <josh@agliodbs.com>
parent: Eric <someone@somewhere.com>
5 siblings, 0 replies; 8+ messages in thread
From: Josh Berkus @ 2003-03-03 18:34 UTC (permalink / raw)
To: Eric <someone@somewhere.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Querying Hierarchical Data
@ 2003-03-03 20:57 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 0 replies; 8+ messages in thread
From: Achilleus Mantzios @ 2003-03-03 20:57 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: Eric <someone@somewhere.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-03-03 20:57 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-03 02:27 Querying Hierarchical Data Eric <someone@somewhere.com>
2003-03-03 15:31 ` Lex Berezhny <LBerezhny@DevIS.com>
2003-03-03 15:40 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-03-03 20:57 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-03-03 16:34 ` Victor Yegorov <viy@pirmabanka.lv>
2003-03-03 18:25 ` Joe Conway <mail@joeconway.com>
2003-03-03 18:34 ` Robert Treat <xzilla@users.sourceforge.net>
2003-03-03 18:34 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox