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

Re: Combinatorial problem

From: Srinivas Iyyer <srini_iyyer_bio(at)yahoo(dot)com>
To: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Combinatorial problem
Date: 2005-11-30 18:40:43
Message-ID: 20051130184043.115.qmail@web31601.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: 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(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/

In response to

Responses

pgsql-novice by date

Next:From: Luis SilvaDate: 2005-11-30 18:45:46
Subject: child fk problem
Previous:From: Marcus EngeneDate: 2005-11-30 14:35:58
Subject: Re: Combinatorial problem

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