Re: Combinatorial problem - Mailing list pgsql-novice

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/