Thread: Why indexes are not used when scanning from functions?

The table (script followed) contains more than 200000 records. 
When I directly selecting records by the single select statement    select fio from patient_temp where fio like
'something%';
 

Postgres uses the index i have created and quickly returns the results. But if that query included into the function,
slowestsequent scan starts. Is it a normal postgres behavior - not using indexes in functions? Or how to make indexes
usablein functions? 
 
I have tried PL/pgSQL, and have got the same result.

create table patient_temp(code serial primary key,    fio varchar(80)
);

create index fio_patient_temp_ind on patient_temp(fio);

create function get_fio1(text) returns varchar
as 'select fio from patient_temp where fio like $1::text || \'%\';'  
language 'sql';
            Aidamir Lovpache <aidanet@yandex.ru>

"Ловпаче Айдамир" <aidanet@yandex.ru> writes:
> create function get_fio1(text) returns varchar
> as 'select fio from patient_temp where fio like $1::text || \'%\';'  
> language 'sql';

You won't get an indexscan for this because the LIKE pattern is not
a constant at planning time, and so the planner cannot extract indexscan
bounds from it.

In 7.1 it's possible to obtain the desired result in plpgsql, by using
EXECUTE.  You'd need to substitute the pattern into the query string
as a literal constant, not as a plpgsql variable.
        regards, tom lane