Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: [SQL] Functional Indexes

Re: [SQL] Functional Indexes

From
Tom Lane
Date:
"Bryan White" <bryan@arcamax.com> writes:
> The documentation for CREATE INDEX implies that functions are allowed in
> index definitions but when I execute:
> create unique index idxtest on customer (lower(email));
> the result is:
> ERROR:  DefineIndex: (null) class not found
> Should this work?  Do I have the syntax wrong?

I tried this wih 6.4.2 and found that it was only accepted if I
explicitly identified which index operator class to use:

play=> create table customer (email text);
CREATE
play=> create unique index idxtest on customer (lower(email));
ERROR:  DefineIndex:  class not found
play=> create unique index idxtest on customer (lower(email) text_ops);
CREATE
play=>

That'll do as a workaround for Bryan, but isn't this a bug?  Surely
the system ought to know what type the result of lower() is...

            regards, tom lane

Re: [SQL] Functional Indexes

From
Sascha Schumann
Date:
On Sat, Feb 06, 1999 at 12:27:47PM -0500, Tom Lane wrote:
> "Bryan White" <bryan@arcamax.com> writes:
> > The documentation for CREATE INDEX implies that functions are allowed in
> > index definitions but when I execute:
> > create unique index idxtest on customer (lower(email));
> > the result is:
> > ERROR:  DefineIndex: (null) class not found
> > Should this work?  Do I have the syntax wrong?
>
> I tried this wih 6.4.2 and found that it was only accepted if I
> explicitly identified which index operator class to use:
>
> play=> create table customer (email text);
> CREATE
> play=> create unique index idxtest on customer (lower(email));
> ERROR:  DefineIndex:  class not found
> play=> create unique index idxtest on customer (lower(email) text_ops);
> CREATE
> play=>
>
> That'll do as a workaround for Bryan, but isn't this a bug?  Surely
> the system ought to know what type the result of lower() is...
>
>             regards, tom lane

I still have a problem with that ... edited typescript follows

funweb=> \d userdat
Table    = userdat
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| username                         | varchar() not null               |    30 |
...
+----------------------------------+----------------------------------+-------+
Index:    userdat_pkey
funweb=> create unique index userdat_idx2 on userdat (lower(username)
varchar_ops);
ERROR:  BuildFuncTupleDesc: function 'lower(varchar)' does not exist

This error message looks very bogus to me.

--

          Regards,

                            Sascha Schumann |
                                 Consultant | finger sas@schell.de
                                            | for PGP public key

Re: [SQL] Functional Indexes

From
Marc Howard Zuckman
Date:
On Mon, 8 Feb 1999, Sascha Schumann wrote:

> On Sat, Feb 06, 1999 at 12:27:47PM -0500, Tom Lane wrote:
> > "Bryan White" <bryan@arcamax.com> writes:
> > > The documentation for CREATE INDEX implies that functions are allowed in
> > > index definitions but when I execute:
> > > create unique index idxtest on customer (lower(email));
> > > the result is:
> > > ERROR:  DefineIndex: (null) class not found
> > > Should this work?  Do I have the syntax wrong?
> >
> > I tried this wih 6.4.2 and found that it was only accepted if I
> > explicitly identified which index operator class to use:
> >
> > play=> create table customer (email text);
> > CREATE
> > play=> create unique index idxtest on customer (lower(email));
> > ERROR:  DefineIndex:  class not found
> > play=> create unique index idxtest on customer (lower(email) text_ops);
> > CREATE
> > play=>
> >
> > That'll do as a workaround for Bryan, but isn't this a bug?  Surely
> > the system ought to know what type the result of lower() is...
> >
> >             regards, tom lane
>
> I still have a problem with that ... edited typescript follows
>
> funweb=> \d userdat
> Table    = userdat
> +----------------------------------+----------------------------------+-------+
> |              Field               |              Type                | Length|
> +----------------------------------+----------------------------------+-------+
> | username                         | varchar() not null               |    30 |
> ...
> +----------------------------------+----------------------------------+-------+
> Index:    userdat_pkey
> funweb=> create unique index userdat_idx2 on userdat (lower(username)
> varchar_ops);
> ERROR:  BuildFuncTupleDesc: function 'lower(varchar)' does not exist
>
> This error message looks very bogus to me.
>
> --
>
>           Regards,
>
>                             Sascha Schumann |
>                                  Consultant | finger sas@schell.de
>                                             | for PGP public key
>

