Re: Speeding up loops in pl/pgsql function

Hi, everyone. Merlin wrote:

> let's see the source. I bet we can get this figured out.

Here you go... it looked nicer before I started to make optimizations;
I've gotten it to run about 2x as fast as the previous version, but now
I'm sorta stuck, looking for further optimizations, including possible
use of builtin functions.

Thanks for any suggestions you can offer.

CREATE OR REPLACE FUNCTION translate_octals_into_decimals(bytea_string
BYTEA) RETURNS BYTEA AS $$
DECLARE
bytea_string_length INTEGER := length(bytea_string);
current_substring TEXT := '';
translated_string_array BYTEA[];

output_number INTEGER := 0;
output_number_text TEXT := '';
current_digit TEXT := '';
BEGIN
RAISE NOTICE '[translate_octals_into_decimals] start at %, string of
length %', clock_timestamp(), pg_size_pretty(length(bytea_string));

FOR i IN 1..length(bytea_string) BY 3 LOOP
current_substring := substring(bytea_string from i for 3);

output_number := 0;

FOR j IN 0..(length(current_substring) - 1) LOOP
current_digit := substring(current_substring from
(length(current_substring) - j) for 1);
output_number := output_number + current_digit::integer * (8 ^ j);
END LOOP;

output_number_text = lpad(output_number::text, 3, '0');

IF output_number_text::int = 92 THEN
translated_string_array := array_append(translated_string_array,
E'\\\\'::bytea);
ELSIF output_number_text::int = 0 THEN
translated_string_array := array_append(translated_string_array,
E'\\000'::bytea);
ELSE
translated_string_array := array_append( translated_string_array,
chr(output_number_text::integer)::bytea );
END IF;

END LOOP;

RETURN array_to_string(translated_string_array, '');
END;
$$ LANGUAGE 'plpgsql';

Reuven

--
Reuven M. Lerner -- Web development, consulting, and training
Mobile: +972-54-496-8405 * US phone: 847-230-9795
Skype/AIM: reuvenlerner