Re: CREATE INDEX test_idx ON test (UPPER(varchar_field)) doesn't work...

Philip Hallstrom <philip(at)adhesivemedia(dot)com> writes:
> devloki=> CREATE INDEX test_upper_idx ON test (UPPER(field));
> ERROR: DefineIndex: function 'upper(varchar)' does not exist

This is a known bug. There is indeed no upper(varchar) function
declared in pg_proc, but the parser knows that varchar is "binary
equivalent" to type text, so when you ask for upper(varchar) in
most contexts it will silently substitute upper(text) instead.
The bug is that CREATE INDEX does not provide the same leeway;
it wants to find an exact type-signature match. It should accept
functions that are binary-compatible with the type being indexed.

This is on the to-do list and might make a good first backend-hacking
project, if anyone is annoyed enough by it to work on it before the
core developers get 'round to it.

BTW, I did just read over the discussion in pg-general (was out of town
so couldn't answer sooner) and I believe you could have made your
function work safely if it read

CREATE FUNCTION upper(VARCHAR) RETURNS TEXT AS '
...
RETURN UPPER($1::text);
...

As you wrote it it's an infinite recursion, because as soon as you
provide a function upper(varchar), that will be selected in preference
to upper(text) for any varchar input value --- so "RETURN UPPER($1)" is
a self-reference. But with the type coercion you should get a call to
the built-in upper(text) instead.

A faster way is the one someone else suggested: just create another row
in pg_proc that declares upper(varchar) as an alias for the built-in
upper(text). For example,
CREATE FUNCTION upper(VARCHAR) RETURNS TEXT AS 'upper' LANGUAGE 'internal';

(You have to first look in pg_proc to confirm that the internal function
is in fact named 'upper' at the C level --- look at the 'prosrc' field.)

The infinite recursion should not have "locked up" your machine; if it
did I'd say that's a bad weakness in FreeBSD. What I see on HPUX is a
coredump due to stack limit overrun within a second or two of invoking
an infinitely-recursive function. Performance of other processes
doesn't seem to be hurt materially... although HPUX does take an
unreasonably long time to actually execute a coredump of a process
that's grown to a large size...

regards, tom lane