Re: 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 <dev(at)archonet(dot)com>:
>
> 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 : paumarc(dot)munoz(at)bioinf(dot)uab(dot)cat