select from an index

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

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