Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 59F729FA220 for ; Tue, 10 Oct 2006 12:15:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 26005-04 for ; Tue, 10 Oct 2006 12:15:45 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id EB2B99FA1A7 for ; Tue, 10 Oct 2006 12:15:44 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 3847556431; Tue, 10 Oct 2006 10:15:44 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Tue, 10 Oct 2006 10:15:42 -0500 Date: Tue, 10 Oct 2006 10:15:42 -0500 From: "Jim C. Nasby" To: "Jay A. Kreibich" Cc: Alexander Burbello , Lista Postgres Subject: Re: Recursive use Message-ID: <20061010151541.GF72517@nasby.net> References: <4523B23A.3000506@yahoo.com.br> <20061006153726.GB15913@uiuc.edu> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20061006153726.GB15913@uiuc.edu> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:061010:jak@uiuc.edu::ZoD9WM+7FoHIxMSJ:000005RAb X-Hashcash: 1:20:061010:burbello3000@yahoo.com.br::7SjzCOu78BNRW77f:000000000000 0000000000000000000000001xOc X-Hashcash: 1:20:061010:pgsql-admin@postgresql.org::b1l0f3BnTkuPqxA8:00000000000 0000000000000000000000000btR X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.054 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200610/65 X-Sequence-Number: 23368 On Fri, Oct 06, 2006 at 10:37:26AM -0500, Jay A. Kreibich wrote: > These are generally referred to as "Hierarchical Queries" and center > around the idea of a self-referencing table (such as an employee > table with a "manager" field that is a FK to another row in the same > table). This essentially makes a tree-like structure. > As pointed out by others, the most general way to deal with this in > PostgreSQL is to write PL/PgSQL (or some other language) functions > that can generate the specific queries you need. It isn't always > pretty, but it can be made to work for a specific set of queries. There are also other ways to represent this type of information without using hierarchical queries. Joe Celko presents two methods in SQL For Smarties. There's also the ltree module in contrib that might be of some use. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)