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

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

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

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

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

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

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.

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