From: Steve Crawford <scrawford@pinpointresearch.com>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: Reuven M. Lerner <reuven@lerner.co.il>
Cc: Alex Hunsaker <badalex@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Speeding up loops in pl/pgsql function
Date: Thu, 26 May 2011 08:24:45 -0700
Message-ID: <4DDE70BD.2060205@pinpointresearch.com> (raw)
In-Reply-To: <BANLkTikoGpWS=S2EFruyWCkGxFoRk7Ms_A@mail.gmail.com>
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>
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
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: scrawford@pinpointresearch.com, mmoncure@gmail.com, reuven@lerner.co.il, badalex@gmail.com
Subject: Re: Speeding up loops in pl/pgsql function
In-Reply-To: <4DDE70BD.2060205@pinpointresearch.com>
* 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