Re: Why Not MySQL?

"Mitch Vincent" <mitch@huntsvilleal.com> writes:
> ipa=# create index applicants_firstname on applicants(lower(firstname));
> ERROR:  DefineIndex: function 'lower(varchar)' does not exist

> ...that syntax is right, isn't it?

Hmm, that's annoying.  I guess you are going to have to make that field
be of type text.

Actually, since text and varchar look the same under the hood, the
existing lower() code would work just fine on varchar.  One fix for this
would be to add a pg_proc entry for lower(varchar), which you could do
by hand if you wanted:

regression=# create index f1lower on f1v (lower(f1));
ERROR:  DefineIndex: function 'lower(varchar)' does not exist

regression=# create function lower(varchar) returns text as 'lower'
regression-# language 'internal' with (iscachable);
CREATE

regression=# select * from pg_proc where proname = 'lower';proname | proowner | prolang | proisinh | proistrusted |
proiscachable| pronargs | proretset | prorettype | proargtypes | probyte_pct | properbyte_cpu | propercall_cpu |
prooutin_ratio| prosrc | probin
 

---------+----------+---------+----------+--------------+---------------+----------+-----------+------------+-------------+-------------+----------------+----------------+----------------+--------+--------lower
 |      256 |      11 | f        | t            | t             |        1 | f         |         25 |          25 |
   100 |              0 |              0 |            100 | lower  | -lower   |      256 |      11 | f        | t
    | t             |        1 | f         |         25 |        1043 |         100 |              0 |              0 |
          100 | lower  | -
 
(2 rows)

-- ok, looks like I got it right ...

regression=# create index f1lower on f1v (lower(f1));
CREATE

This will be a tiny bit slower than if the function were really truly
built-in, but it should work well enough.

But since type varchar is considered binary-compatible with type text,
you shouldn't have had to create the extra function entry.  It looks
like the indexing routines do not pay attention to binary type
compatibility when looking up functions for functional indexes.  I'm not
going to try fixing that now, but it's something that should be on the
TODO list: * Functional indexes should allow functions on binary-compatible types
        regards, tom lane