Postgres Pro
Downloads
Home   >   mailing lists

Thread: select from an index

select from an index

From
"Pau Marc Munoz Torres"
Date:
Hi

 Recently i created an index in a table using a function (not a column) as following

create index H2IAb on precalc (idr(p1,p4,p6,p7,p9,'H-2*IAb'));,

where idr is a function that returns a real number,

as a result i got the following table

mhc2db=> \d precalc;
                            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)),

now, i would like to perform a query using this index, something like

 
Select * from precalc where h2iab>2

but obviously h2iab  is not a column...

some of you knows what i should do?

thanks

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

Re: select from an index

From
Steve Atkins
Date:
On Nov 27, 2007, at 3:15 AM, Pau Marc Munoz Torres wrote:

> Hi
>
>  Recently i created an index in a table using a function (not a
> column) as following
>
> create index H2IAb on precalc (idr(p1,p4,p6,p7,p9,'H-2*IAb'));,
>
>
> now, i would like to perform a query using this index, something like
>
>
> Select * from precalc where h2iab>2
>
> but obviously h2iab  is not a column...
>
> some of you knows what i should do?
>

select * from precalc where idr(p1,p4,p6,p7,p9,'H-2*IAb') > 2

Cheers,
   Steve

Re: select from an index

From
"Pau Marc Munoz Torres"
Date:

Hi

 Recently i created an index in a table using a function (not a column) as following

create index H2IAb on precalc (idr(p1,p4,p6,p7,p9,'H-2*IAb')); , where idr is a function that returns a real number,as a result i got the following table

mhc2db=> \d precalc;
                           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)),

now, i perform a query using this index
 

Select * from precalc where Idr(p1, p4, p6, p7, p9, 'H-2*IAb')>2

and its works, the problems comes when you try to do selects below certain number that it crash,

the funtion looks like

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;


and crash at (*), some of you know why?


thanks


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