Thread: select using an index

select using an index

From

"Pau Marc Munoz Torres"

Date:

27 November 2007, 10:18:03

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

--
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 using an index

From

Zoltan Boszormenyi

Date:

27 November 2007, 10:33:20

Pau Marc Munoz Torres írta:
> 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

Formulate your WHERE clause so it matches
your index's expression. E.g.

SELECT * FROM precalc WHERE
idr(p1, p4, p6, p7, p9, 'H-2*IAb'::character varying) > 2;

Best regards,
Zoltán Böszörményi


--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/