Monday, September 8, 2008

[HACKERS] 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>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

No comments: