Re: Combinatorial problem - Mailing list pgsql-novice

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/