trigger and dblink

Question

im working on a trigger in PL/TCL that inserts data into tableB on databaseB upon insertion on tableA from databaseA. both databases are on the same server. i came up with this:

CREATE OR REPLACE FUNCTION add_to_erp() RETURNS trigger AS $$
BEGIN
IF (TG_OP='INSERT') THEN
perform dblink_connect('dbname=oerp_test user=postgres password=abouali');
perform dblink_exec('insert into product_template(standard_price, list_price, name) values ('||NEW.pricebuy||','||NEW.pricesell||','||NEW.name||');');
perform dblink_exec('insert into product_product(product_tmpl_id) values (currval(''product_template_id_seq''::regclass));');
perform dblink_disconnect();
END IF;
RETURN NEW;
END; $$
LANGUAGE 'plpgsql';
CREATE TRIGGER add_to_erp_trigger AFTER INSERT ON products FOR EACH ROW EXECUTE PROCEDURE add_to_erp();

but when i try:
INSERT INTO products (id,reference,code,name,pricebuy,pricesell,category,taxcat) VALUES (3,3,3,'apple',12,24,'000','000');

i get:
ERROR: column "apple" does not exist

if i try instead:
INSERT INTO products (id,reference,code,name,pricebuy,pricesell,category,taxcat) VALUES (3,3,3,'34',12,24,'000','000');

its successful

any ideas?

Answer 1

You need to include the quotes on the value for the name column in the query you're constructing. You'll need:

perform dblink_exec('insert into product_template(standard_price, list_price, name) 
                     values ('||NEW.pricebuy||','||NEW.pricesell||','''||NEW.name||''');');

Where '' within the string becomes ', then you concatenate apple, so the end result becomes values (12,24,'apple') instead of values (12,24,apple)