Hi -
I have a table that has a varchar field (fname). I'd like to
create an index on UPPER(fname), but am running into problems...
What I don't understand is that I can do "SELECT UPPER(fname) FROM
mytable" and it works just fine. I also tried creating a SQL function
that did upper for me, but then the create index complains I can't use SQL
functions this way.
Hmm... I just tried creating a plpgsql function and now I can create the
index just fine...
Is this the only way to do it? How come there's no
UPPER(varchar) function?
Just curious...
Thanks!
-philip
devloki=> create index foo on rolo_entry (UPPER(fname));
ERROR: DefineIndex: function 'upper(varchar)' does not exist
devloki=> create index foo on rolo_entry (UPPER(varchar(fname)));
ERROR: parser: parse error at or near "varchar"
devloki=> create index foo on rolo_entry (UPPER(text(fname)));
ERROR: parser: parse error at or near "("
devloki=> create index foo on rolo_entry (UPPER(text fname));
ERROR: parser: parse error at or near "fname"
devloki=> create index foo on rolo_entry (UPPER(fname::text));
ERROR: parser: parse error at or near "::"
devloki=> create index foo on rolo_entry (UPPER(CAST(fname AS TEXT)));
ERROR: parser: parse error at or near "cast"
devloki=>
devloki=> create function varcharupper(varchar) returns text as '
devloki'> begin
devloki'> return upper($1);
devloki'> end;
devloki'> ' LANGUAGE 'plpgsql';
CREATE
devloki=> select varcharupper('test');
varcharupper
--------------
TEST
(1 row)
devloki=> create index foo on rolo_entry (varcharupper(fname));
CREATE
devloki=>
