Обсуждение: 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