json - postgresql文本到json的转换

Question

我在数据库中有一个表query_config，里面会包含查询的脚本，以及其他配置值 。这就是为什么我使用 json 类型的原因。表格如下:

CREATE TABLE query_config 
(
   id integer, 
   execute_query json 
); 

在这个表中我想插入例如:

INSERT INTO query_config(id, execute_query)
    VALUES (4, ('{"query": ' || 
    '"select *
    from tests
    where tests.id > 5
    order by moment desc"}')::json);

但我一直收到错误:

ERROR: invalid input syntax for type json 
DETAIL: Character with value 0x0a must be escaped.

请问我做错了什么，如何转义换行符？

Answer 1

使用 to_json 使其成为有效的 json 和 dollar quoting 所以它会吞下查询中的任何文本

select format('{%s: %s}',
    to_json('query'::text), 
    to_json($query$
        select *
        from tests
        where tests.id > 5
        order by moment desc
    $query$::text)
)::json;
                                                       format                                                        
---------------------------------------------------------------------------------------------------------------------
 {"query": "\n        select *\n        from tests\n        where tests.id > 5\n        order by moment desc\n    "}

http://www.postgresql.org/docs/current/static/functions-json.html#FUNCTIONS-JSON-TABLE

http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

格式化功能只是为了好看

http://www.postgresql.org/docs/current/static/functions-string.html#FUNCTIONS-STRING-OTHER