Skip site navigation (1) Skip section navigation (2)

Re: Combinatorial problem

From: Marcus Engene <mengpg(at)engene(dot)se>
To: Srinivas Iyyer <srini_iyyer_bio(at)yahoo(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Combinatorial problem
Date: 2005-11-30 18:51:35
Message-ID: 438DF4B7.6080600@engene.se (view raw or flat)
Thread:
Lists: pgsql-novice
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(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
> 
> 


In response to

Responses

pgsql-novice by date

Next:From: Sean DavisDate: 2005-11-30 19:12:57
Subject: Re: Combinatorial problem
Previous:From: Luis SilvaDate: 2005-11-30 18:45:46
Subject: child fk problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group