Re: Speeding up loops in pl/pgsql function

["Reuven M. Lerner" <reuven@xxxxxxxxxxxx>]

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


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