pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed"reverse()" on strings
4+ messages / 3 participants
[nested] [flat]
* "reverse()" on strings
@ 2002-08-26 20:12 h012@ied.com
0 siblings, 1 reply; 4+ messages in thread
From: h012@ied.com @ 2002-08-26 20:12 UTC (permalink / raw)
To: pgsql-sql
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. ##
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: "reverse()" on strings
@ 2002-08-26 20:56 Jeff Boes <jboes@nexcerpt.com>
parent: h012@ied.com
0 siblings, 1 reply; 4+ messages in thread
From: Jeff Boes @ 2002-08-26 20:56 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: "reverse()" on strings
@ 2002-08-26 21:11 Josh Berkus <josh@agliodbs.com>
parent: Jeff Boes <jboes@nexcerpt.com>
0 siblings, 1 reply; 4+ messages in thread
From: Josh Berkus @ 2002-08-26 21:11 UTC (permalink / raw)
To: Jeff Boes <jboes@nexcerpt.com>; pgsql-sql
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 Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: "reverse()" on strings
@ 2002-08-28 08:07 h012@ied.com
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 4+ messages in thread
From: h012@ied.com @ 2002-08-28 08:07 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Jeff Boes <jboes@nexcerpt.com>; pgsql-sql
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-08-28 08:07 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-26 20:12 "reverse()" on strings h012@ied.com
2002-08-26 20:56 ` Jeff Boes <jboes@nexcerpt.com>
2002-08-26 21:11 ` Josh Berkus <josh@agliodbs.com>
2002-08-28 08:07 ` h012@ied.com
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox