pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Hierarchical Queries
9+ messages / 8 participants
[nested] [flat]

* Hierarchical Queries
@ 2001-01-30 10:59  clemens schmuck <clemens.schmuck@ins.at>
  0 siblings, 0 replies; 9+ messages in thread

From: clemens schmuck @ 2001-01-30 10:59 UTC (permalink / raw)
  To: pgsql-sql

hi there,
in the database i'm currently trying to implement i make heavy use of self 
joins and therefore i do often need to make hierarchical queries. oracle 
offers the connect by clause to do this. how can i do this with, postgresql?

clemens




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Hierarchical queries
@ 2002-01-15 13:39  Paulo Rodrigues <prodrigues@vianetworks.pt>
  0 siblings, 2 replies; 9+ messages in thread

From: Paulo Rodrigues @ 2002-01-15 13:39 UTC (permalink / raw)
  To: pgsql-sql

Hello all,

how do you usually handle hierarchical (tree-like) queries? I'm sure 
someone else needed this already.

Thanks in advance,
Paulo

-- 
Paulo Rodrigues
Professional Services / MIS
prodrigues@vianetworks.pt

VIA NET.WORKS Portugal
http://www.vianetworks.pt
local touch global reach
-------------------------
Sabe quem tem acesso à rede da sua empresa?
http://security.vianetworks.pt/sig/




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Hierarchical queries
@ 2002-01-15 16:33  Josh Berkus <josh@agliodbs.com>
  parent: Paulo Rodrigues <prodrigues@vianetworks.pt>
  1 sibling, 0 replies; 9+ messages in thread

From: Josh Berkus @ 2002-01-15 16:33 UTC (permalink / raw)
  To: Paulo Rodrigues <prodrigues@vianetworks.pt>; pgsql-sql

Paulo,

> how do you usually handle hierarchical (tree-like) queries? I'm sure someone
>  else needed this already.

Yup!
Please see:
http://techdocs.postgresql.org/  
... which has at least one article on trees.

Also, Joe Celko's book, SQL for Smarties ( reviewed at
 http://techdocs.postgresql.org/bookreviews.php ) has chapters on the two main
 approaches to tree structures in fairly exhaustive detail.  Buy the book.  Or,
 the condensed version of some of that information was posted to the list by
 Joe Celko last summer; you can hunt for it in the archives.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Hierarchical queries
@ 2002-01-15 16:46  Paulo Rodrigues <prodrigues@vianetworks.pt>
  parent: Wei Weng <wweng@kencast.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Paulo Rodrigues @ 2002-01-15 16:46 UTC (permalink / raw)
  To: Wei Weng <wweng@kencast.com>; +Cc: pgsql-sql

Hi Wei,

An example would be a Bill of Materials; with Oracle I'd do

select level, item_id, parent_id, quantity from boms start with 
parent_id='A' connect by prior parent_id=item_id;

This is what I understand for a "tree-like" query. If that's the wrong 
name for it, it's my mistake, I'm sure.

Thanks,
Paulo

Wei Weng wrote:

> What is a "tree-like" queries? Or you mean a query returns you a hierarchy
> structure? (Thus tree like)
> 
> On Tue, 15 Jan 2002 13:39:11 +0000
> Paulo Rodrigues <prodrigues@vianetworks.pt> wrote:
> 
> 
>>Hello all,
>>
>>how do you usually handle hierarchical (tree-like) queries? I'm sure 
>>someone else needed this already.
>>
>>Thanks in advance,
>>Paulo
>>
>>-- 
>>Paulo Rodrigues
>>Professional Services / MIS
>>prodrigues@vianetworks.pt
>>
>>VIA NET.WORKS Portugal
>>http://www.vianetworks.pt
>>local touch global reach
>>-------------------------
>>http://security.vianetworks.pt/sig/
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 4: Don't 'kill -9' the postmaster
>>
>>
> 
> 


