Re: Combinatorial problem

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/