Re: [GENERAL] Making a query from 2 tables at same time
Pau Marc Munoz Torres wrote: Ok, I have two tables, first one, that i call precalc has the following structure ... and a numer of index that is a real number resulting of a function (function is called idr and i talk about it bellow) ... another table is local than has the following fields id values for both tables are the same. idr function is a function that I wrote create function IDR(char,char,char,char,char,varchar(20)) returns real AS' ... select into P1 score from PSSMS where AA=$1 and POS=1 and MOLEC=$6; ... ' LANGUAGE plpgsql IMMUTABLE; This function is not IMMUTABLE, it queries a table. Labelling it IMMUTABLE doesn't make it so. Oh and it takes "char" parameters but you seem to pass it "varchar". where PSSMS was a temporay table (it don't exist right now) Then how can the function be meaningful? now i would like to perform a select to get p1,p2,p6,p7,p9, sp and pos from those register that her value in the index is bigger than x OK mhc2db=> select t1.sp, t1.pos,t2.p1, t2.p4, t2.p6, t2.p7, t2.p9 from local as t1, precalc as t2 where t1.ce='ACIAD' and idr(t2.p1, t2.p4, t2.p6, t2.p7, t2.p9, 'HLA-DRB5*0101')>2; if i perfom a select like select * from precalc where dr(p1,p4, p6, p7, p9, 'HLA-DRB5*0101')>2; I assume you mean idr(...) If you want this to work properly, then your function needs to genuinely be immutable. That means it only depends on the values you pass in to it, not on any other tables. If it works in the simple case above, then that is purely chance. So - can idr() calculate its score based on its inputs? or select * from local where ce='ACIAD'; I don't see what this query has to do with your problem. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Making a query from 2 tables at same time
This is the problem: > ... t2.idr(p1, p4, p6, p7, p9, 'HLA-DRB5*0101')>2; What is that t2.idr ? Based on the syntax postgres must look for a function (because of the parentheses), and it thinks t2 is the schema where it must look for it. Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Making a query from 2 tables at same time
Ok, I have two tables, first one, that i call precalc has the following structure id . serial p1 varchar p4 varchar p6 varchar p7 varchar p9 varchar and a numer of index that is a real number resulting of a function (function is called idr and i talk about it bellow) another table is local than has the following fields ce varchar sp varchar pos integer id integer id values for both tables are the same. idr function is a function that I wrote create function IDR(char,char,char,char,char,varchar(20)) returns real AS' DECLARE output real; P1 real; P4 real; P6 real; P7 real; P9 real; BEGIN select into P1 score from PSSMS where AA=$1 and POS=1 and MOLEC=$6; select into P4 score from PSSMS where AA=$2 and POS=4 and MOLEC=$6; select into P6 score from PSSMS where AA=$3 and POS=6 and MOLEC=$6; select into P7 score from PSSMS where AA=$4 and POS=7 and MOLEC=$6; select into P9 score from PSSMS where AA=$5 and POS=9 and MOLEC=$6; select into output P1+P4+P6+P7+P9; return output; END; ' LANGUAGE plpgsql IMMUTABLE; where PSSMS was a temporay table (it don't exist right now) now i would like to perform a select to get p1,p2,p6,p7,p9, sp and pos from those register that her value in the index is bigger than x mhc2db=> select t1.sp, t1.pos,t2.p1, t2.p4, t2.p6, t2.p7, t2.p9 from local as t1, precalc as t2 where t1.ce='ACIAD' and idr(t2.p1, t2.p4, t2.p6, t2.p7, t2.p9, 'HLA-DRB5*0101')>2; if i perfom a select like select * from precalc where dr(p1,p4, p6, p7, p9, 'HLA-DRB5*0101')>2; or select * from local where ce='ACIAD'; works perfectely is it clear enough? i don't now if i make myself understand, any way, if it not, please, ask me!! thanks pau 2007/11/29, Richard Huxton <[EMAIL PROTECTED]>: > > Pau Marc Munoz Torres wrote: > > i test it and now the error is > > > > mhc2db=> select t1.sp, t1.pos,t2.p1, t2.p4, t2.p6, t2.p7, t2.p9 from > local > > as t1, precalc as t2 where t1.ce='ACIAD' and idr(t2.p1, t2.p4, t2.p6, > t2.p7, > > t2.p9, 'HLA-DRB5*0101')>2; > > ERROR: relation "pssms" does not exist > > CONTEXT: SQL statement "SELECT score from PSSMS where AA= $1 and > POS=1 > > and MOLEC= $2 " > > PL/pgSQL function "idr" line 11 at select into variables > > > > pssm was a temporary table that i used to calculate the index in precalc > > table with idr function, should i rebuilt it? the problem is that if i > can't > > use idr as an index it eill be to slow > > I think you need to take a step back and explain what it is you are > trying to do - you shouldn't be using an external table in an indexed > function at all. > > -- >Richard Huxton >Archonet Ltd > -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
Re: [GENERAL] Making a query from 2 tables at same time
Pau Marc Munoz Torres wrote: i test it and now the error is mhc2db=> select t1.sp, t1.pos,t2.p1, t2.p4, t2.p6, t2.p7, t2.p9 from local as t1, precalc as t2 where t1.ce='ACIAD' and idr(t2.p1, t2.p4, t2.p6, t2.p7, t2.p9, 'HLA-DRB5*0101')>2; ERROR: relation "pssms" does not exist CONTEXT: SQL statement "SELECT score from PSSMS where AA= $1 and POS=1 and MOLEC= $2 " PL/pgSQL function "idr" line 11 at select into variables pssm was a temporary table that i used to calculate the index in precalc table with idr function, should i rebuilt it? the problem is that if i can't use idr as an index it eill be to slow I think you need to take a step back and explain what it is you are trying to do - you shouldn't be using an external table in an indexed function at all. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Making a query from 2 tables at same time
i test it and now the error is mhc2db=> select t1.sp, t1.pos,t2.p1, t2.p4, t2.p6, t2.p7, t2.p9 from local as t1, precalc as t2 where t1.ce='ACIAD' and idr(t2.p1, t2.p4, t2.p6, t2.p7, t2.p9, 'HLA-DRB5*0101')>2; ERROR: relation "pssms" does not exist CONTEXT: SQL statement "SELECT score from PSSMS where AA= $1 and POS=1 and MOLEC= $2 " PL/pgSQL function "idr" line 11 at select into variables pssm was a temporary table that i used to calculate the index in precalc table with idr function, should i rebuilt it? the problem is that if i can't use idr as an index it eill be to slow pau 2007/11/29, Richard Huxton < [EMAIL PROTECTED]>: > > Pau Marc Munoz Torres wrote: > > Hi everybody > > > > I'm doing a two table query as follow > > > > mhc2db=> select t1.sp, t1.pos,t2.p1, t2.p4, t2.p6, t2.p7, t2.p9 from > local > > as t1, precalc as t2 where t1.ce='ACIAD' and t2.idr(p1, p4, p6, p7, p9, > > 'HLA-DRB5*0101')>2; > > > > and i get the following error > > > > ERROR: schema "t2" does not exist > > > > but those tables exists!! and are as follow!! > > You have t2.idr(...) which is being read as a function "idr" in schema > "t2". > > Did you mean idr(t2.p1, t2.p4, ...)? > > > -- >Richard Huxton >Archonet Ltd > -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
Re: [GENERAL] Making a query from 2 tables at same time
am Thu, dem 29.11.2007, um 15:48:45 +0100 mailte Pau Marc Munoz Torres folgendes: > Hi everybody > > I'm doing a two table query as follow > > mhc2db=> select t1.sp, t1.pos,t2.p1, t2.p4, t2.p6, t2.p7, t2.p9 from local as > t1, precalc as t2 where t1.ce='ACIAD' and t2.idr(p1, p4, p6, p7, p9, > 'HLA-DRB5*0101')>2; > > and i get the following error > > ERROR: schema "t2" does not exist > > but those tables exists!! and are as follow!! The error-message means the function-call t2.idr(...), this is wrong. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Making a query from 2 tables at same time
Pau Marc Munoz Torres wrote: Hi everybody I'm doing a two table query as follow mhc2db=> select t1.sp, t1.pos,t2.p1, t2.p4, t2.p6, t2.p7, t2.p9 from local as t1, precalc as t2 where t1.ce='ACIAD' and t2.idr(p1, p4, p6, p7, p9, 'HLA-DRB5*0101')>2; and i get the following error ERROR: schema "t2" does not exist but those tables exists!! and are as follow!! You have t2.idr(...) which is being read as a function "idr" in schema "t2". Did you mean idr(t2.p1, t2.p4, ...)? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Making a query from 2 tables at same time
Hi everybody
I'm doing a two table query as follow
mhc2db=> select t1.sp, t1.pos,t2.p1, t2.p4, t2.p6, t2.p7, t2.p9 from local
as t1, precalc as t2 where t1.ce='ACIAD' and t2.idr(p1, p4, p6, p7, p9,
'HLA-DRB5*0101')>2;
and i get the following error
ERROR: schema "t2" does not exist
but those tables exists!! and are as follow!!
mhc2db=> \d precalc (t2)
Table "public.precalc"
Column | Type | Modifiers
+--+--
id | integer | not null default
nextval('precalc_id_seq'::regclass)
p1 | character(1) |
p4 | character(1) |
p6 | character(1) |
p7 | character(1) |
p9 | character(1) |
Indexes:
"h2iab" btree (idr(p1, p4, p6, p7, p9, 'H-2*IAb'::character varying))
"h2iad" btree (idr(p1, p4, p6, p7, p9, 'H-2*IAd'::character varying))
"h2iak" btree (idr(p1, p4, p6, p7, p9, 'H-2*IAk'::character varying))
"h2ied" btree (idr(p1, p4, p6, p7, p9, 'H-2*IEd'::character varying))
"hladqa10501" btree (idr(p1, p4, p6, p7, p9, 'HLA-DQA1*0501'::character
varying))
"hladr" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR'::character varying))
"hladr1" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR1'::character varying))
"hladr13" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR13'::character varying))
"hladr3" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR3'::character varying))
"hladr4" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR4'::character varying))
"hladr7" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR7'::character varying))
"hladrb10101" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character
varying))
"hladrb10102" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0102'::character
varying))
"hladrb10301" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0301'::character
varying))
"hladrb10401" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0401'::character
varying))
"hladrb10402" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0402'::character
varying))
"hladrb10404" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0404'::character
varying))
"hladrb10701" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0701'::character
varying))
"hladrb10802" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0802'::character
varying))
"hladrb10901" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0901'::character
varying))
"hladrb11101" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1101'::character
varying))
"hladrb11102" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1102'::character
varying))
"hladrb11104" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1104'::character
varying))
"hladrb11301" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1301'::character
varying))
"hladrb11302" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1302'::character
varying))
"hladrb11501" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1501'::character
varying))
"hladrb40101" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB4*0101'::character
varying))
"hladrb50101" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB5*0101'::character
varying))
"iid" btree (id)
mhc2db=> \d local (t1)
Table "public.local"
Column | Type | Modifiers
+--+---
ce | character varying(6) |
sp | character varying(6) |
pos| integer |
id | integer |
Someone knows what is the problem?
Thanks
Pau
--
Pau Marc Muñoz Torres
Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent
Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)
telèfon: 93 5812807
Email : [EMAIL PROTECTED]
