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

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

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





--
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

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

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 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