Re: Re: Lock up on 7.0.2 involving CREATE FUNCTION/INDEX..

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
>