Message-ID: <4DDD8209.4020208@lerner.co.il>
Date: Thu, 26 May 2011 01:26:17 +0300
From: "Reuven M. Lerner" <reuven@lerner.co.il>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US;
	rv:1.9.2.17) Gecko/20110414 Thunderbird/3.1.10
MIME-Version: 1.0
To: Merlin Moncure <mmoncure@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Speeding up loops in pl/pgsql function
References: <4DDD358E.20307@lerner.co.il>
	<BANLkTiko3haYX_CV4QKF8GMvsnq8-Uxs8Q@mail.gmail.com>
In-Reply-To: <BANLkTiko3haYX_CV4QKF8GMvsnq8-Uxs8Q@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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