postgresql trigger with dblink doesn't return anything

Question

I created a trigger to replicate inserts from a table 'mytable_db1' in database1 into the same table 'mytable_db2' on database2. Both databases are on the same server.

CREATE OR REPLACE FUNCTION trigger_osm_test_insert()
RETURNS trigger AS
$BODY$
BEGIN
  PERFORM dblink_connect('db2', 'dbname=xxx port=5432 user=myusr password=xxx');
  PERFORM dblink_exec('db2', 
  'insert into test.mytable_db2 (osm_id, name, name_eng, name_int, type, z_order, population, last_update, country, iso3, shape)
  values ('||new.osm_id||', '''||new.name||''', '''||new.name_eng||''', '''||new.name_int||''', '''||new.type||''', '||new.z_order||',
  '||new.population||', '''||new.last_update||''', '''||new.country||''', '''||new.iso3||''',
  st_geometry((st_AsText('''||new.shape::text||'''))))');
  PERFORM dblink_disconnect('db2');
  RETURN new;
END;
$BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;
ALTER FUNCTION trigger_osm_test_insert()
 OWNER TO myusr;

CREATE TRIGGER osm_insert_test
 AFTER INSERT
 ON mytable_db1
 FOR EACH ROW
 EXECUTE PROCEDURE trigger_osm_test_insert();

However, when I make a test insert such as:

insert into test.mytable_db1 (name, shape) values ('test', '0101000020E6100000E0979950035F4A40404B2751B0861CC0');

The inserted row is inserted into mytable_db1, but the trigger seem not being working as I have nothing in mytable_db2. The insert doesn't give me any error message from the trigger.

I'm using postgresql 9.2.2. Both databases have dblink 1.0 installed as well as postgis 2.0.6.

Does anyone have suggestions on what I am doing wrong?

Thanks!

Answer 1

There is a better solution to do it

Here another example

-- Function: flux_tresorerie_historique_backup_row()

-- DROP FUNCTION flux_tresorerie_historique_backup_row();

CREATE OR REPLACE FUNCTION flux_tresorerie_historique_backup_row()
  RETURNS trigger AS
$BODY$
BEGIN
  perform dblink_connect('dbname=gtr_bd_archive user=postgres password=postgres');
  perform dblink_exec('insert into flux_tresorerie_historique values('||
    concat_ws(', ', quote_nullable(OLD.id_flux_historique),
                    quote_nullable(OLD.date_operation_flux),
                    quote_nullable(OLD.date_valeur_flux),
                    quote_nullable(OLD.date_rapprochement_flux),
                    quote_nullable(OLD.libelle_flux),
                    quote_nullable(OLD.montant_flux),
                    quote_nullable(OLD.contre_valeur_dzd),
                    quote_nullable(OLD.rib_compte_bancaire),
                    quote_nullable(OLD.frais_flux),
                    quote_nullable(OLD.sens_flux),
                    quote_nullable(OLD.statut_flux),
                    quote_nullable(OLD.code_devise),
                    quote_nullable(OLD.code_mode_paiement),
                    quote_nullable(OLD.code_agence),
                    quote_nullable(OLD.code_compte),
                    quote_nullable(OLD.code_banque),
                    quote_nullable(OLD.date_maj_flux),
                    quote_nullable(OLD.statut_frais),
                    quote_nullable(OLD.reference_flux),
                    quote_nullable(OLD.code_commission),
                    quote_nullable(OLD.id_flux)
           )||');');
  perform dblink_disconnect();
  RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION flux_tresorerie_historique_backup_row()
  OWNER TO postgres;

if your Tables are identique you can use "format" like this

CREATE OR REPLACE FUNCTION flux_tresorerie_historique_backup_row()
  RETURNS trigger AS
$func$
BEGIN
   PERFORM dblink_connect('myserver');  -- name of foreign server

   PERFORM dblink_exec( format(
   $$
   INSERT INTO flux_tresorerie_historique
   SELECT (%L::flux_tresorerie_historique).*
   $$
   , OLD::text));

   PERFORM dblink_disconnect();
   RETURN NULL;  -- only for AFTER trigger
END
$func$  LANGUAGE plpgsql;

Answer 2

The problem is that you are not passing all columns when doing the INSERT. Because of this, some columns in NEW are null, and because you use string concatenation, your whole INSERT INTO... string is null.

For example, this query returns NULL:

select NULL || 'some text';

You should check every column for nulless, in example so:

CREATE OR REPLACE FUNCTION trigger_osm_test_insert()
RETURNS trigger AS
$BODY$
DECLARE insert_statement TEXT;
BEGIN
  
  IF NOT ARRAY['db2'] <@ dblink_get_connections() OR dblink_get_connections() IS NULL THEN
    PERFORM dblink_connect('db2', 'dbname=xxx port=5432 user=xxx password=xxx');
  END IF;

  insert_statement =  format('insert into mytable_db2 (
      osm_id, name, name_eng, name_int, type, z_order, 
      population, last_update, country, iso3, shape
      )
      values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
  coalesce(new.osm_id::text,'NULL'), 
  coalesce(quote_literal(new.name), 'NULL'), 
  coalesce(quote_literal(new.name_eng), 'NULL'), 
  coalesce(new.name_int::text, 'NULL'), 
  coalesce(quote_literal(new.type),'NULL'), 
  coalesce(new.z_order::text,'NULL'),
  coalesce(new.population::text,'NULL'), 
  coalesce(quote_literal(new.last_update::text),'NULL'), 
  coalesce(quote_literal(new.country),'NULL'), 
  coalesce(quote_literal(new.iso3::text), 'NULL'),
  coalesce(quote_literal(new.shape::text),'NULL')
  );
  PERFORM dblink_exec('db2', insert_statement);
  PERFORM dblink_disconnect('db2');
  RETURN new;
END;
$BODY$
 LANGUAGE plpgsql VOLATILE;

I'm not sure about the types of the columns, so check it. Inserting values which can be NULL is a little tricky...Please use this:

  coalesce(new.osm_id::text,'NULL'), 

For inserting integer values and this one:

  coalesce(quote_literal(new.name), 'NULL'), 

for inserting text, timestamp and geometries values. In the insert statement use always %s.

In addition to this, I done the following modifications to the function:

• A check if the connection does already exist
• The function st_geometry((st_AsText()) is not needed, as you already insert geometries in WKB format
• I use the function format() instead of the concatenate operator ||