Thread: Force index usage on bigint in PL/PGSQL

Hi all,

Reading  the pg  archives i  found  out the  reason  why i  have bad  db
performance. It's becase of the quotes around the bigint issue.

I have a large (23M records) table (called data) on which i've create an
index on  the field  'ora' that is  a bigint. Explain  told me  that the
following query doesn't use this index

select codice, ora, tem from data where ora > 200210230000;

while this one does

select codice, ora, tem from data where ora > '200210230000';

The problem is that i'd like to "force" my plpgsql functions to use
indexes.

Is there  a way  I can rewrite  the following plpgsql  query to  use the
quote-trick ?

result record;
orain alias for $1;     -- bigint
orafin alias for $2;    -- bigint

for result in
    select rai, tem  -- rai and tem are fields of the table
    from data
    where (codice LIKE cod_staz and
        ora > orain and
        ora <= orafin ) loop


By the way, has this thing been fixed in the newest versions of postgres
? I'm using the "debianized" version 7.2.1

Thanks in advance !

Bye, Federico.

Federico Pedemonte <fepede@inwind.it> writes:
> The problem is that i'd like to "force" my plpgsql functions to use
> indexes.
> Is there  a way  I can rewrite  the following plpgsql  query to  use the
> quote-trick ?

> result record;
> orain alias for $1;     -- bigint
> orafin alias for $2;    -- bigint

If $1 and $2 are in fact declared as bigint, then you don't have a
problem.

            regards, tom lane