Re: Combinatorial problem - Mailing list pgsql-novice

bond=# \d comb
        Table "public.comb"
   Column   |  Type   | Modifiers
-----------+---------+-----------
  drug_id   | integer |
  target_id | integer |

bond=# \d target
               Table "public.target"
    Column    |         Type          | Modifiers
-------------+-----------------------+-----------
  target_id   | integer               |
  target_name | character varying(10) |

bond=# \d drug
               Table "public.drug"
   Column   |         Type          | Modifiers
-----------+-----------------------+-----------
  drug_id   | integer               |
  drug_name | character varying(10) |

bond=# select * from comb;
  drug_id | target_id
---------+-----------
        1 |         1
        1 |         2
        2 |         2
        3 |         2
        4 |         1
        4 |         2
(6 rows)

bond=# select * from drug;
  drug_id | drug_name
---------+-----------
        1 | m1
        2 | m2
        3 | m3
        4 | m4
(4 rows)

bond=# select * from target;
  target_id | target_name
-----------+-------------
          1 | t1
          2 | t2
          3 | t3
          4 | t4
(4 rows)

bond=# create view comb_v as
bond-# select
bond-#  d.drug_name as drug_id
bond-# ,t.target_name as target_id
bond-# from
bond-#  comb c
bond-# ,drug d
bond-# ,target t
bond-# where
bond-#  d.drug_id = c.drug_id AND
bond-#  t.target_id = c.target_id;
CREATE VIEW
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
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
bond-#    ,the_arr
bond-# from
bond-# comb_t ct
bond-# ;
  drug_arr | the_arr
----------+---------
  {m1,m4}  | {t1,t2}
  {m2,m3}  | {t2}
(2 rows)

Here we do the same thing. We make an array of all the drug_ids that has
this drugid:s targets. Since we have an array, instead of some variable
number of columns, we can just use array = array to see if they match.

Since we use the view (comb_v) we get it in plaintext.

If you wanted the dependencies the other way around, basically swap drug
with target in the selects.

Hope this helps,
Marcus



