Re: Dynamic SELECT condition

On Sat, 1 Mar 2003, Bruno Wolff III wrote:

> On Mon, Feb 24, 2003 at 19:53:00 +0200,
>   Victor Yegorov <viy@pirmabanka.lv> wrote:
> >
> > I mean, I know the id of a root object, it's 3. If I'll extract with
> > condition parent_id = 3, then I'll get only 2 rows. I'd like to have a
> > recursive result set - while there are records in the table for which
> > count(next_level_parent_id == this_level_child_id) > 0, select parent_id, child_id.

Hi Victor,

i have done that using arrays to describe the path from a node to its root
ancestor starting from the first direct ancestor.

Then the table would look like:

id | parents
1  | null
2  | {1}
3  | {1}
4  | {3,1}
5  | {3,1}
6  | {4,3,1}

Now if you want to know for instance the immediate (direct) father of id=2
it
is just parents[1].
If you want to know the direct kids (4,5 in the example) of id=3, then it
is
select from table where itoar(3) ~ parents and level(parents)=2
(itoar and level are simple C functions that convert an int4 to its
corresponding 1x1 array and calculate the length of the array
, respectively)
if you want all the subtree under node id=3, then you do
select from table where itoar(3) ~ parents order by level(parents)

Note that there are techniques to speed up the above queries.
Also note that you can use indexes on functions on arrays, and indexes
on arrays (contrib/intarray by the GiST team is a must)

>
> contrib/tablefunc will probably allow you to do what you want.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-10-8981112
fax:    +30-10-8981877
email:  achill@matrix.gatewaynet.com       mantzios@softlab.ece.ntua.gr