Re: Escaping text / hstore

On Tue, Oct 20, 2015 at 07:00:04AM -0700, pinker wrote:
> 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 73LINE 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

First of all - stop the insanity of wrapping long lines like above
- it's unreadable.

Second- learn to use "EXECUTE USING"

Third - learn to use format() when execute using can't help.

and it will stop the ''' $$''" madness.

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/