在PostgreSQL中使用“BEFORE INSERT”触发器将传入数据的数据类型更改为与列数据类型匹配。

Question

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

我正试图在表上编写一个BEFORE INSERT触发器，以便在数据上调用此函数，以便我可以确保没有插入类型错误。但是似乎它不起作用，即使有触发器，我仍然在尝试插入数据时出现错误。

在我花费太多时间进行调查之前，我想找出是否可能这样做。我能否使用触发器以此方式更改传入数据的类型？

我希望这可以在postgresql 9.3上运行，但我已经注意到了postgres 9.5上的错误和无效触发器。

- Amandasaurus

---

我不确定这是否有效，但你尝试过规则了吗？ - Kevin

Answer 1

正如 Patrick 所述，您需要指定一个宽松的目标，以便在操作数据之前 Postgres 验证不会拒绝数据。

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

例如，下面的表 tab1 有一个整数列。视图 v_tab1 有一个 varchar，因此任何插入都适用于该视图。然后，instead of 触发器检查输入的值是否为数字，如果不是，则使用 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();

现在插入将会正常工作，无论值是什么。

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

提供

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

请在http://sqlfiddle.com/#!15/9af5ab/1上进行示范。

- Gary - Stand with Ukraine

Answer 2

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

同样适用于规则，顺便说一句，所以Kevin在评论中的建议行不通。

可能您最好的解决方案是创建一个具有"宽容"列数据类型（例如text）的暂存表，然后在该表上放置一个BEFORE INSERT触发器，将所有列值转换为其正确的类型，然后将它们插入到最终表中。如果第二次插入成功，您甚至可以从插入中返回NULL，以便该行不会进入表格（不确定COPY对此有何看法...）。那些最终进入表中的记录具有一些奇怪的数据，然后您可以手动处理这些行。

- Patrick