pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHierarchical 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