使用“BEFORE INSERT”触发器来更改传入数据的数据类型，以匹配PostgreSQL中的列数据类型

I have a postgres table, with a column C which has type T. People will be using COPY to insert data into this table. However sometimes they try to insert a value for C that isn't of type T, however I have a postgres function which can convert the value to T.

我有一个postgres表，其中有一个C列，它的类型是t。人们将使用COPY将数据插入到这个表中。但是，有时他们试图为C插入一个不是类型为T的值，但是我有一个postgres函数，它可以将值转换为T。

I'm trying to write a BEFORE INSERT trigger on the table which will call this function on the data so that I can ensure that I get no insert type errors. However it doesn't appear to work, I'm getting errors when trying to insert the data, even with the trigger there.

我试着在表上写一个插入触发器，它会在数据上调用这个函数，这样我就能确保没有插入类型错误。但是它似乎不起作用，当我试图插入数据时，即使有触发器也会出错。

Before I spend too much time investigating, I want to find out if this is possible. Can I use triggers in this way to change the type of incoming data?

在我花太多时间调查之前，我想知道这是否可能。我可以用这种方式使用触发器来更改传入数据的类型吗?

I want this to run on postgresql 9.3, but I have noticed the error and non-functioning trigger on postgres 9.5.

我想让它在postgresql 9.3上运行，但是我注意到了postgres9.5上的错误和无法运行的触发器。

2 个解决方案

#1

No, you can not use this approach. The reason is that the backend already populates a record with the values that are to be inserted into the table. That is in the form of the NEW parameter that is available in the trigger. So the error is thrown even before the trigger fires.

不，你不能用这种方法。原因是后端已经用要插入到表中的值填充记录。这是在触发器中可用的新参数的形式。所以这个错误在触发之前就被抛出了。

The same applies to rules, incidentally, so Kevin's suggestion in his comment won't work.

顺便说一句，同样的道理也适用于规则，所以凯文的建议不会起作用。

Probably your best solution is to create a staging table with "permissive" column data types (such as text) and then put a BEFORE INSERT trigger on that table that casts all column values to their correct type before inserting them in the final table. If that second insertion is successful you can even RETURN NULL from the insert so the row won't go into the table (not sure, though, what COPY thinks about that...). Those records that do end up in the table have some weird data in them and you can then deal with those rows manually.

可能您最好的解决方案是使用“允许的”列数据类型(如文本)创建一个staging表，然后在该表上放置一个BEFORE INSERT触发器，在将所有列值插入最终表之前将它们转换为正确的类型。如果第二个插入是成功的，您甚至可以从insert返回NULL，这样行就不会进入表中(但是，不确定，是什么副本认为…)。那些最终出现在表中的记录中有一些奇怪的数据，然后您可以手动处理这些行。

#2

As Patrick stated you have to specify a permissive target so that Postgres validation doesn't reject the data before you get a chance to manipulate it.

如Patrick所述，您必须指定一个允许的目标，以便Postgres验证在获得操作它之前不会拒绝数据。

Another way without a second table, is to create a view on your base table that casts everything to varchar, and then have an INSTEAD OF trigger that populates the base table whenever an insert is tried on the view.

另一种没有第二个表的方法是，在基表上创建一个视图，该视图将所有内容转换为varchar，然后在视图上尝试插入时使用替代触发器填充基表。

For example, the table tab1 below has an integer column. The view v_tab1 has a varchar instead so any insert will work for the view. The instead of trigger then checks to see if the entered value is numeric and if not uses a 0 instead.

例如，下面的表tab1有一个整数列。视图v_tab1具有varchar，因此任何插入都可以用于视图。而不是触发器则检查输入的值是否为数值，如果不是，则使用0。

create table tab1 (i1 int, v1 varchar);

create view v_tab1 as select cast(i1 as varchar) i1, v1 from tab1;

create or replace function v_tab1_insert_trgfun() returns trigger as
$$
declare
  safe_i1 int;
begin
  if new.i1 ~ '^([0-9]+)$' then
     safe_i1 = new.i1::int;
  else
     safe_i1 = 0;
  end if;

  insert into tab1 (i1, v1) values (safe_i1, new.v1);
  return new;
end;
$$
language plpgsql;

create trigger v_tab1_insert_trigger instead of insert on v_tab1  for each row execute procedure v_tab1_insert_trgfun();

Now the inserts will work regardless of the value

现在插入将会工作，不管值是多少

insert into v_tab1 values ('12','hello');
insert into v_tab1 values ('banana','world');
select * from tab1;

Giving

给

|i1   |v1   |
+-----+-----+
|12   |hello|
|0    |world|

Fiddle at: http://sqlfiddle.com/#!15/9af5ab/1

小提琴在:http://sqlfiddle.com/ ! 15/9af5ab / 1

#1