Thread: Escaping text / hstore
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:
View this message in context: Escaping text / hstore
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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.
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/
On 10/20/2015 07:00 AM, 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 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 Here is my very similar function: CREATE OR REPLACE FUNCTION utility.archive_del_record() RETURNS trigger AS $BODY$ DECLARE tbl_name text := TG_TABLE_NAME || '_delete' ; archive_row hstore := hstore(OLD.*); user_name text := session_user; BEGIN EXECUTE 'INSERT INTO ' ||quote_ident(tbl_name) || '(record_fld, del_ts, del_user)' || ' VALUES('||quote_literal(archive_row)||', now(),' || quote_literal(user_name)||')'; RETURN OLD; END; $BODY$ LANGUAGE plpgsql SECURITY DEFINER; > > > ------------------------------------------------------------------------ > View this message in context: Escaping text / hstore > <http://postgresql.nabble.com/Escaping-text-hstore-tp5870728.html> > Sent from the PostgreSQL - general mailing list archive > <http://postgresql.nabble.com/PostgreSQL-general-f1843780.html> at > Nabble.com. -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver-4 wrote > Here is my very similar function: > > CREATE OR REPLACE FUNCTION utility.archive_del_record() RETURNS trigger AS > $BODY$ > DECLARE > tbl_name text := TG_TABLE_NAME || '_delete' ; > archive_row hstore := hstore(OLD.*); > user_name text := session_user; > BEGIN > EXECUTE 'INSERT INTO ' ||quote_ident(tbl_name) || > '(record_fld, del_ts, del_user)' > || ' VALUES('||quote_literal(archive_row)||', now(),' || > quote_literal(user_name)||')'; > RETURN OLD; > END; > $BODY$ > LANGUAGE plpgsql SECURITY DEFINER; Thank you Adrian you made my day! I compared our functions and came out that the problem was only in quotes... I have used 3 of them and with hstore one is enough. -- View this message in context: http://postgresql.nabble.com/Escaping-text-hstore-tp5870728p5870762.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.