Re: Functional Index Question

On Wed, March 12, 2008 17:02, hubert depesz lubaczewski wrote:
> correct way:
> select lower(trim(both ' ' from(regexp_replace(' depeSz hub ', E'\\s+', ' ',
> 'g' ))));
>
> now. i would suggest *not* to use this as base for index.
>
> make a wrapper function instead:
>
> create function cleaned(text) returns text as $BODY$
> select lower(trim(both ' ' from(regexp_replace($1, E'\\s+', ' ', 'g' ))));
> $BODY$ language sql immutable;
>
> now you can simply:
> create unique index xxx on table ( cleaned(<column>) );
> plus your sql's will look saner.
> instead of:
> select * from table where lower(trim(both ' ' from(regexp_replace(<column>,
> E'\\s+', ' ', 'g' )))) = lower(trim(both ' '
> from(regexp_replace('some_string', E'\\s+', ' ', 'g' ))));
> you will have:
> select * from table where cleaned(<field>) = cleaned('some_string');
>

Thank you very much. I really appreciate this.

To return to my first question. Is doing this sort of thing considered good a
practice?

I am prototyping in Rails a replacement system for one implemented with a
CODASYL shallow network DBSM. In Rails the paradigm seems to be to keep this
kind of thing out of the database and place it in the application code. With
the existing system we had no choice in the matter but I am considering moving
theses sorts of purification routines into the DBMS because I feel that is
where they really belong. However, the prevailing sentiment of the community
surrounding Rails seems to hold otherwise so I wonder if this is really the
right thing to do.

Regards,

--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB(at)Harte-Lyne(dot)ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3