Re: Combinatorial problem - Mailing list 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@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
>
>