Re: [GENERAL] Re: Lock up on 7.0.2 involving CREATE FUNCTION/INDEX..
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
Re: [GENERAL] Re: Lock up on 7.0.2 involving CREATE FUNCTION/INDEX..
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
Re: [GENERAL] Re: Lock up on 7.0.2 involving CREATE FUNCTION/INDEX..
Mike Mascari [EMAIL PROTECTED] 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
[EMAIL PROTECTED]
Re: [GENERAL] 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
Re: [GENERAL] Re: Lock up on 7.0.2 involving CREATE FUNCTION/INDEX..
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
Re: [GENERAL] Re: Lock up on 7.0.2 involving CREATE FUNCTION/INDEX..
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
[GENERAL] Re: Lock up on 7.0.2 involving CREATE FUNCTION/INDEX..
In article [EMAIL PROTECTED], Prasanth A. Kumar [EMAIL PROTECTED] wrote: Philip Hallstrom [EMAIL PROTECTED] 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
