带有dblink的postgresql触发器不返回任何内容 [英] postgresql trigger with dblink doesn't return anything

问题描述

我创建了一个触发器，用于将插入从数据库1中的表"mytable_db1"复制到数据库2中的同一表"mytable_db2"中.两个数据库都在同一台服务器上.

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');

插入的行已插入到mytable_db1中，但是触发器似乎没有起作用，因为mytable_db2中没有任何内容.插入并没有给我任何来自触发器的错误消息.

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.

我正在使用PostgreSQL 9.2.2.这两个数据库都安装了dblink 1.0以及postgis 2.0.6.

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?

谢谢！

推荐答案

问题是执行INSERT时没有传递所有列.因此，NEW中的某些列为空，并且由于使用字符串连接，因此整个INSERT INTO...字符串为空.

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.

在示例中，此查询返回NULL:

In 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;

我不确定列的类型，请检查一下.插入可以为NULL的值有些棘手...请使用以下方法:

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'), 

用于插入文本，时间戳和几何值.在插入语句中，始终使用%s.

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:

• 检查连接是否已经存在
• 不需要功能st_geometry((st_AsText())，因为您已经以WKB格式插入了几何图形
• 我使用函数format()代替了串联运算符||

• 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 ||

这篇关于带有dblink的postgresql触发器不返回任何内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！