Maybe somebody have an idea how to escape text string for use in hstore column? I have tried $$ and quote_literal in audit trigger function, but still db won't let me pass values with // or ' to the hstore... INSERT FROM trigger function:
EXECUTE 'INSERT INTO history.' || TG_TABLE_SCHEMA || '_' || TG_TABLE_NAME || l_table_suffix || '(operation, event_time, executed_by, new_value) VALUES(''' || TG_OP || ''', ''' || CURRENT_TIMESTAMP || ''', ''' || SESSION_USER || ''', $$''' || hstore(NEW) || '''$$)';
During insert occurs error:
INSERT INTO my_table ("some_id", "someother_id","description") VALUES (1751, 10907,'gdfddfg''gdfg');
The same occurs with backslash:
INSERT INTO my_table ("some_id", "someother_id","description") VALUES (1751, 10907,'gdfddfg//gdfg');
ERROR: Syntax error near ''' at position 73
LINE 2: ..., '2015-10-20 15:41:08.534645+02', 'my_user', $$'"some... ^
QUERY: INSERT INTO history.public_my_table_2015_10(operation, event_time, executed_by, new_value) VALUES('INSERT', '2015-10-20 15:41:08.534645+02', 'my_user', $$'"some_id"=>"1751", "someother_id"=>"10907", "description"=>"gdfddfg'gdfg"'$$)
CONTEXT: PL/pgSQL function insert() line 6 at EXECUTE statement
View this message in context:
Escaping text / hstore Sent from the
PostgreSQL - general mailing list archive at Nabble.com.