-- 
Paulo Rodrigues
Professional Services / MIS
prodrigues@vianetworks.pt

VIA NET.WORKS Portugal
http://www.vianetworks.pt
local touch global reach
-------------------------
Sabe quem tem acesso à rede da sua empresa?
http://security.vianetworks.pt/sig/




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Hierarchical queries
@ 2002-01-15 16:47  Wei Weng <wweng@kencast.com>
  parent: Paulo Rodrigues <prodrigues@vianetworks.pt>
  1 sibling, 1 reply; 9+ messages in thread

From: Wei Weng @ 2002-01-15 16:47 UTC (permalink / raw)
  To: Paulo Rodrigues <prodrigues@vianetworks.pt>; +Cc: pgsql-sql

What is a "tree-like" queries? Or you mean a query returns you a hierarchy
structure? (Thus tree like)

On Tue, 15 Jan 2002 13:39:11 +0000
Paulo Rodrigues <prodrigues@vianetworks.pt> wrote:

> Hello all,
> 
> how do you usually handle hierarchical (tree-like) queries? I'm sure 
> someone else needed this already.
> 
> Thanks in advance,
> Paulo
> 
> -- 
> Paulo Rodrigues
> Professional Services / MIS
> prodrigues@vianetworks.pt
> 
> VIA NET.WORKS Portugal
> http://www.vianetworks.pt
> local touch global reach
> -------------------------
> http://security.vianetworks.pt/sig/
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 


-- 
Wei Weng
Network Software Engineer
KenCast Inc.




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Hierarchical queries
@ 2002-01-15 16:58  Steve Boyle (Roselink) <boylesa@roselink.co.uk>
  parent: Paulo Rodrigues <prodrigues@vianetworks.pt>
  0 siblings, 0 replies; 9+ messages in thread

From: Steve Boyle \(Roselink\) @ 2002-01-15 16:58 UTC (permalink / raw)
  To: Paulo Rodrigues <prodrigues@vianetworks.pt>; Wei Weng <wweng@kencast.com>; +Cc: pgsql-sql

Paolo,

I think this is an item on the TODO list under exotic features. (recursive
unions).

You could get around this using plpgsql, follow the link below for examples:

http://www.brasileiro.net/postgres/cookbook/view-recipes.adp?section_id=2&fo
rmat=long

hih

steve boyle

----- Original Message -----
From: "Paulo Rodrigues" <prodrigues@vianetworks.pt>
To: "Wei Weng" <wweng@kencast.com>
Cc: <pgsql-sql@postgresql.org>
Sent: Tuesday, January 15, 2002 4:46 PM
Subject: Re: [SQL] Hierarchical queries


> Hi Wei,
>
> An example would be a Bill of Materials; with Oracle I'd do
>
> select level, item_id, parent_id, quantity from boms start with
> parent_id='A' connect by prior parent_id=item_id;
>
> This is what I understand for a "tree-like" query. If that's the wrong
> name for it, it's my mistake, I'm sure.
>
> Thanks,
> Paulo
>
> Wei Weng wrote:
>
> > What is a "tree-like" queries? Or you mean a query returns you a
hierarchy
> > structure? (Thus tree like)
> >
> > On Tue, 15 Jan 2002 13:39:11 +0000
> > Paulo Rodrigues <prodrigues@vianetworks.pt> wrote:
> >
> >
> >>Hello all,
> >>
> >>how do you usually handle hierarchical (tree-like) queries? I'm sure
> >>someone else needed this already.
> >>
> >>Thanks in advance,
> >>Paulo
> >>
> >>--
> >>Paulo Rodrigues
> >>Professional Services / MIS
> >>prodrigues@vianetworks.pt
> >>
> >>VIA NET.WORKS Portugal
> >>http://www.vianetworks.pt
> >>local touch global reach
> >>-------------------------
> >>http://security.vianetworks.pt/sig/
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 4: Don't 'kill -9' the postmaster
> >>
> >>
> >
> >
>
>
> --
> Paulo Rodrigues
> Professional Services / MIS
> prodrigues@vianetworks.pt
>
> VIA NET.WORKS Portugal
> http://www.vianetworks.pt
> local touch global reach
> -------------------------
> Sabe quem tem acesso à rede da sua empresa?
> http://security.vianetworks.pt/sig/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Hierarchical Queries
@ 2002-04-09 13:01  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Gautham S. Rao <gautam.rao@tatainfotech.com>
  1 sibling, 0 replies; 9+ messages in thread

