## Re: Combinatorial problem

From: Marcus Engene Marcus Engene pgsql-novice(at)postgresql(dot)org Re: Combinatorial problem 2005-11-30 23:37:36 438E37C0.3070504@engene.se (view raw, whole thread or download thread mbox) 2005-11-24 15:53:32 from Peter George  2005-11-27 03:07:00 from   2005-11-29 23:44:10 from Srinivas Iyyer    2005-11-30 01:16:01 from Michael Fuhr    2005-11-30 11:46:03 from Sean Davis     2005-11-30 13:26:38 from Srinivas Iyyer      2005-11-30 13:43:12 from Sean Davis       2005-11-30 13:57:42 from Srinivas Iyyer        2005-11-30 14:09:48 from Sean Davis         2005-11-30 18:40:43 from Srinivas Iyyer          2005-11-30 18:51:35 from Marcus Engene           2005-11-30 19:12:57 from Sean Davis           2005-11-30 21:05:27 from Srinivas Iyyer            2005-11-30 21:32:52 from Marcus Engene             2005-11-30 23:37:36 from Marcus Engene        2005-11-30 14:35:58 from Marcus Engene pgsql-novice
```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

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

order by ct2.drug_id
in this subselect.