Re: Escape double-quotes in text[]?

On 29/04/2014 22:54, David G Johnston wrote:
> Raymond O'Donnell wrote
>> Hi all,
>>
>> Probably a silly question, but I'm having trouble figuring out the
>> answer... if I'm constructing an string representation of a value to go
>> into a text[] column, and one of the text literals includes
>> double-quotes, do I need to escape the literal?
>>
>> For example, can I insert something like this into a text[] column
>> directly? -
>>
>> '{"abc", "de"f"}'
>>
>> Or would I need to do this? -
>>
>> E'{"abc", "de"f"}'
>>
>> ....or something different again? I'm doing this from PHP via the Zend
>> framework (v.1) if it makes any difference.
>>
>> Thanks,
>>
>> Ray.
>
> The easy way:
>
> SELECT ARRAY['ab"c','de"f']::varchar[] => {"ab"c","de"f"}
>
> Knowing the above; reverse-engineer the literal input syntax
>
> SELECT {"ab"c","de"f"}::varchar[] #Nope "bare {"
> SELECT '{"ab"c","de"f"}'::varchar[] #Nope "malformed array literal"
> SELECT '{"ab\"c","de\"f"}'::varchar[] #Yay!
> SELECT E'{"ab\"c","de\"f"}'::varchar[] #hmmm.....
> SELECT E'{"ab\\"c","de\\"f"}'::varchar[] #yeah, double-escape (literal
> first, then array)
>
> This is all documented but it does not seem to be centrally summarized; you
> need to check few different array-related areas to pick up the rules and/or
> capabilities (namely, use ARRAY[...] syntax if at all possible).

Thanks a million David - that's very helpful.

ARRAY[] doesn't work for me, unfortunately; I'm using parametrised
queries in Zend framework, and all the parameters get put in as strings,
so I need to build the array literals before submitting them.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie