RE: Using functions with indexes

Hi Alain

try this :

create function mylower( varchar) returns varchar as '
declare
lowered alias for $1;
begin
return lower(lowered);
end;
' language 'plpgsql';

create index mytext_s1 on mytext ( mylower(col) varchar_ops);

( assuming table mytext has column "col" )

You will need to have the Language plpgsql installed, if you have not do :

create function plpgsql_call_handler () returns opaque as
'/usr/local/pgsql/lib/plpgsql.so' language 'C';

create trusted procedural language 'plpgsql'
handler plpgsql_call_handler
lancompiler 'PL/pgSQL';

I found that using language sql to create "mylower" gave an error
at index create time - something about cannot use sql in this context..

Cheers

Mark