Postgres Pro
Downloads
Home   >   mailing lists

Thread: Hierarchical query's

Hierarchical query's

From
Cláudia Morgado
Date:
Hello!
 
Oracle has the option with the SQL CONECT BY statement to run through a hierarchical database with a single SQl-statement:

<!--SQL SELECT ms_id,ms_parent FROM messages CONNECT BY PRIOR ms_id = ms_parent START WITH ms_id = 1 -->
 
Result-set (example):
 
ms_id parent_id 1 1.1 1 1.1.1 1.1 1.1.2 1.1 1.1.3 1.1 1.2 1 1.2.1 1.2
 
etc
 
 
I need to do this in PostgreSql.
 
This is possible in the Postgre?
 
 
regards,
Cláudia Morgado and Carla Mello
 
 

Re: Hierarchical query's

From
Greg Stark
Date:
Cláudia Morgado <claudia.morgado@widesoft.com.br> writes:

> Oracle has the option with the SQL CONECT BY statement to run through a
> hierarchical database with a single SQl-statement:

Not really.

There is a package in the contrib directory called tablefunc that has a
function called connect_by that can do some things like this. I have no
experience with it and don't know if it would meet your needs.

--
greg

Re: Hierarchical query's

From
Pavel Stehule
Date:
Hello

PostgreSQL doesn't support h.q and I will not support this type of h.q.
Supported syntax will be like db2 - ANSI SQL 2kx. But you can try hier
patch - http://gppl.terminal.ru/readme.html.

This is a patch which allows PgSQL to perform hierarchical queries like
Oracle does.

Regards
Pavel Stehule


On Thu, 23 Oct 2003, [iso-8859-1] Cláudia Morgado wrote:

> Hello!
>
> Oracle has the option with the SQL CONECT BY statement to run through a hierarchical database with a single
SQl-statement:
>
> <!--SQL SELECT ms_id,ms_parent FROM messages CONNECT BY PRIOR ms_id = ms_parent START WITH ms_id = 1 -->
>
> Result-set (example):
>
> ms_id parent_id 1 1.1 1 1.1.1 1.1 1.1.2 1.1 1.1.3 1.1 1.2 1 1.2.1 1.2
>
> etc
>
>
> I need to do this in PostgreSql.
>
> This is possible in the Postgre?
>
>
> regards,
> Cláudia Morgado and Carla Mello
>
>
>

Re: Hierarchical query's

From
Cláudia Morgado
Date:
Thanks very much!

We will go to do the tests.

Regards,
Carla Mello and Cláudia Morgado


----- Original Message -----
From: "Pavel Stehule" <stehule@kix.fsv.cvut.cz>
To: "Cláudia Morgado" <claudia.morgado@widesoft.com.br>
Cc: <pgsql-general@postgresql.org>; <cmmello@wide.com.br>
Sent: Friday, October 24, 2003 3:32 AM
Subject: Re: [GENERAL] Hierarchical query's


> Hello
>
> PostgreSQL doesn't support h.q and I will not support this type of h.q.
> Supported syntax will be like db2 - ANSI SQL 2kx. But you can try hier
> patch - http://gppl.terminal.ru/readme.html.
>
> This is a patch which allows PgSQL to perform hierarchical queries like
> Oracle does.
>
> Regards
> Pavel Stehule
>
>
> On Thu, 23 Oct 2003, [iso-8859-1] Cláudia Morgado wrote:
>
> > Hello!
> >
> > Oracle has the option with the SQL CONECT BY statement to run through a
hierarchical database with a single SQl-statement:
> >
> > <!--SQL SELECT ms_id,ms_parent FROM messages CONNECT BY PRIOR ms_id =
ms_parent START WITH ms_id = 1 -->
> >
> > Result-set (example):
> >
> > ms_id parent_id 1 1.1 1 1.1.1 1.1 1.1.2 1.1 1.1.3 1.1 1.2 1 1.2.1 1.2
> >
> > etc
> >
> >
> > I need to do this in PostgreSql.
> >
> > This is possible in the Postgre?
> >
> >
> > regards,
> > Cláudia Morgado and Carla Mello
> >
> >
> >
>