From: Reuven M. Lerner <reuven@lerner.co.il>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Speeding up loops in pl/pgsql function
Date: Thu, 26 May 2011 01:26:17 +0300
Message-ID: <4DDD8209.4020208@lerner.co.il> (raw)
In-Reply-To: <BANLkTiko3haYX_CV4QKF8GMvsnq8-Uxs8Q@mail.gmail.com>
References: <4DDD358E.20307@lerner.co.il>
<BANLkTiko3haYX_CV4QKF8GMvsnq8-Uxs8Q@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: reuven@lerner.co.il, mmoncure@gmail.com
Subject: Re: Speeding up loops in pl/pgsql function
In-Reply-To: <4DDD8209.4020208@lerner.co.il>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox