Message-ID: <4DDE70BD.2060205@pinpointresearch.com>
Date: Thu, 26 May 2011 08:24:45 -0700
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.17) Gecko/20110424 Lightning/1.0b2 Thunderbird/3.1.10
MIME-Version: 1.0
To: Merlin Moncure <mmoncure@gmail.com>
CC: "Reuven M. Lerner" <reuven@lerner.co.il>,
	Alex Hunsaker <badalex@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: Speeding up loops in pl/pgsql function
References: <4DDD358E.20307@lerner.co.il>
	<BANLkTinuiR=Txtn_dUeSbvvgcWZj9c2+iw@mail.gmail.com>
	<4DDD4E57.7070800@lerner.co.il>	<4DDD98B6.6080208@pinpointresearch.com>
	<BANLkTin8u2fM+RjE4Ao78v2W83V2Z9iHUA@mail.gmail.com>
	<BANLkTimU=O-vreTUTmC6TV+cA+TyqYqUnQ@mail.gmail.com>
	<BANLkTikoGpWS=S2EFruyWCkGxFoRk7Ms_A@mail.gmail.com>
In-Reply-To: <BANLkTikoGpWS=S2EFruyWCkGxFoRk7Ms_A@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 05/26/2011 05:36 AM, Merlin Moncure wrote:
> ...
> got it:
> select decode(regexp_replace('141142143', '([0-9][0-9][0-9])',
> $q$\\\1$q$ , 'g'), 'escape');
>   decode
> --------
>   abc
> (1 row)
>
> merlin
>
Nice. A word of warning, in 9.0 this returns a hex string:

select decode(regexp_replace('141142143', '([0-9][0-9][0-9])', 
$q$\\\1$q$ , 'g'), 'escape');
   decode
----------
  \x616263

See http://www.postgresql.org/docs/9.0/static/release-9-0.html:

E.5.2.3. Data Types
     bytea output now appears in hex format by default (Peter Eisentraut)
     The server parameter bytea_output can be used to select the 
traditional output format if needed for compatibility.

Another wrinkle, the function I wrote sort of ignored the bytea issue by 
using text. But text is subject to character-encoding (for both good and 
bad) while bytea is not so the ultimate solution will depend on whether 
the input string is the octal representation of an un-encoded sequence 
of bytes or represents a string of ASCII/UTF-8/whatever... encoded text.

Cheers,
Steve