From: Christopher Kings-Lynne @ 2002-04-09 13:01 UTC (permalink / raw)
  To: Gautham S. Rao <gautam.rao@tatainfotech.com>; +Cc: pgsql-sql

You can't - that syntax is not implemented in Postgres.

The only alternative is to try and write a stored procedure that uses a
FOR loop.  Remember that you can only return an open cursor from pl/pgsql,
not an actual recordset.

Chris

On Tue, 9 Apr 2002, Gautham S. Rao wrote:

> Hi,
>
> Can someone tell me how to write a hierarchical query in pgsql. What I
> want is something like the '...CONNECT BY PRIOR ... START WITH....'
> clause which is available in Oracle.
>
> Regards,
> Gautham.
>
>




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Hierarchical Queries
@ 2002-04-09 13:04  Gautham S. Rao <gautam.rao@tatainfotech.com>
  0 siblings, 2 replies; 9+ messages in thread

From: Gautham S. Rao @ 2002-04-09 13:04 UTC (permalink / raw)
  To: pgsql-sql

Hi,

Can someone tell me how to write a hierarchical query in pgsql. What I want is something like the '...CONNECT BY PRIOR ... START WITH....' clause which is available in Oracle.

Regards,
Gautham.

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Hierarchical Queries
@ 2002-04-09 15:34  Roberto Mello <rmello@cc.usu.edu>
  parent: Gautham S. Rao <gautam.rao@tatainfotech.com>
  1 sibling, 0 replies; 9+ messages in thread

From: Roberto Mello @ 2002-04-09 15:34 UTC (permalink / raw)
  To: Gautham S. Rao <gautam.rao@tatainfotech.com>; +Cc: pgsql-sql

On Tue, Apr 09, 2002 at 06:34:29PM +0530, Gautham S. Rao wrote:
> Hi,
> 
> Can someone tell me how to write a hierarchical query in pgsql. What I want is something like the '...CONNECT BY PRIOR ... START WITH....' clause which is available in Oracle.

There have been lots of messages discussing this topic on this list.
Search the archives and take a look at the "Porting from Oracle PL/SQL"
chapter of the documentation.

-Roberto

-- 
+----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+
  Roberto Mello - Computer Science, USU - http://www.brasileiro.net/ 
       http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer    
Ad astra, per ardua nostra.



^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2002-04-09 15:34 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-30 10:59 Hierarchical Queries clemens schmuck <clemens.schmuck@ins.at>
2002-01-15 13:39 Hierarchical queries Paulo Rodrigues <prodrigues@vianetworks.pt>
2002-01-15 16:33 ` Re: Hierarchical queries Josh Berkus <josh@agliodbs.com>
2002-01-15 16:47 ` Re: Hierarchical queries Wei Weng <wweng@kencast.com>
2002-01-15 16:46   ` Re: Hierarchical queries Paulo Rodrigues <prodrigues@vianetworks.pt>
2002-01-15 16:58     ` Re: Hierarchical queries Steve Boyle (Roselink) <boylesa@roselink.co.uk>
2002-04-09 13:04 Hierarchical Queries Gautham S. Rao <gautam.rao@tatainfotech.com>
2002-04-09 13:01 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-04-09 15:34 ` Roberto Mello <rmello@cc.usu.edu>

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