From: Jay A. Kreibich <jak@uiuc.edu>
To: Alexander Burbello <burbello3000@yahoo.com.br>
Cc: Lista Postgres <pgsql-admin@postgresql.org>
Subject: Re: Recursive use
Date: Fri, 6 Oct 2006 10:37:26 -0500
Message-ID: <20061006153726.GB15913@uiuc.edu> (raw)
In-Reply-To: <4523B23A.3000506@yahoo.com.br>
References: <4523B23A.3000506@yahoo.com.br>
On Wed, Oct 04, 2006 at 10:08:10AM -0300, Alexander Burbello scratched on the wall:
> Hi people,
>
> I need to know if Postgres do recursive search and how can I do!
> I will explain my problem.
>
>
> table COOPERATIVE
> code_cooperative int
> code_coo_father int
>
> I can have 3 level by business rules
>
> 1 - Father
> ----- 2 - Children
> --------- 3 - Grandchildren
>
>
> I would like to have a query asking who is father and granfather
> select grandfather, father from COOPERATIVE where COD_COOPERATIVE = 3
>
> Do the Postgres can solve this problem?
> Could anybody help me?
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.
Oracle supports these types of queries with their "START WITH ...
CONNECT BY" extensions to SELECT. In Oracle, hierarchical queries
also return a pseudo-column called "LEVEL" that is the depth of a node
in the tree. The syntax is fairly complete and allows all kinds of
queries up and down the tree. It is extremely useful for dealing with
self-referencing tables.
Alas, PostgreSQL does not support a similar set of extensions.
Although self-referencing tables are a bit of a design niche, they
show up all then time when translating traditional computer memory
structures and object trees into RDBMS storage systems. It would be
really cool if "START WITH ... CONNECT BY" or some similar set of
extensions was found in PostgreSQL.
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.
If you have a known structure (like the fact that your tree is
never any more than three levels deep) you can also join the table
to itself multiple times. This can get really confusing very quickly,
and is not an overly general solution, but it can be done in "pure" SQL
in a fairly straight forward (if not a bit complex) kind of way.
-j
--
Jay A. Kreibich | CommTech, Emrg Net Tech Svcs
jak@uiuc.edu | Campus IT & Edu Svcs
<http://www.uiuc.edu/~jak; | University of Illinois at U/C
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: jak@uiuc.edu, burbello3000@yahoo.com.br
Subject: Re: Recursive use
In-Reply-To: <20061006153726.GB15913@uiuc.edu>
References: <4523B23A.3000506@yahoo.com.br> <20061006153726.GB15913@uiuc.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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