Thread: 8.0.2 Install Problems on Win XP
Downloaded postgresql-8.0.2.zip from http://www.postgresql.org/ftp/ binary/v8.0.2/win32/ , attempted install on Windows XP. Received following error immediately after the Postgresql installer language selection screen: "The installation package could not be opened. Verify that the package exists and that you can access it, or contact the application vendor to ensure that this is a valid Windows installer package." Anyone come across this before? Any thoughts? TIA P -- Logicalware Ltd, tel +44 (0)131 273 5130, Stuart House, Eskmills, Station Road, Musselburgh EH21 7PQ, UK http://www.logicalware.com/
Peter, You can see if the WinXP PostgreSQL tutorial found at... http://www.geocities.com/operationsengineer1/ helps out. Best, OE1 --- Peter George <peter@logicalware.com> wrote: > Downloaded postgresql-8.0.2.zip from > http://www.postgresql.org/ftp/ > binary/v8.0.2/win32/ , attempted install on Windows > XP. > > Received following error immediately after the > Postgresql installer > language selection screen: > > "The installation package could not be opened. > Verify that the > package exists and that you can access it, or > contact the application > vendor to ensure that this is a valid Windows > installer package." > > Anyone come across this before? Any thoughts? > > TIA > > P > > -- > Logicalware Ltd, tel +44 (0)131 273 5130, > Stuart House, Eskmills, Station Road, Musselburgh > EH21 7PQ, UK > http://www.logicalware.com/ > > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will > ignore your desire to > choose an index scan if your joining column's > datatypes do not > match > __________________________________ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com
Dear Group, I have a piece of data that is more relational. The following is an illustrative example: Drug Target m1 T1 m2 T1 m3 T2 m2 T3 m2 T2 m4 T4 m1 T4 m5 T1 m5 T5 m6 T3 m6 T4 m6 T5 m6 T6 .. .. .. .. m3200 T9000 In this example m1 drug targets are T1 and T4. m2 drug targets are T1,T2,T3. Likewise, I have over 3000 drugs and over 9000 targets. The question that I asked is: For a given target what are the unique Drugs (such as m1,m4,m6) For a group of related drugs (m1,m2 and m3) what are the targets. I am novice programmer in python and R-statistical language. I attempted in both these and it is proving very very difficult. I chose a database approach. I created 3 tables: A drug table 'Drug' A target table 'Target' A combined table with mapping information 'Comb' (in comb table I have the mapping information as illustrated in the above example) Drug: -------------------- drug_id | drug_name -------------------- 1 | m1 2 | m2 3 | m3 Here drug_id is primary key and made as serial. drug_name is varchar(10) Target: -------------------- target_id | target_name -------------------- 1 | T1 2 | T2 3 | T3 target_id is 'Serial' primary key Target_name is varchar(20) Comb: -------------------- drug_id | target_ID -------------------- 1 | 1 2 | 1 1 | 2 1 | 4 1 | 432 Now my question is how can I ask SQL the question: For T3 what are the Drugs ? For T4 what are the drugs? For a group of drugs m1,m3,m5 what are the targets? Can any one please help me. Thanks I am stuck and my progress is depended on ur kind suggestion. thanks again. Sr __________________________________ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com
On Tue, Nov 29, 2005 at 03:44:10PM -0800, Srinivas Iyyer wrote: > Now my question is how can I ask SQL the question: > > For T3 what are the Drugs ? > For T4 what are the drugs? > > For a group of drugs m1,m3,m5 what are the targets? I think you're looking for a join. These parts of the PostgreSQL documentation should help: http://www.postgresql.org/docs/8.1/interactive/tutorial-join.html http://www.postgresql.org/docs/8.1/interactive/queries-table-expressions.html You could also google for words like "sql tutorial join". -- Michael Fuhr
On 11/29/05 6:44 PM, "Srinivas Iyyer" <srini_iyyer_bio@yahoo.com> wrote:
> Dear Group,
> I have a piece of data that is more relational.
>
> The following is an illustrative example:
>
>
> Drug Target
> m1 T1
> m2 T1
> m3 T2
> m2 T3
> m2 T2
> m4 T4
> m1 T4
> m5 T1
> m5 T5
> m6 T3
> m6 T4
> m6 T5
> m6 T6
> .. ..
> .. ..
> m3200 T9000
>
>
> In this example m1 drug targets are T1 and T4.
>
> m2 drug targets are T1,T2,T3.
>
> Likewise, I have over 3000 drugs and over 9000
> targets.
>
>
> The question that I asked is:
>
> For a given target what are the unique Drugs (such as
> m1,m4,m6)
> For a group of related drugs (m1,m2 and m3) what are
> the targets.
>
>
> I am novice programmer in python and R-statistical
> language. I attempted in both these and it is proving
> very very difficult.
>
> I chose a database approach.
>
> I created 3 tables:
>
> A drug table 'Drug'
> A target table 'Target'
> A combined table with mapping information 'Comb'
>
> (in comb table I have the mapping information as
> illustrated in the above example)
>
>
> Drug:
> --------------------
> drug_id | drug_name
> --------------------
> 1 | m1
> 2 | m2
> 3 | m3
>
>
> Here drug_id is primary key and made as serial.
> drug_name is varchar(10)
> Target:
>
> --------------------
> target_id | target_name
> --------------------
> 1 | T1
> 2 | T2
> 3 | T3
>
> target_id is 'Serial' primary key
> Target_name is varchar(20)
>
>
> Comb:
>
> --------------------
> drug_id | target_ID
> --------------------
> 1 | 1
> 2 | 1
> 1 | 2
> 1 | 4
> 1 | 432
>
>
>
> Now my question is how can I ask SQL the question:
>
> For T3 what are the Drugs ?
select drug_name
from drug, target, comb
where drug.drug_id=comb.drug_id and
comb.target_id=target.target_id and
target_name='T3';
> For T4 what are the drugs?
>
> For a group of drugs m1,m3,m5 what are the targets?
select target_name
from drug, target, comb
where drug.drug_id=comb.drug_id and
comb.target_id=target.target_id and
drug_name in ('m1','m3','m5');
Three points: In postgresql, capital letters in column names and table
names are folded to lower case if you don't use "" around them ALL THE TIME.
Therefore, I tend to not use any capitals. Second, there are a number of
good SQL tutorials online that will be quite helpful for learning how to
squeeze information from your database. Lastly, if you are an R user, be
sure to check out RdbiPgSQL, available via the BioConductor site, for
interfacing R with Postgres.
Sean
Dear Sean,
Thank you for your help.
I have another question and sorry to bother you. If I
do not know the pairing before hand for drugs (such as
m1,m3 and m5) how can I define any random combination.
should it have to be solved using PL/PgSQL or just
through SQL queries?
thanks
>
> select target_name
> from drug, target, comb
> where drug.drug_id=comb.drug_id and
> comb.target_id=target.target_id and
> drug_name in ('m1','m3','m5');
>
>
> Three points: In postgresql, capital letters in
> column names and table
> names are folded to lower case if you don't use ""
> around them ALL THE TIME.
> Therefore, I tend to not use any capitals. Second,
> there are a number of
> good SQL tutorials online that will be quite helpful
> for learning how to
> squeeze information from your database. Lastly, if
> you are an R user, be
> sure to check out RdbiPgSQL, available via the
> BioConductor site, for
> interfacing R with Postgres.
>
> Sean
>
>
__________________________________
Yahoo! Music Unlimited
Access over 1 million songs. Try it free.
http://music.yahoo.com/unlimited/
On 11/30/05 8:26 AM, "Srinivas Iyyer" <srini_iyyer_bio@yahoo.com> wrote: > Dear Sean, > Thank you for your help. > > I have another question and sorry to bother you. If I > do not know the pairing before hand for drugs (such as > m1,m3 and m5) how can I define any random combination. > should it have to be solved using PL/PgSQL or just > through SQL queries? I'm not sure what you want to do--can you elaborate? Do you mean that you want to choose the drugs via some interface and then do the query with those drugs? Or something else? I'm not sure how PL/PgSQL would come into it. Sean
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. What I mean to ask is, does it need a program such as Pl/pgsql to solve. I am nervous, because if it has to be done like that it is going to take long long time because, I have to learn it first. Thank you. --- Sean Davis <sdavis2@mail.nih.gov> wrote: > On 11/30/05 8:26 AM, "Srinivas Iyyer" > <srini_iyyer_bio@yahoo.com> wrote: > > > Dear Sean, > > Thank you for your help. > > > > I have another question and sorry to bother you. > If I > > do not know the pairing before hand for drugs > (such as > > m1,m3 and m5) how can I define any random > combination. > > should it have to be solved using PL/PgSQL or just > > through SQL queries? > > I'm not sure what you want to do--can you elaborate? > Do you mean that you > want to choose the drugs via some interface and then > do the query with those > drugs? Or something else? I'm not sure how > PL/PgSQL would come into it. > > Sean > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map > settings > __________________________________ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com
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
Srinivas Iyyer 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.
>
> What I mean to ask is, does it need a program such as
> Pl/pgsql to solve. I am nervous, because if it has to
> be done like that it is going to take long long time
> because, I have to learn it first.
>
> Thank you.
Hi Srinivas,
Perhaps this is what you want:
select *
from (
select distinct
drug_id,
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
Best regards,
Marcus
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/
But why when you can do it with simple selects? Isn't this the output
you wanted?
create table comb_t as
select *
from (
select distinct
drug_id,
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
select distinct
array (select ct2.drug_id
from comb_t ct2
where ct2.the_arr = ct.the_arr)
,the_arr
from
comb_t ct
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?
Best regards,
Marcus
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
> 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/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
On 11/30/05 1:51 PM, "Marcus Engene" <mengpg@engene.se> wrote: > But why when you can do it with simple selects? Isn't this the output > you wanted? > > create table comb_t as > select * > from ( > select distinct > drug_id, > 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 > > > select distinct > array (select ct2.drug_id > from comb_t ct2 > where ct2.the_arr = ct.the_arr) > ,the_arr > from > comb_t ct > > 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, I agree with Marcus here. I thought (my misunderstanding) you needed all combinations of targets and their corresponding combinations of drugs. If you just want all the drugs that hit a specific target, then this is doable with straight SQL. Thanks, Marcus for clarifying for us. Sean > 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 >> 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/ >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 4: Have you searched our list archives? >> >> http://archives.postgresql.org >> >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >
Hi Marcus, Thanks for your help. Could you please explain the code because i did not understand the code and I am unable to pull out names. I am not that advanced user. Thank you. --- Marcus Engene <mengpg@engene.se> wrote: > But why when you can do it with simple selects? > Isn't this the output > you wanted? > > create table comb_t as > select * > from ( > select distinct > drug_id, > 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 > > > select distinct > array (select ct2.drug_id > from comb_t ct2 > where ct2.the_arr = ct.the_arr) > ,the_arr > from > comb_t ct > > 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? > > Best regards, > Marcus > > > 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 > > 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/ > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 4: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > __________________________________ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/
bond=# \d comb
Table "public.comb"
Column | Type | Modifiers
-----------+---------+-----------
drug_id | integer |
target_id | integer |
bond=# \d target
Table "public.target"
Column | Type | Modifiers
-------------+-----------------------+-----------
target_id | integer |
target_name | character varying(10) |
bond=# \d drug
Table "public.drug"
Column | Type | Modifiers
-----------+-----------------------+-----------
drug_id | integer |
drug_name | character varying(10) |
bond=# select * from comb;
drug_id | target_id
---------+-----------
1 | 1
1 | 2
2 | 2
3 | 2
4 | 1
4 | 2
(6 rows)
bond=# select * from drug;
drug_id | drug_name
---------+-----------
1 | m1
2 | m2
3 | m3
4 | m4
(4 rows)
bond=# select * from target;
target_id | target_name
-----------+-------------
1 | t1
2 | t2
3 | t3
4 | t4
(4 rows)
bond=# create view comb_v as
bond-# select
bond-# d.drug_name as drug_id
bond-# ,t.target_name as target_id
bond-# from
bond-# comb c
bond-# ,drug d
bond-# ,target t
bond-# where
bond-# d.drug_id = c.drug_id AND
bond-# t.target_id = c.target_id;
CREATE VIEW
bond=# create table comb_t as
bond-# select *
bond-# from (
bond(# select distinct
bond(# drug_id,
bond(# array (select cb2.target_id
bond(# from comb_v cb2
bond(# where cb2.drug_id = cb.drug_id) as the_arr
bond(# from comb_v cb
bond(# ) as foo
bond-# order by the_arr, drug_id;
SELECT
bond=# select * from comb_t;
drug_id | the_arr
---------+---------
m1 | {t1,t2}
m4 | {t1,t2}
m2 | {t2}
m3 | {t2}
(4 rows)
Ok, now we created a temporary table where the_arr is the set of targets
each drug has. Note, this is an array. I use an array here because sql
isn't really suited for dealing with varying number of columns.
It's very often a good thing to use a temporary table like this. It
simplifies many problems. Sometimes you can use a view instead, the
principle is the same.
When you select, instead of just writing a column you can write a select
statement as well. And (ofcourse, otherwise there would be no point) you
can use tables in the "big select" in the subselect. Here we create an
array of all the targets this drug_id affect.
bond=# select distinct
bond-# array (select ct2.drug_id
bond(# from comb_t ct2
bond(# where ct2.the_arr = ct.the_arr) as drug_arr
bond-# ,the_arr
bond-# from
bond-# comb_t ct
bond-# ;
drug_arr | the_arr
----------+---------
{m1,m4} | {t1,t2}
{m2,m3} | {t2}
(2 rows)
Here we do the same thing. We make an array of all the drug_ids that has
this drugid:s targets. Since we have an array, instead of some variable
number of columns, we can just use array = array to see if they match.
Since we use the view (comb_v) we get it in plaintext.
If you wanted the dependencies the other way around, basically swap drug
with target in the selects.
Hope this helps,
Marcus
Srinivas Iyyer wrote:
> Hi Marcus,
> Thanks for your help.
> Could you please explain the code because i did not
> understand the code and I am unable to pull out names.
> I am not that advanced user.
>
> Thank you.
>
> --- Marcus Engene <mengpg@engene.se> wrote:
>
>
>>But why when you can do it with simple selects?
>>Isn't this the output
>>you wanted?
>>
>>create table comb_t as
>>select *
>>from (
>>select distinct
>>drug_id,
>>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
>>
>>
>>select distinct
>> array (select ct2.drug_id
>> from comb_t ct2
>> where ct2.the_arr = ct.the_arr)
>> ,the_arr
>>from
>>comb_t ct
>>
>>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?
>>
>>Best regards,
>>Marcus
>>
>>
>>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
>>
>>>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/
>>>
>>>---------------------------(end of
>>
>>broadcast)---------------------------
>>
>>>TIP 4: Have you searched our list archives?
>>>
>>> http://archives.postgresql.org
>>>
>>>
>>
>>
>>---------------------------(end of
>>broadcast)---------------------------
>>TIP 4: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>>
>
>
>
>
>
> __________________________________
> Yahoo! Music Unlimited
> Access over 1 million songs. Try it free.
> http://music.yahoo.com/unlimited/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>
Marcus Engene wrote:
Wops. I just came to think of something. The subselects cunstructing the
array should have an order by so each array with the same elements will
be constructed the same way. Otherwise there is no guarrante that
distinct will work.
> bond=# create table comb_t as
> bond-# select *
> bond-# from (
> bond(# select distinct
> bond(# drug_id,
> bond(# array (select cb2.target_id
> bond(# from comb_v cb2
> bond(# where cb2.drug_id = cb.drug_id) as the_arr
Add a
order by cb2.target_id
in this subselect.
> bond(# from comb_v cb
> bond(# ) as foo
> bond-# order by the_arr, drug_id;
> SELECT
> bond=# select * from comb_t;
> drug_id | the_arr
> ---------+---------
> m1 | {t1,t2}
> m4 | {t1,t2}
> m2 | {t2}
> m3 | {t2}
> (4 rows)
>
> Ok, now we created a temporary table where the_arr is the set of targets
> each drug has. Note, this is an array. I use an array here because sql
> isn't really suited for dealing with varying number of columns.
>
> It's very often a good thing to use a temporary table like this. It
> simplifies many problems. Sometimes you can use a view instead, the
> principle is the same.
>
> When you select, instead of just writing a column you can write a select
> statement as well. And (ofcourse, otherwise there would be no point) you
> can use tables in the "big select" in the subselect. Here we create an
> array of all the targets this drug_id affect.
>
> bond=# select distinct
> bond-# array (select ct2.drug_id
> bond(# from comb_t ct2
> bond(# where ct2.the_arr = ct.the_arr) as drug_arr
add a
order by ct2.drug_id
in this subselect.
Sorry about this.
Marcus