Re: Speeding up loops in pl/pgsql function

On Wed, May 25, 2011 at 10:59, Reuven M. Lerner <reuven(at)lerner(dot)co(dot)il> wrote:
> Hi, everyone.  I'm working on a project that's using PostgreSQL 8.3, that
> requires me to translate strings of octal digits into strings of characters
> -- so '141142143' should become 'abc', although the database column
> containing this data (both before and after) is a bytea.

Have you tried something like:
SELECT encode(regexp_replace('141142143', '(\d{3})', '\\\1',
'g')::bytea, 'escape');

> ...
> Of course, any suggestions for how to deal with octal digits in PostgreSQL
> 8.3, such as an octal equivalent to the x'ff' syntax, would be more than
> welcome.

I think select E'\XXX' is what you are looking for (per the fine
manual: http://www.postgresql.org/docs/current/static/datatype-binary.html)