Re: Question on inserting non-ascii strings

On Thu, May 14, 2009 at 11:25:33AM -0400, Steven Lembark wrote:
> Q: Is there any combination of locale, encoding,
> client_encoding or functions that will allow
> me to insert values with these escape sequences
> without getting the warnings?
>
> Trying this in psql with various combinations of
> prepares statements leaves me unable to use
> convert_from with a varchar argument (requires
> bytea).

I think you're confused about where the escaping happens; in SQL,
escape sequences are expanded as it is being parsed (actually, it's the
step before known as "lex"ing, but the two stages are normally rolled
together unless precision is needed). Parameters are passed after the
SQL has been parsed and hence no expansion of escape sequences in your
values is done.

You want to be using whatever language you're generating the parameter
from (Perl) to handle the expansion of escape sequences for you. This
will cause the expanded string (i.e. the escapes have been interpreted)
to be sent to Postgres and everything should just work. Unfortunately I
don't use Perl much, so can't give much in the way of a demo--hopefully
others will.

--
Sam http://samason.me.uk/