postgresql: dblink inserting on remote database

Question

This is my situation,

I have two databases (db1, db2), both with table "countries". I need that when I insert on db1.countries, automatically inserts on db2.countries. I'm using dblink module to connect with the second database.

countries table SQL schema is:

CREATE TABLE countries (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  flag VARCHAR(5) 
);

To manage this situation, on db1 I have created the following procedure:

CREATE OR REPLACE FUNCTION prc_insert_to_countries()
      RETURNS trigger AS
    $$
    DECLARE 
        insert_statement TEXT;
        res TEXT;
    BEGIN                
        perform dblink_connect('db2', 'dbname=db2 host=localhost 
        user=xxx password=xxx');

        insert_statement = 'insert into countries(id, name, flag) 
                                    values ('||NEW.id||', 
                                    '''||NEW.name||''', 
                                    '''||NEW.flag||'''
                                    );';
        res := dblink_exec('db2', insert_statement, true);
        RAISE INFO '%', res;
        perform dblink_disconnect('db2');
        RETURN NEW;
    END;
    $$
    LANGUAGE 'plpgsql';

And the following trigger:

CREATE TRIGGER tr_countries
      AFTER INSERT
      ON countries
      FOR EACH ROW
      EXECUTE PROCEDURE prc_insert_to_countries();

If I execute the next insert statement on db1:

INSERT INTO countries (id, name, flag) VALUES (1, 'Italy', 'ITA');

All works perfectly, and the inserted row is inserted into db2.countries too. But if I execute:

INSERT INTO countries (id, name, flag) VALUES (1, 'Italy', NULL);

The inserted row is not inserted into db2.countries...

I have tried to solve this with this post: postgresql trigger with dblink doesn't return anything but it doesnt' works me..

How can I solve this?

Thanks.

That's probably because concatenating (||) with NULL results in NULL (your insert_statement will be NULL). Also, concatenating SQL statements opens up the possibility for SQL injection. Use the format() function instead of concatenating manually. — pozs
– pozs, Commented Feb 2, 2017 at 13:36

ferbusson · Accepted Answer · 2020-10-01 01:30:33Z

0

The NULL field yields the problem, try to use COALESCE to handle the NULL like this:

insert_statement = 'insert into countries(id, name, flag) 
                                    values ('||NEW.id||', 
                                    '''||NEW.name||''', 
                                    '''||COALESCE(NEW.flag,'')||'''
                                    );';

Just keep in mind that the flag field in the remote db will get an empty string isntead of NULL.

edited Oct 1, 2020 at 1:30

answered Sep 30, 2020 at 23:44

ferbusson

12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql: dblink inserting on remote database

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related