Re: Recursive use

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(at)uiuc(dot)edu | Campus IT & Edu Svcs
<http://www.uiuc.edu/~jak> | University of Illinois at U/C