Re: Speeding up loops in pl/pgsql function

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