Re: 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 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

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(at)somewhere(dot)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

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

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(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr