From: | Greg Stark <gsstark(at)mit(dot)edu> |
---|---|
To: | Achilleus Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> |
Cc: | Bruno Wolff III <bruno(at)wolff(dot)to>, Victor Yegorov <viy(at)pirmabanka(dot)lv>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Dynamic SELECT condition |
Date: | 2003-03-02 14:19:08 |
Message-ID: | 87smu5kg0z.fsf@stark.dyndns.tv |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Achilleus Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> writes:
> On Sat, 1 Mar 2003, Bruno Wolff III wrote:
>
> > On Mon, Feb 24, 2003 at 19:53:00 +0200,
> > Victor Yegorov <viy(at)pirmabanka(dot)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.
You might want to look into the "Nested Sets" cookbook page too:
http://www.brasileiro.net:8080/postgres/cookbook/view-recipes.adp?section_id=310&format=long
This representation of hierarchies has a lot of nice properties including
being able to look up a whole subtree quickly. It makes it a bit of a pain to
modify the tree though.
> 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)
In the "int_agg" directory in contrib there are operators that do this, you
can say "WHERE parents *= 3". I prefer to keep level in a separate column
though.
I've find the features in the "int_agg" and "array" directories in the contrib
directory to be extremely useful. The only disadvantage is that the optimizer
doesn't have good basis for guessing the selectivity of the *= type operators.
GiST indexing of arrays is nice but depends on knowing which element of the
array you're looking for. If you're looking for 3 anywhere in the hierarchy I
don't think you can use the index.
--
greg
From | Date | Subject | |
---|---|---|---|
Next Message | Achilleus Mantzios | 2003-03-02 16:04:17 | Re: Dynamic SELECT condition |
Previous Message | Greg Stark | 2003-03-02 14:10:25 | Re: Inquiry From Form [pgsql] |