Re: Escape double-quotes in text[]?

On 30/04/2014 17:32, David G Johnston wrote:
> 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.

Ahhhh.... very nice! Thank you!

Ray.

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