Postgresql 插入触发器以设置值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16102188/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql insert trigger to set value
提问by user1408470
Assume in Postgresql, I have a table Tand one of its column is C1.
假设在 Postgresql 中，我有一个表T，其中一列是C1.
I want to trigger a function when a new record is adding to the table T. The function should check the value of column C1in the new record and if it is null/empty then set its value to 'X'.
我想在表中添加新记录时触发一个函数T。该函数应检查C1新记录中列的值，如果它为空/空，则将其值设置为'X'.
Is this possible?
这可能吗？
回答by Bohemian
You are correct that you need a trigger, because setting a default value for the column won't work for you - default values only work for nullvalues and don't help you in preventing blank values.
您需要触发器是正确的，因为为列设置默认值对您不起作用 - 默认值仅适用于null值，不能帮助您防止空值。
In postgres there are a couple of steps to creating a trigger:
在 postgres 中，创建触发器有几个步骤：
Step 1: Create a function that returns type trigger:
第 1 步：创建一个返回 type 的函数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'
Step 2: Create a trigger that fires beforeinsert, which allows you to change values befre they are inserted, that invokes the above function:
第 2 步：创建一个在插入之前触发的触发器，它允许您在插入之前更改值，从而调用上述函数：
CREATE TRIGGER my_trigger
BEFORE INSERT ON T
FOR EACH ROW
EXECUTE PROCEDURE my_trigger_function()
And you're done.
你已经完成了。
See the above code executing on SQLFIddledemonstrating it working correctly!
查看上面在 SQLFIdle 上执行的代码，证明它可以正常工作！
You mention in a comment that the value 'X'is retrieved from a subquery. If so, change the relevant line so something like:
您在评论中提到该值'X'是从子查询中检索的。如果是这样，请更改相关行，例如：
NEW.C1 := (select some_column from some_table where some_condition);
回答by Steve
It is possible but you are likely to be better off setting a default constraint on the column instead. When creating the table that would look like:
这是可能的，但您最好在列上设置默认约束。创建如下所示的表时：
create table mytable as (
C1 thetype not null default X
);
This says that if you add a row to the table and don't specify the value for C1 then X will be used instead. The not null is not necessary, but prevents updates from nulling that column assuming that's what you want.
这表示如果您向表中添加一行并且不指定 C1 的值，则将使用 X。not null 不是必需的，但可以防止更新将该列置空，假设这是您想要的。
EDIT: This only works for constant X, from your comments it seems there are two possible solutions.
编辑：这仅适用于常量 X，从您的评论看来，有两种可能的解决方案。
Using a trigger would look something like this:
使用触发器看起来像这样：
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();
The newvariable in a trigger function is special, representing the row being inserted. Specifying the trigger as a before inserttrigger means you can modify the row before it is written to the table.
new触发器函数中的变量是特殊的，表示要插入的行。将触发器指定为before insert触发器意味着您可以在将行写入表之前对其进行修改。
The second solution would be to use a computed column which Postgres defines in an unusual way:
第二种解决方案是使用 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;
This creates a function that takes a row of your table and returns a value, you can call it using . notation though, meaning you can do:
这将创建一个函数，该函数接受表中的一行并返回一个值，您可以使用 . 符号虽然，这意味着你可以这样做：
select
*,
t.C1
from
mytable t;
The declaration of the function being immutable is optional, but it's needed if you want to index the "column". You would be able to index this column like this:
不可变函数的声明是可选的，但如果您想索引“列”，则需要它。您可以像这样索引此列：
create index on mytable (C1(mytable));
