PostgreSQL插入触发器设置值

Question

假设在Postgresql中，我有一个名为T的表，其中一个列为C1。

当向表T添加新纪录时，我想触发一个函数。该函数应检查新记录中列C1的值，如果它为空/空白，则将其值设置为'X'。

这是否可行？

- user1408470

---

请注意，此值 X 必须从另一个子查询中检索。 - user1408470

Answer 1

你是正确的，你需要触发器，因为为列设置默认值对你没有作用——默认值只适用于 null 值，并且不能帮助你防止空白值。

在 postgres 中，创建触发器有几个步骤：

步骤 1：创建一个返回类型为 trigger 的函数：

CREATE FUNCTION my_trigger_function()
RETURNS trigger AS $$
BEGIN
  IF NEW.C1 IS NULL OR NEW.C1 = '' THEN
    NEW.C1 := 'X';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql

步骤2：创建一个触发器，调用上述函数并在BEFORE INSERT时触发，这允许您在值进入表之前进行更改：

CREATE TRIGGER my_trigger
BEFORE INSERT ON T
FOR EACH ROW
EXECUTE PROCEDURE my_trigger_function()

完成了。

请参见上述代码在SQLFIddle上的执行结果。

---

您在评论中提到值'X'是从子查询中检索出来的。如果是这样，请将相关行更改为以下内容：

NEW.C1 := (select some_column from some_table where some_condition);

- Bohemian

---

为什么你不用$$而是用'？ - Stefan Falk

@StefanFalk 可能的一个原因是像 DbVisualizer 这样的软件在使用 $$ 时存在分隔符问题。直到你指出这一点，我一直无法使用函数！ - Noumenon

如果您不喜欢“IF”，您可以使用带有coalesce()的单行代码，例如：new.c1 := coalesce (new.c1,X); - Bernardo Jerez

Answer 2

如果需要，你可以这样做，但最好还是在列上设置默认约束。创建表时应该像这样：

create table mytable as (
    C1 thetype not null default X
);

这意味着如果您向表中添加一行并且没有指定C1的值，则会使用X。非空不是必需的，但可以防止更新将该列置空（假设这是您想要的）。

编辑：仅适用于常量X，从您的评论中看来有两种可能的解决方案。

使用触发器可能如下所示：

create function update_row_trigger() returns trigger as $$
begin
    if new.C1 is NULL then
        new.C1 := X;
    end if;
    return new;
end
$$ language plpgsql;

create trigger mytrigger before insert on mytable for each row execute procedure update_row_trigger();

在触发器函数中，new变量是特殊的，表示正在插入的行。将触发器指定为before insert触发器意味着您可以在写入表之前修改行。

第二种解决方案是使用计算列，Postgres以不寻常的方式定义它：

create or replace function C1(row mytable) returns columntype immutable as $$
begin
    return X; -- where X is an expression using values from `row`
end
$$ language plpgsql;

这将创建一个函数，它接受您的表格行并返回一个值，您可以使用“.”符号调用它，这意味着您可以执行：

select
    *,
    t.C1
from
    mytable t;

函数声明为不可变的是可选的，但如果您想索引“列”，则需要该声明。您可以像这样索引此列：

create index on mytable (C1(mytable));

- Steve

---

谢谢，但在我的情况下，当将值设置为默认约束时，我会收到一个错误。因为这个值X是通过子查询获取的，所以不被允许。你能告诉我一个解决方法吗？ - user1408470

C1的取值是否由同一行中的其他值唯一确定，还是取决于其他因素，例如时间或其他表中的数据？ - Steve

它是唯一确定的。简单来说，可以通过以下方式检索到它：(select id from Table2 where name = 'Unique_Value')。这个id在不同的数据库中可能会有所不同，因此必须始终从子查询中检索它。 - user1408470