PostgreSQL - 根据另一个单元格值设置默认单元格值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16737738/
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 - set a default cell value according to another cell value
提问by mosid
If i have a column say column aof any given values, and i want another column column bto have a default valueaccording to the value of column a
如果我有一列发言权column a任何给定值的，我想另一列column b到一个有default value根据的价值column a
In another words:
if column a = 'peter'then column b default value = 'doctor'.
换句话说：
如果column a = 'peter'那么column b default value = 'doctor'。
回答by Erwin Brandstetter
This is not possiblewith a simple DEFAULTvalue, as the manual clearly states:
这对于简单的值是不可能的DEFAULT，因为手册明确指出：
The value is any variable-free expression (subqueries and cross-references to other columns in the current table are not allowed).
该值是任何无变量表达式（不允许对当前表中的其他列进行子查询和交叉引用）。
You could use a triggerinstead:
您可以改用触发器：
CREATE OR REPLACE FUNCTION trg_foo_b_default()
RETURNS trigger AS
$func$
BEGIN
-- For just a few constant options, CASE does the job:
NEW.b :=
CASE NEW.a
WHEN 'peter' THEN 'doctor'
WHEN 'weirdo' THEN 'shrink'
WHEN 'django' THEN 'undertaker'
ELSE NULL
END;
/* -- For more, or dynamic options, you could use a lookup table:
SELECT INTO NEW.b t.b
FROM def_tbl t
WHERE t.a = NEW.a;
*/
RETURN NEW;
END
$func$ LANGUAGE plpgsql;
CREATE TRIGGER b_default
BEFORE INSERT ON foo
FOR EACH ROW
WHEN (NEW.b IS NULL AND NEW.a IS NOT NULL)
EXECUTE PROCEDURE trg_foo_b_default();
To make this more effective I use a WHENclause (available since Postgres 9.0) with the trigger definition. This way the trigger function is only executed, when it's actually useful. I am assuming here, we can let b IS NULLslide if a IS NULL.
为了使其更有效，我WHEN在触发器定义中使用了一个子句（自 Postgres 9.0 起可用）。这样触发器函数只会在它真正有用的时候才被执行。我在这里假设，我们可以让b IS NULL幻灯片 if a IS NULL。
Works in a similar, but subtly differentfashion from a DEFAULTvalue.
With a default value, you can explicitly insert NULLto overrule the default. That's not possible here, NULLin bis replaced with the value derived from a.
以与值类似但略有不同的方式工作DEFAULT。
使用默认值，您可以显式插入NULL以否决默认值。这在这里是不可能的，NULLinb被替换为从 派生的值a。
