0

I have problems with copying bytea data in trigger from one table to another.

The original query looks like:

INSERT INTO "mytable" ("dataid","data")
VALUES 
(123456,'\x3008d1a10a000000050000000000000000000000fb27000009090504010804060a08020b020c040000000000000000000000000001000000'::bytea);

Inside "before insert" trigger, I get syntax error when trying to insert this value to another table. The reason is that inside the trigger the value NEW.data is already escaped and looks like

0\010\321\241\012\000\000\000\005\000\000\000\000\000\000\000\000\000\000\000\373\'000\000\011\011\005\004\001\010\004\006\012\010\002\013\002\014\004\000\000\000\000\000\000\000\000\000\000\000\000\000\001\000\000\000

Is there a way to retrive NEW.dataid actual value ( before type cast or as a string ) ?

p.s. I cannot change original query or convert or set bytea_output to hex on production db

Trigger function code:

BEGIN 
PERFORM dblink('logsyellow', '
    INSERT INTO datastorage(dataid,data)    
    VALUES(' || new.dataid || ','||new.data||') 
');
RETURN new;
END;

Error I get:

PL/pgSQL function fn_replicate_data() line 4 at PERFORM
2014-07-08 13:24:35 GMT OPERATOR:  INSERT INTO mytable ( dataid, data ) VALUES ( 123456, '\x3008d1a10a000000050000000000000000000000fb27000009090504010804060a08020b020c040000000000000000000000000001000000'::bytea );
2014-07-08 13:24:35 GMT ERROR:  syntax error (at near "\")
2014-07-08 13:24:35 GMT CONTEXT:  Error occurred on dblink connection named "unnamed": could not execute command.
        SQL-operator: "SELECT DBLINK_EXEC('logsyellow','INSERT INTO datastorage(dataid,data)        VALUES('||new.dataid||','''||new.data||''') ')"
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
Natalie
  • 441
  • 1
  • 6
  • 17
  • There's nothing special to do, since a bytea column in `NEW` in a trigger is actually **not escaped**. Please show the code and error message or reproducible test case that makes you think otherwise. – Daniel Vérité Jul 10 '14 at 09:11
  • Hi Daniel, just added function code and error above. The reason I think it is already **escaped**, because "raise notice" + sql query shows INSERT INTO datastorage ( dataid, data ) VALUES(123456,0\010\321\241\012\000\000\000\005\000\000\000\000\000\000\000\000\000\000\000\373\'000\000\011\011\005\004\001\010\004\006\012\010\002\013\002\014\004\000\000\000\000\000\000\000\000\000\000\000\000\000\001\000\000\000); – Natalie Jul 10 '14 at 09:21
  • @Natalie It gets escaped as part of formatting into a string for display. – Craig Ringer Jul 10 '14 at 10:44
  • @Craig it gets escaped on the stage of building dblink query or before, otherwise I wouldn't get syntax error. Original query is correct and doesn't produce errors. – Natalie Jul 10 '14 at 11:09

1 Answers1

0

Finally I got a solution.. thanks to everyone)

PERFORM dblink('logsyellow','INSERT INTO datastorage(dataid,data)   VALUES(' || new.dataid || ','||quote_literal(new.data)||') ')
Natalie
  • 441
  • 1
  • 6
  • 17