Fast REVERSE() function?
I've got a case where I need to reverse strings, and find that, oddly
enough, there isn't a C-based reverse() function.
A search turns up pl/pgsql and SQL implementations:
create or replace function reverse_string(text) returns text as $$
DECLARE
reversed_string text;
incoming alias for $1;
BEGIN
reversed_string = '''';
for i in reverse char_length(incoming)..1 loop
reversed_string = reversed_string || substring(incoming from i for 1);
end loop;
return reversed_string;
END $$
language plpgsql;
CREATE OR REPLACE FUNCTION reverse(TEXT) RETURNS TEXT AS $$
SELECT
array_to_string(
ARRAY
( SELECT substring($1, s.i,1) FROM generate_series(length($1), 1, -1) AS s(i) ),
'');
$$ LANGUAGE SQL IMMUTABLE;
Unfortunately, neither is particularly fast. This should be
"blinding-quick" in C, in comparison; reversing a set of bytes should
be able to be done mighty quick!
(Aside: presumably we could walk thru the string destructively,
in-place, swapping bytes; I think that would be theoretically
quickest...)
I could probably add this in as an SPI() function; is there a good
reason to try to avoid doing so?
--
output = reverse("ofni.sesabatadxunil" "@" "enworbbc")
http://www3.sympatico.ca/cbbrowne/sgml.html
"Consistency is the single most important aspect of *ideology.*
Reality is not nearly so consistent." - <cbbrowne@hex.net>
(Aside: presumably we could walk thru the string destructively,
in-place, swapping bytes; I think that would be theoretically
quickest...)
Hmmm... I guess it will not work für UTF-8 or any other multibyte charset
On Mon, Sep 08, 2008 at 11:20:18AM -0400, Chris Browne wrote:
I've got a case where I need to reverse strings, and find that, oddly
enough, there isn't a C-based reverse() function.
A search turns up pl/pgsql and SQL implementations:
just for completenes - there is also pl/perl and c versions freely
available:
http://www.depesz.com/index.php/2007/07/30/indexable-field-like-something/
(pl/perl)
http://blog.frosties.org/post/2007/08/28/Fonction-reverse-C-avec-PostgreSQL
(c)
Best regards,
depesz
--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
Hello
2008/9/8 Mario Weilguni <mario.weilguni@icomedias.com>:
(Aside: presumably we could walk thru the string destructively,
in-place, swapping bytes; I think that would be theoretically
quickest...)Hmmm... I guess it will not work für UTF-8 or any other multibyte charset
it isn't problem, but I am not sure, if ANSI SQL has this function?
Regards
Pavel Stehule
Show quoted text
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Mario Weilguni wrote:
(Aside: presumably we could walk thru the string destructively,
in-place, swapping bytes; I think that would be theoretically
quickest...)Hmmm... I guess it will not work f�r UTF-8 or any other multibyte charset
Yes, quite.
Perl's reverse might work with UTF8 - I've never tried.
cheers
andrew
2008/9/8 Andrew Dunstan <andrew@dunslane.net>:
Mario Weilguni wrote:
(Aside: presumably we could walk thru the string destructively,
in-place, swapping bytes; I think that would be theoretically
quickest...)Hmmm... I guess it will not work für UTF-8 or any other multibyte charset
Yes, quite.
orafce contains multibyte (UTF8) reverse function.
Pavel
Show quoted text
Perl's reverse might work with UTF8 - I've never tried.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
depesz@depesz.com (hubert depesz lubaczewski) writes:
On Mon, Sep 08, 2008 at 11:20:18AM -0400, Chris Browne wrote:
I've got a case where I need to reverse strings, and find that, oddly
enough, there isn't a C-based reverse() function.
A search turns up pl/pgsql and SQL implementations:just for completenes - there is also pl/perl and c versions freely
available:
http://www.depesz.com/index.php/2007/07/30/indexable-field-like-something/
(pl/perl)
http://blog.frosties.org/post/2007/08/28/Fonction-reverse-C-avec-PostgreSQL
(c)
I hadn't thought about the Unicode issue (mentioned elsewhere in the
thread); that's a good reason why the method I mentioned *wouldn't* be
a good one!
I'm NOT interested in pl/perl as an option; building and deploying all
of Perl is a mighty expensive way to get *ONE* function (and I don't
think that fundamentally changes if it's 10 functions!).
In the long run, I'd be keen on there being a REVERSE function
available in pg_catalog, which is why I'm asking about the C version,
as that would be the way to put it into the "core."
--
"cbbrowne","@","linuxdatabases.info"
http://www3.sympatico.ca/cbbrowne/sap.html
DSK: STAN.K; ML EXIT -- FILE NOT FOUND