Thread: Dynamic SELECT condition
Hello. I'd like to know, is it possible to use returned data set values as condition at runtime? Here comes the details. I have one table which links objects of various types into trees. Say, it has 5 rows: parent_id | child_id -----------+----------1 | 21 | 33 | 43 | 54 | 6 Each parent_id and child_id are primary keys in some other tables (don't matter which, with object details). Say, I'd like to show user a tree for object_id = 3 on the web. Is it possible to get the following result set with one SQL request: parent_id | child_id -----------+----------3 | 43 | 54 | 6 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. I hope you'll understand my problem. Thanks in advance. -- Victor Yegorov
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. contrib/tablefunc will probably allow you to do what you want.
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
Achilleus Mantzios <achill@matrix.gatewaynet.com> writes: > 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. 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
On 2 Mar 2003, Greg Stark wrote: > Achilleus Mantzios <achill@matrix.gatewaynet.com> writes: > > > 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. > > 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. What do you mean?? GiST indexing just indexes columns of type *array* for the &&,=,@,~,@@, etc.. operators. And if i am looking for 3 somewhere in an array column then i can definately use the index. See contrib/intarray for further details. Of course if the majority of queries just want the first direct father or the most ancient root grandfather or the median ancestor, there are ways to speed up such situations. > > -- > greg > ================================================================== 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
Victor Yegorov wrote: > I mean, I know the id of a root object, it's 3. If I'll extract with > condition parent_id =3D 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 =3D=3D this_level_child_id) > 0, select parent_i= > d, child_id. > > I hope you'll understand my problem. As I can undestand you problem, I can use function returned resultset for this purpose. -- Olleg Samoylov
* Greg Stark <gsstark@mit.edu> [02.03.2003 16:22]: > Achilleus Mantzios <achill@matrix.gatewaynet.com> writes: > > 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. Thanks a lot. Nested sets are pretty good. In my app trees arebeing updated quite often, so, instead of having a field field in a tree tale, I've combined nested sets with adjacency list. Now I have such table: tree_id int4 not null,branch_id int4 not null,leaf_id int4 not null,left int4 not null,right int4 not null,... Quite usefull, I think. -- Victor Yegorov