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