I know... using your example below, try the following:
CREATE INDEX test_idx ON test (UPPER(field));
On my system I get the following errors:
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"
So, by creating a function such as UPPER(varchar) instead of the built-in
UPPER(text), I can do what I want.
What's odd, is that I can create the function UPPER(varchar) which then
calls UPPER(text) and use it all I want. However, if I then try to create
an index (like my first example above) it locks up the entire machine.
I just realized this after someone mentioned there was probably a
recursive loop, but wouldn't that affect simple select statements as well?
Oh well... I've renamed my function with a prefix which I'll probably just
do all the time as it makes it easy to know what's mine and what's not.
-philip
On Wed, 9 Aug 2000, Mike Mascari wrote:
> Philip Hallstrom wrote:
> >
> > Is there another function that will uppercase? Or is there some way to
> > call the other UPPER function? Or something within plpgsql I don't know
> > about.
> > Thanks!
> > -philip
>
> I don't understand this. UPPER() is a built-in function:
>
> stocks=# create table test (field varchar(16));
> CREATE
> stocks=# insert into test values ('hello');
> INSERT 1788137 1
> stocks=# select upper(field) from test;
> upper
> -------
> HELLO
> (1 row)
>
> The oid for upper is 871. Do you not have this in your pg_proc?
>
> --
>
> Cheers,
>
> Mike Mascari
>
