pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDynamic SELECT condition
7+ messages / 5 participants
[nested] [flat]
* Dynamic SELECT condition
@ 2003-02-24 17:53 Victor Yegorov <viy@pirmabanka.lv>
0 siblings, 2 replies; 7+ messages in thread
From: Victor Yegorov @ 2003-02-24 17:53 UTC (permalink / raw)
To: pgsql-sql
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 | 2
1 | 3
3 | 4
3 | 5
4 | 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 | 4
3 | 5
4 | 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Dynamic SELECT condition
@ 2003-03-02 04:46 Bruno Wolff III <bruno@wolff.to>
parent: Victor Yegorov <viy@pirmabanka.lv>
1 sibling, 1 reply; 7+ messages in thread
From: Bruno Wolff III @ 2003-03-02 04:46 UTC (permalink / raw)
To: Victor Yegorov <viy@pirmabanka.lv>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Dynamic SELECT condition
@ 2003-03-02 14:19 Greg Stark <gsstark@mit.edu>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 7+ messages in thread
From: Greg Stark @ 2003-03-02 14:19 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: Bruno Wolff III <bruno@wolff.to>; Victor Yegorov <viy@pirmabanka.lv>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Dynamic SELECT condition
@ 2003-03-02 16:04 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 7+ messages in thread
From: Achilleus Mantzios @ 2003-03-02 16:04 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Victor Yegorov <viy@pirmabanka.lv>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Dynamic SELECT condition
@ 2003-03-03 07:27 Victor Yegorov <viy@pirmabanka.lv>
parent: Greg Stark <gsstark@mit.edu>
1 sibling, 0 replies; 7+ messages in thread
From: Victor Yegorov @ 2003-03-03 07:27 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: Achilleus Mantzios <achill@matrix.gatewaynet.com>; Bruno Wolff III <bruno@wolff.to>; pgsql-sql
* 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Dynamic SELECT condition
@ 2003-03-03 12:01 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Greg Stark <gsstark@mit.edu>
1 sibling, 0 replies; 7+ messages in thread
From: Achilleus Mantzios @ 2003-03-03 12:01 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: Bruno Wolff III <bruno@wolff.to>; Victor Yegorov <viy@pirmabanka.lv>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Dynamic SELECT condition
@ 2003-03-03 13:30 Oleg Samoylov <olleg@telecom.mipt.ru>
parent: Victor Yegorov <viy@pirmabanka.lv>
1 sibling, 0 replies; 7+ messages in thread
From: Oleg Samoylov @ 2003-03-03 13:30 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-03-03 13:30 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-24 17:53 Dynamic SELECT condition Victor Yegorov <viy@pirmabanka.lv>
2003-03-02 04:46 ` Bruno Wolff III <bruno@wolff.to>
2003-03-02 16:04 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-03-02 14:19 ` Greg Stark <gsstark@mit.edu>
2003-03-03 07:27 ` Victor Yegorov <viy@pirmabanka.lv>
2003-03-03 12:01 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-03-03 13:30 ` Oleg Samoylov <olleg@telecom.mipt.ru>
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