Thread: Using functions with indexes

Hello,

I want to use an index for comparisons on strings which are
not case-sensitive (I'm currently using col2=lower(col1))

The type of the column is char(30), it looks like I can't
use the function lower directly as it's not working on
the right type :

dip=> create index idx_tmpplayer_add_low on TMPPLAYER ( lower ( EM_ADDRESS )
text_ops );
ERROR:  BuildFuncTupleDesc: function 'lower(bpchar)' does not exist

But I can't manage to define such a function I need for the index, what's
the trick ?

dip=> create function mylower(char) returns char as 'select lower($1)'
language 'sql';
ERROR:  return type mismatch in function: declared to return char, returns
text
dip=> create function mylower(char) returns char as 'select
char(lower(char_text($1)))' language 'sql';
ERROR:  parser: parse error at or near "lower"


Thanks
Alain

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