CODEWITHSUNDEEP
postgresqltriggersdblink
Wildchild
Wildchild

Reputation: 243

postgresql: dblink inserting on remote database

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.

Upvotes: 1

Views: 6397

Answers (1)

ferbusson
ferbusson

Reputation: 1

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.

Upvotes: 0

Related Questions