Srinivas Iyyer wrote:
> Hi Marcus,
> Thanks for your help.
> Could you please explain the code  because i did not
> understand the code and I am unable to pull out names.
> I am not that advanced user.
>
> Thank you.
>
> --- Marcus Engene <mengpg@engene.se> wrote:
>
>
>>But why when you can do it with simple selects?
>>Isn't this the output
>>you wanted?
>>
>>create table comb_t as
>>select *
>>from (
>>select distinct
>>drug_id,
>>array (select cb2.target_id
>>        from comb cb2
>>        where cb2.drug_id = cb.drug_id) as the_arr
>>from comb cb
>>) as foo
>>order by the_arr, drug_id
>>
>>
>>select distinct
>>     array (select ct2.drug_id
>>            from comb_t ct2
>>            where ct2.the_arr = ct.the_arr)
>>    ,the_arr
>>from
>>comb_t ct
>>
>>If you want it as plaintext I suggest you make a
>>view called comb_v,
>>that shows comb but with the names instead of id:s
>>and columns named as
>>in comb and replace all occurences of comb with
>>comb_v in the first
>>select. Then you get what you asked for in the first
>>mail, right?
>>
>>Best regards,
>>Marcus
>>
>>
>>Srinivas Iyyer wrote:
>>
>>>Dear Sean,
>>>Instead of going the other way, I tried to pullout
>>
>>the
>>
>>>drugs for each target.
>>>The logic:
>>>For each target_id in the target table
>>>    match the target_id with drug_id in comb table
>>>    pull out the drug_name from drug_id.
>>>
>>>The output I wanted is:
>>>
>>>Target     Drug name \t drug name \t drug name \t
>>
>>drug
>>
>>>name
>>>
>>>
>>>Function:
>>>
>>>CREATE FUNCTION extract_drugcomb() RETURNS text AS
>>
>>'
>>
>>>        DECLARE
>>>                drugids varchar;
>>>                tarnames varchar;
>>>                results TEXT;
>>>        BEGIN
>>>                FOR i in 0..20000 LOOP
>>>
>>>                  SELECT into tarnames target_name
>>>FROM target where target_id = i;
>>>                  SELECT  DISTINCT drug_name INTO
>>>drugids from drug,target,comb where drug.drug_id =
>>>comb.drug_id and comb.target_id = i;
>>>                  results = results ||"\n"
>>>||tarnames||"\t"|| mirids||"\n";
>>>                END LOOP;
>>>        RETURN results;
>>>        END;
>>> ' LANGUAGE plpgsql;
>>>
>>>
>>>ERROR from SQL :
>>>
>>>drug-test=> \i loop.sql -- (That function was
>>
>>saved as
>>
>>>loop.sql)
>>>CREATE FUNCTION
>>>drug-test=> SELECT extract_drugcomb();
>>>ERROR:  column "
>>>" does not exist
>>>CONTEXT:  SQL statement "SELECT   $1  ||"
>>>" || $2 ||"     "||  $3 ||"
>>>""
>>>PL/pgSQL function "extract_drugcomb" line 10 at
>>>assignment
>>>drug-test=>
>>>
>>>
>>>
>>>Could you please help me where the error and
>>
>>problem
>>
>>>in this function is.
>>>
>>>Thanks again.
>>>
>>>--- Sean Davis <sdavis2@mail.nih.gov> wrote:
>>>
>>>
>>>
>>>>On 11/30/05 8:57 AM, "Srinivas Iyyer"
>>>><srini_iyyer_bio@yahoo.com> wrote:
>>>>
>>>>
>>>>
>>>>>Sorry for being unclear.
>>>>>
>>>>>As of now from the whole mess of data, I do not
>>>>
>>>>know
>>>>
>>>>
>>>>>which targets are acted unique by a drug.
>>>>>
>>>>>For instance, Drug m134 is acting only on target
>>>>
>>>>T432,
>>>>
>>>>
>>>>>T438,T654.
>>>>>these targets are affected only by drug m134 and
>>>>>nothing else.
>>>>>
>>>>>Similarly, two drugs, m23 and m45 are acting on a
>>>>>group of targets, T987, T12,T334, T543.
>>>>>
>>>>>m2,m3 and m5 are acting on T439,3421,T4568,T31
>>>>>m2,m3 and m8 are acrting on T124, T1334,T446,T98.
>>>>>m5,m8 and m12 are acting on T088,T898,T329.
>>>>>
>>>>>Now, I have no idea what combination of drugs are
>>>>>acting on set of targets.
>>>>>
>>>>>IS there any way to get set of drugs and set of
>>>>>targets that happening in the data.
>>>>
>>>>I see your problem.  I don't see how to do this
>>
>>off
>>
>>>>the top of my head.
>>>>However, I do agree that you will not likely be
>>
>>able
>>
>>>>to do this with
>>>>straight SQL, as you suspect.  You might try
>>
>>posting
>>
>>>>to pgsql-sql list, as
>>>>well, if you don't get an answer here.
>>>>
>>>>Sean
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>>__________________________________
>>>Yahoo! Music Unlimited
>>>Access over 1 million songs. Try it free.
>>>http://music.yahoo.com/unlimited/
>>>
>>>---------------------------(end of
>>
>>broadcast)---------------------------
>>
>>>TIP 4: Have you searched our list archives?
>>>
>>>               http://archives.postgresql.org
>>>
>>>
>>
>>
>>---------------------------(end of
>>broadcast)---------------------------
>>TIP 4: Have you searched our list archives?
>>
>>               http://archives.postgresql.org
>>
>
>
>
>
>
> __________________________________
> Yahoo! Music Unlimited
> Access over 1 million songs. Try it free.
> http://music.yahoo.com/unlimited/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>