Postgres Pro
Downloads
Home   >   mailing lists

Thread: "reverse()" on strings

"reverse()" on strings

From
h012@ied.com
Date:
Hi,
CREATE TABLE file (name varchar(255));
I have a couple of milion filenames. I need to build index based on 
extensions. A couple of possibilities come to mind:
CREATE INDEX extension_idx ON file (reverse(name));
-- but I didn't find a function called "reverse"
CREATE INDEX extension_idx ON file (regex_match( '.*(\.[^\.]*)$' );
-- but I didn't find a function called "regex_match" which would return
string matched in brackets ()

Any ideas ? Help ? Hints ?

  Thanks in advance !
     John


PS: if there is reverse on strings -- where could I find "reverse()" on 
arrays ? - thx

-- 
-- Gospel of Jesus is the saving power of God for all who believe --              ## To some, nothing is impossible.
##

Re: "reverse()" on strings

From
Jeff Boes
Date:
On Mon, 26 Aug 2002 16:13:44 -0400, h012 wrote:


>  CREATE INDEX extension_idx ON file (reverse(name));
> -- but I didn't find a function called "reverse"
> 
>  CREATE INDEX extension_idx ON file (regex_match( '.*(\.[^\.]*)$' );
> -- but I didn't find a function called "regex_match" which would return
> string matched in brackets ()
> 

You probably want to do a

$ createlang plperl

and then something like this:

CREATE FUNCTION fn_strrev(text) returns text as '
return reverse($_[0])
' language 'plperl' with (iscachable);

The same approach can be used to provide a regex match with Perl syntax,
but I don't have an example of that coded up and at hand.


-- 
Jeff Boes                                      vox 269.226.9550 ext 24
Database Engineer                                     fax 269.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com          ...Nexcerpt... Extend your Expertise

Re: "reverse()" on strings

From
Josh Berkus
Date:
Jeff, h012,

> CREATE FUNCTION fn_strrev(text) returns text as '
> return reverse($_[0])
> ' language 'plperl' with (iscachable);

If you make that "WITH (ISCACHABLE, ISSTRICT)" the index will be faster to
update on columns which contain large numbers of NULLs.  "ISSTRICT" refers to
the fact that if the function receives a NULL, it will output a NULL, and
thus saves the parser the time running NULLs through the function.

Also, remember to use this index, you'll have to call the exact same function
in your queries.

--
-Josh BerkusAglio Database SolutionsSan Francisco

Re: "reverse()" on strings

From
h012@ied.com
Date:
Jeff & Josh, thanks for showing me a solution !
  John


PS: just curious: is there anything I can do to affect effectiveness of 
the cache, when ISCACHABLE is used ? (I.e. size / expiring algorithm, or 
order of inputs - E.g. when building the index, is there anything that 
would order the inputs first, to maximize cache hit/miss ratio, such as 
"CREATE INDEX extension_idx ON file (reverse(name)) ORDER BY name" ?

On Mon, 26 Aug 2002, Josh Berkus wrote:

> Jeff, h012,
> 
> > CREATE FUNCTION fn_strrev(text) returns text as '
> > return reverse($_[0])
> > ' language 'plperl' with (iscachable);
> 
> If you make that "WITH (ISCACHABLE, ISSTRICT)" the index will be faster to 
> update on columns which contain large numbers of NULLs.  "ISSTRICT" refers to 
> the fact that if the function receives a NULL, it will output a NULL, and 
> thus saves the parser the time running NULLs through the function.
> 
> Also, remember to use this index, you'll have to call the exact same function 
> in your queries.
> 
> 

-- 
-- Gospel of Jesus is the saving power of God for all who believe --              ## To some, nothing is impossible. ##
                  http://Honza.Vicherek.com/