我曾经遇到过类似的问题,我需要发送一种JSON结构,然后将其解码为我的查询,但是有一个程序接收了我的字符串,并且这个程序转义了我的转义字符,导致查询失败。最终我找到了这个链接:
在PostgreSQL中使用$$表示美元引用字符串
其中提到了quote_literal(42.5)
https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-OTHER
这解决了我的问题。以下是一个示例:
字符串为
'LocalTime={US/Central}; NumDays={1}; NumRows={3}; F_ID={[Apple, Orange, Bannana]}'
Select
Param, value , replace(quote_literal(replace(replace(Value,'[',''),']','')),',',quote_literal(',')) ValueList
FROM (
select
SPLIT_PART(split,'=',1) as Param,
replace( replace(SPLIT_PART(split,'=',2),'{',''),'}','') as Value
FROM
(
select
trim(split_part(freeform.txt, ';', number.n)) as split
from
( select
'LocalTime={US/Central}; NumDays={1}; NumRows={3}; F_ID={[Apple, Orange, Bannana]}' as txt
) freeform,
( select 1 as n union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10
) number
where split <> ''
) as MY_TMP
) as valuePart