AM/PM times? Am I going crazy?

On Tue, 8 Aug 2000, Philip Hallstrom wrote:

Hi -
It appears that postgres considers 1pm to really be 1am??? The
following is occuring on 7.0.2. Am I missing something obvious?

The PM/AM bug is already fixed in the current CVS tree.

Karel

Karel Zak <zakkr@zf.jcu.cz> writes:

On Tue, 8 Aug 2000, Philip Hallstrom wrote:

Hi -
It appears that postgres considers 1pm to really be 1am??? The
following is occuring on 7.0.2. Am I missing something obvious?

The PM/AM bug is already fixed in the current CVS tree.

Any chance of getting a patch towards 7.0.2 or a 7.0.3? TIA.

--
Trond Eivind Glomsr�d
Red Hat, Inc.

On 8 Aug 2000, Trond Eivind[iso-8859-1] Glomsr�d wrote:

Karel Zak <zakkr@zf.jcu.cz> writes:

On Tue, 8 Aug 2000, Philip Hallstrom wrote:

Hi -
It appears that postgres considers 1pm to really be 1am??? The
following is occuring on 7.0.2. Am I missing something obvious?

The PM/AM bug is already fixed in the current CVS tree.

Any chance of getting a patch towards 7.0.2 or a 7.0.3? TIA.

Not sure. In current CVS is changed 'fmgr' matter for all
build-in functions, this not allow easy prepare a patch :-((

You can try use CVS sources.

Karel

Karel Zak <zakkr@zf.jcu.cz> writes:

On 8 Aug 2000, Trond Eivind[iso-8859-1] Glomsrřd wrote:

Karel Zak <zakkr@zf.jcu.cz> writes:

On Tue, 8 Aug 2000, Philip Hallstrom wrote:

Hi -
It appears that postgres considers 1pm to really be 1am??? The
following is occuring on 7.0.2. Am I missing something obvious?

The PM/AM bug is already fixed in the current CVS tree.

Any chance of getting a patch towards 7.0.2 or a 7.0.3? TIA.

Not sure. In current CVS is changed 'fmgr' matter for all
build-in functions, this not allow easy prepare a patch :-((

You can try use CVS sources.

I can't use a CVS snapshot for our release...

--

Hi -
I have a table that has a varchar field (fname). I'd like to
create an index on UPPER(fname), but am running into problems...

What I don't understand is that I can do "SELECT UPPER(fname) FROM
mytable" and it works just fine. I also tried creating a SQL function
that did upper for me, but then the create index complains I can't use SQL
functions this way.

Hmm... I just tried creating a plpgsql function and now I can create the
index just fine...

Is this the only way to do it? How come there's no
UPPER(varchar) function?

Just curious...

Thanks!

-philip

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"

devloki=>
devloki=> create function varcharupper(varchar) returns text as '
devloki'> begin
devloki'> return upper($1);
devloki'> end;
devloki'> ' LANGUAGE 'plpgsql';
CREATE
devloki=> select varcharupper('test');
varcharupper
--------------
TEST
(1 row)

devloki=> create index foo on rolo_entry (varcharupper(fname));
CREATE
devloki=>

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.

???

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

--
Prasanth Kumar
kumar1@home.com

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:

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: