sql - 通过作为触发器参数传递的列名访问触发器中的记录字段

Question

我有一组表，它们使用鉴别器列来区分实体类型。其中某些表描述了定义的实体组之间的关系，或者包含特定类型实体的更多信息。我想对表中的数据进行完整性检查。最初这些是作为检查编写的，但是 postgres 不能很好地处理备份中的表排序，所以我想将检查重写为触发器，这样我就可以在恢复数据库时关闭触发器。

由于实体的实际列名会因表而异，我想以 DRY 方式进行，因此我尝试编写一个触发器函数，将实体的 ID 作为参数并验证它是正确的类型。

我正在尝试调用一个带参数的函数触发器

c.is_earth_based_measurement_tg(bigint).

实际的触发器是这样写的:

CREATE TRIGGER earth_based_measurement_locations_tg1
  BEFORE INSERT OR UPDATE
  ON measurements.earth_based_measurement_locations
  FOR EACH ROW
    EXECUTE PROCEDURE c.is_earth_based_measurement_tg(NEW.fk_measurement);

当我尝试保存触发器时，我收到“错误:“.”处或附近的语法错误”指的是 NEW.fk_measurement。编写此触发器的正确​​方法是什么？

谢谢。

Answer 1

您不需要将 NEW.anything 作为参数传递；它作为变量 NEW 自动提供给触发器函数。因此，只要 tg_op 是 INSERT 或 UPDATE，您的触发器就可以从其中的任何位置访问 NEW.fk_measurement (NEW 没有为 DELETE 或 TRUNCATE 触发器设置)。

触发器可以带参数，但它们出现在 TG_ARGV 中(如 zero323 注释)。它们不需要用于列值，实际上不能引用列值；它们的目的是用于参数化触发器、具有可选功能的触发器等。

在重新阅读您的问题时，您确实需要一个参数作为感兴趣的列名。不幸的是，通过 NEW 元组中的动态名称访问列是非常尴尬的。

您可以将触发器创建为:

CREATE TRIGGER earth_based_measurement_locations_tg1
  BEFORE INSERT OR UPDATE
  ON measurements.earth_based_measurement_locations
  FOR EACH ROW
    EXECUTE PROCEDURE c.is_earth_based_measurement_tg('fk_measurement');

即将 colname 作为字符串文字传递。

触发程序定义为:

CREATE OR REPLACE FUNCTION is_earth_based_measurement_tg() RETURNS trigger AS $$
BEGIN
  IF tg_op = 'INSERT' OR tg_op = 'UPDATE' THEN
    PERFORM my_func(hstore(NEW) -> TG_ARGV[0]);
  END IF;
END;
$$ LANGUAGE plpgsql;

...或者您想对从列中提取的值执行的任何其他操作。

您不能在当前的 PostgreSQL 版本(直到并包括 9.4)中通过动态列名设置 NEW 中列的值。

请注意，我们将 NEW 元组转换为 hstore，然后通过列名取消引用它。