But why when you can do it with simple selects? Isn't this the output
create table comb_t as
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
array (select ct2.drug_id
from comb_t ct2
where ct2.the_arr = ct.the_arr)
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?
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
> CREATE FUNCTION extract_drugcomb() RETURNS text AS '
> drugids varchar;
> tarnames varchar;
> results TEXT;
> 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;
> ' LANGUAGE plpgsql;
> ERROR from SQL :
> drug-test=> \i loop.sql -- (That function was saved as
> 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
> 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"
>>>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
>>>these targets are affected only by drug m134 and
>>>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.
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
In response to
pgsql-novice by date
|Next:||From: Sean Davis||Date: 2005-11-30 19:12:57|
|Subject: Re: Combinatorial problem|
|Previous:||From: Luis Silva||Date: 2005-11-30 18:45:46|
|Subject: child fk problem|