Re: Escape double-quotes in text[]?

Raymond O'Donnell wrote
> 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.

SELECT regexp_split_to_array('val"1|val"2|val"3','\|');

SELECT regexp_split_to_array(?,'\|');

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Escape-double-quotes-in-text-tp5801913p5802008.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.