Escaping text / hstore

[pinker <pinker@xxxxxxx>]

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.