Lets say I have function that triggers when table is being updated. Then it tries to send this data as json into remote database via dblink:
statement := 'INSERT INTO mytable(my_data) VALUES (''' || my_json || ''')';
PERFORM dblink('my connection data', statement);
my_json
is formed by json_build_object
method with some dynamic data. When some of this json fields values contains single quote, this function starts throw syntax errors.
I know that I need to use double single quotes, but I can’t because data is dynamic.
For example if my json is like this:
{ "a": "It's a test" }
It throws:
Syntax error at s
3
Answers
I'm going to answer to my own question.
The way I was able to solve this problem I have added some fixes before forming my statement:
Only after that I have formed my statement for my dblink:
You can use
quote_literal()
to add the 2nd apostrophe:Use:
Based on your comments, I would point that:
%L
.Another way using
quote_literal()
and not usingformat()
would be:Again there are no single quotes to add around
quote_literal(myjson)
. It’s the responsibility ofquote_literal
to add these quotes.