Обсуждение: Hierarchical query's
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 -->
<!--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
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
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 > > >
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 > > > > > > >