Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.


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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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)

share|improve this answer
    
Great that was it, thank you :) –  user74756 Mar 17 '11 at 9:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.