Re: Combinatorial problem

On 11/30/05 1:51 PM, "Marcus Engene" <mengpg(at)engene(dot)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?

Srinivas,

I agree with Marcus here. I thought (my misunderstanding) you needed all
combinations of targets and their corresponding combinations of drugs. If
you just want all the drugs that hit a specific target, then this is doable
with straight SQL.

Thanks, Marcus for clarifying for us.

Sean

> 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(at)mail(dot)nih(dot)gov> wrote:
>>
>>
>>> On 11/30/05 8:57 AM, "Srinivas Iyyer"
>>> <srini_iyyer_bio(at)yahoo(dot)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
>