PostgreSQL 触发器使默认值引用另一个表

Question

PostgreSQL 默认值不能包含变量或引用表​​中或不同表中的任何其他列。

但是，是否可以使用触发器来创建一个将按以下方式运行的“默认值”。首先，让我用两个示例表来说明:

create table projects
(
id serial primary key,
created_at timestamp with time zone default now()
);

create table last_updated
(
project_id integer primary key references projects,
updated_at timestamp with time zone default ...
);

在第二个表 (last_updated) 中，我希望默认值类似于 default projects(created_at)。 IE。如果未为 updated_at 指定日期，请查看 projects 表中引用的 project_id，找到 created_at 日期，并将 updated_at 设置为此日期。但是，您不能按照我问题的第一段来写。

那么您如何编写提供此功能的触发器？

Answer 1

正确答案取决于您未指定的内容。通常，我们会对 projects 表进行更新，然后在 last_updated 表中对其进行审核，使用表 上的 AFTER UPDATE 触发器项目:

CREATE FUNCTION audit_project_update () RETURNS trigger AS $$
BEGIN
  INSERT INTO last_updated VALUES
    (NEW.id,   -- NEW refers to the updated record in the projects table
     now()     -- this would be the logical value, but can use NEW.created_at
     -- other columns, possibly log session_user
  );
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER tr_projects_update
  AFTER UPDATE ON projects
  FOR EACH ROW EXECUTE PROCEDURE audit_project_update();

请注意，在这种方法中，永远不会出现在表 last_updated 上执行 INSERT 而未指定 updated_at 值的情况，假设您不会对表 last_updated 上的任何角色 GRANT INSERT，因为触发器函数始终指定 now()。在表定义中，您不必再指定默认值:触发器会为您提供所需的自动化行为。

您提出的问题 - 并在下面的评论中得到确认 - 也将使用触发器，但随后在 last_updated 表上:

CREATE FUNCTION project_last_updated () RETURNS trigger AS $$
BEGIN
  IF (NEW.updated_at IS NULL) THEN
    SELECT created_at INTO NEW.updated_at
    FROM projects
    WHERE id = NEW.project_id;
  END IF;
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER tr_projects_update
  BEFORE INSERT ON last_updated
  FOR EACH ROW EXECUTE PROCEDURE project_last_updated();

此规范回避了一个问题，即为什么您不简单地向 projects 表中添加一列 updated_at。由于 project_id 列是 last_update 表中的 PK，因此您只能为每个项目存储一个上次更新日期。