I don't think lower is defined for varchar arguments.  consider redefining
username as type text and using text_ops.

This method worked on my system:

stocks=> create table temptext (a text, b varchar(20));
CREATE
stocks=> create index itemptext on temptext using btree(lower(a) text_ops) ;
CREATE

Your error reproduced:

stocks=> create index i2temptext on temptext using btree(lower(b) text_ops) ;
ERROR:  BuildFuncTupleDesc: function 'lower(varchar)' does not exist

Excerpt from function definitions( both return value and argument are text
types):

text     |lower              |text          |lowercase

Marc Zuckman
marc@fallon.classyad.com

_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_
_     Visit The Home and Condo MarketPlace              _
_          http://www.ClassyAd.com                  _
_                                  _
_  FREE basic property listings/advertisements and searches.  _
_                                  _
_  Try our premium, yet inexpensive services for a real          _
_   selling or buying edge!                      _
_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_

Re: [SQL] Functional Indexes

From
Tom Lane
Date:
Marc Howard Zuckman <marc@fallon.classyad.com> writes:
> On Mon, 8 Feb 1999, Sascha Schumann wrote:
>> funweb=> create unique index userdat_idx2 on userdat (lower(username)
>> varchar_ops);
>> ERROR:  BuildFuncTupleDesc: function 'lower(varchar)' does not exist
>>
>> This error message looks very bogus to me.

> I don't think lower is defined for varchar arguments.  consider redefining
> username as type text and using text_ops.

I think Marc is right.  Someone was working on adding lower() to the
available ops for varchar for 6.5, but it's not there in 6.4.

You can get lower() to work on varchar source data in a simple
SELECT, but that's some sort of hack that involves the system
knowing that text and varchar have the same physical representation
so it's OK to use a function that takes text on a varchar column.
The type matching requirements for functional indexes are tighter.

Note to hackers: is there a good reason why indexes are more
restrictive?  Offhand it seems like the same physical-equivalence
trick could be applied.

            regards, tom lane

Re: [SQL] Functional Indexes

From
Bruce Momjian
Date:
>
> Note to hackers: is there a good reason why indexes are more
> restrictive?  Offhand it seems like the same physical-equivalence
> trick could be applied.

What do you mean by restrictive.  If you mean:

    * allow creation of functional indexes to use default types

It is on the TODO list, and has been for a while.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [HACKERS] Re: [SQL] Functional Indexes

From
"Thomas G. Lockhart"
Date:
> > I don't think lower is defined for varchar arguments.
> Note to hackers: is there a good reason why indexes are more
> restrictive?  Offhand it seems like the same physical-equivalence
> trick could be applied.

Well, we should have a combination of "binary compatible" and type
coersion to make this fly, just as we have in other places for v6.4. I
didn't realize this index code was there, so never looked at it.

If someone else doesn't get to it, I'll try to look at it before or
during 6.5beta...

                       - Tom

Re: [HACKERS] Re: [SQL] Functional Indexes

From
Bruce Momjian
Date:
On the TODO list:

    * allow creation of functional indexes to use default types


> "Bryan White" <bryan@arcamax.com> writes:
> > The documentation for CREATE INDEX implies that functions are allowed in
> > index definitions but when I execute:
> > create unique index idxtest on customer (lower(email));
> > the result is:
> > ERROR:  DefineIndex: (null) class not found
> > Should this work?  Do I have the syntax wrong?
>
> I tried this wih 6.4.2 and found that it was only accepted if I
> explicitly identified which index operator class to use:
>
> play=> create table customer (email text);
> CREATE
> play=> create unique index idxtest on customer (lower(email));
> ERROR:  DefineIndex:  class not found
> play=> create unique index idxtest on customer (lower(email) text_ops);
> CREATE
> play=>
>
> That'll do as a workaround for Bryan, but isn't this a bug?  Surely
> the system ought to know what type the result of lower() is...
>
>             regards, tom lane
>
>


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026