Instead of going the other way, I tried to pullout the
drugs for each target.
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
CREATE FUNCTION extract_drugcomb() RETURNS text AS '
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"
' LANGUAGE plpgsql;
ERROR from SQL :
drug-test=> \i loop.sql -- (That function was saved as
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
Could you please help me where the error and problem
in this function is.
--- 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
> > which targets are acted unique by a drug.
> > For instance, Drug m134 is acting only on target
> > 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.
Yahoo! Music Unlimited
Access over 1 million songs. Try it free.
In response to
pgsql-novice by date
|Next:||From: Luis Silva||Date: 2005-11-30 18:45:46|
|Subject: child fk problem|
|Previous:||From: Marcus Engene||Date: 2005-11-30 14:35:58|
|Subject: Re: Combinatorial problem|