pgSQL: am trying to insert into a table using dblink_exec() method, but getting following error

Question

select dblink_exec('hostaddr=59.89.210.56 port=5432 dbname=mydb user=postgres password=****','insert into gtab04 (productid,product,itgrid,itemtype,patentid,taxid,convfact,boxpack,cntid )values (2301,''D PEARL 2000 Caps 10's'',4,2,7,3,10,'''',0)')

while exe this query am getting the following error

ERROR:  syntax error at or near "s"
LINE 2: ...oxpack,cntid )values (2301,''D PEARL 2000 Caps 10's'',4,2,7,..

the problem is in product's name ie my product name is D PEARL 2000 Caps 10's (i think ' makes the problem) query concats this as ''D PEARL 2000 Caps 10's'' <- here is the problem

score 1 · Answer 1 · answered Oct 15 '14 at 11:15

I hope this will help you,

create or replace function create_or_update_test_employee_table() returns trigger as $_$
declare
    query text;
    fname text;
    lname text;
    id integer;
    created_at timestamp;
begin
    fname = NEW.first_name;
    lname = NEW.last_name;

    IF (TG_OP = 'INSERT') THEN
        id = NEW.id;
        query = 'insert into test_employee(id, first_name, last_name) values(''' || id || ''',''' || fname || ''',''' || lname || ''')';
        PERFORM dblink_exec('dbname=test_db',query);
        RETURN NEW;
    END IF;

    IF (TG_OP = 'UPDATE') THEN
        id = OLD.id;
        query = 'update test_employee set first_name = ''' || fname || ''', last_name = ''' || lname || ''' WHERE id = ' || id ||'';
        PERFORM dblink_exec('dbname=test_db',query);
        RETURN NEW;
    END IF;

    IF(TG_OP = 'DELETE') THEN
        PERFORM dblink_exec('dbname=test_db','DELETE FROM test_employee WHERE id = ' || OLD.id);
        RETURN OLD;
    END IF;

end $_$ language 'plpgsql';

score 0 · Accepted Answer · answered Mar 16 '14 at 23:47

0

Caps 10's should be Caps 10''''s

You double the single-quotes for dblink once, and for being inside a string the second time.

answered Mar 16 '14 at 23:47

maniek

7,087
2
20
43

domdomcodecode · Answer 3 · 2014-03-15T10:43:58.173

-1

Escape the single quote

D PEARL 2000 Caps 10\'\'s

edited Mar 15 '14 at 10:43

answered Mar 15 '14 at 10:34

domdomcodecode

2,355
4
19
27

Try escaping your quotes with `\'` instead then. You have to escape the escapes for mysql lol. – domdomcodecode Mar 15 '14 at 10:43
not working , and am using PostgreSQL, i have changed actual `''D PEARL 2000 Caps 10's''` to `''D PEARL 2000 Caps 10\'s''` – Mar 15 '14 at 10:54
Do `\'` twice, I modified the answer. – domdomcodecode Mar 15 '14 at 14:19

pgSQL: am trying to insert into a table using dblink_exec() method, but getting following error

3 Answers3