Re: [GENERAL] select from an index

[Pau Marc Munoz Torres]

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 : [EMAIL PROTECTED]