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

In article <am.pgsql.general.965867637.80159@illiad.adhesivemedia.com>,
Prasanth A. Kumar <kumar1@home.com> wrote:
>Philip Hallstrom <philip@adhesivemedia.com> writes:
>
>> Hi -
>>     The following statements lock up my machine completely (I can
>> ping, but can't telnet, nothing).  This is FreeBSD 3.4-STABLE running
>> 7.0.2.
>>
>> rolo_entry.fname is of type VARCHAR(30).
>>
>> devloki=> CREATE FUNCTION upper(VARCHAR) RETURNS TEXT AS '
>> devloki'>   BEGIN
>> devloki'>   RETURN UPPER($1);
>> devloki'>   END;
>> devloki'> ' LANGUAGE 'plpgsql';
>> CREATE
>> devloki=> CREATE INDEX foo_idx ON rolo_entry (upper(fname));
>>
>> If I rename the function to say "am_upper" it works just fine.
>>
>> ???
>
>I'm guessing that since sql is case insensitive, that results in
>infinite recursion because you have a function upper() which calls
>UPPER().
Oh... Duh!!!  Geesh... for some reason I figured it would call the
"built-in" UPPER, but obviously it won't. ha ha ha.  *sigh*
My next question then is how to get around this?  I could just rename my
function but it's nice to leave it UPPER since that is what it does.  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

Philip Hallstrom wrote:
> Oh... Duh!!!  Geesh... for some reason I figured it would call the
> "built-in" UPPER, but obviously it won't. ha ha ha.  *sigh*
> My next question then is how to get around this?  I could just rename my
> function but it's nice to leave it UPPER since that is what it does.  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

Uh, maybe I'm missing something, but, just _why_ do you need a pl/pgsql
function named UPPER that does nothing but call the built-in upper()?
Is there a type mismatch problem I'm not seeing?  Why do you need to do
this?

--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

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

Mike Mascari <mascarm@mascari.com> writes:

> 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?
<snip>

I think his original question was if one could create an index based
on the upper() of a table attribute. He stated he tried it and
couldn't and it trying some alternatives.

--
Prasanth Kumar
kumar1@home.com

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
>

Philip Hallstrom wrote:
> CREATE INDEX test_idx ON test (UPPER(field));

> 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.

That is wild.  I'd say bring this up in the hackers list -- as upper
should also work with varchar by default.

--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Yes... it is odd... especially since the following works fine:

SELECT UPPER(field) FROM test;

-philip

On Wed, 9 Aug 2000, Lamar Owen wrote:

> Philip Hallstrom wrote:
> > CREATE INDEX test_idx ON test (UPPER(field));
>
> > 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.
>
> That is wild.  I'd say bring this up in the hackers list -- as upper
> should also work with varchar by default.
>
> --
> Lamar Owen
> WGCR Internet Radio
> 1 Peter 4:11
>