Thread: 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 > -- > 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 > > 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