Re: Making a query from 2 tables at same time - Mailing list pgsql-general

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 <dev@archonet.com>:


--
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 : paumarc.munoz@bioinf.uab.cat