Marcus Engene wrote:
Wops. I just came to think of something. The subselects cunstructing the
array should have an order by so each array with the same elements will
be constructed the same way. Otherwise there is no guarrante that
distinct will work.
> bond=# create table comb_t as
> bond-# select *
> bond-# from (
> bond(# select distinct
> bond(# drug_id,
> bond(# array (select cb2.target_id
> bond(# from comb_v cb2
> bond(# where cb2.drug_id = cb.drug_id) as the_arr
Add a
order by cb2.target_id
in this subselect.
> bond(# from comb_v cb
> bond(# ) as foo
> bond-# order by the_arr, drug_id;
> SELECT
> bond=# select * from comb_t;
> drug_id | the_arr
> ---------+---------
> m1 | {t1,t2}
> m4 | {t1,t2}
> m2 | {t2}
> m3 | {t2}
> (4 rows)
>
> Ok, now we created a temporary table where the_arr is the set of targets
> each drug has. Note, this is an array. I use an array here because sql
> isn't really suited for dealing with varying number of columns.
>
> It's very often a good thing to use a temporary table like this. It
> simplifies many problems. Sometimes you can use a view instead, the
> principle is the same.
>
> When you select, instead of just writing a column you can write a select
> statement as well. And (ofcourse, otherwise there would be no point) you
> can use tables in the "big select" in the subselect. Here we create an
> array of all the targets this drug_id affect.
>
> bond=# select distinct
> bond-# array (select ct2.drug_id
> bond(# from comb_t ct2
> bond(# where ct2.the_arr = ct.the_arr) as drug_arr
add a
order by ct2.drug_id
in this subselect.
Sorry about this.
Marcus