Обсуждение: 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:

   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.