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