From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | pinker <pinker(at)onet(dot)eu>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Escaping text / hstore |
Date: | 2015-10-20 14:12:48 |
Message-ID: | 56264BE0.6030308@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2015-10-20 14:33:42 | Re: RAID and SSD configuration question |
Previous Message | hubert depesz lubaczewski | 2015-10-20 14:07:18 | Re: Escaping text / hstore |