PostgreSQL - 根据另一个单元格值设置默认单元格值 [英] PostgreSQL - set a default cell value according to another cell value

问题描述

如果我有一列说 column a 的任何给定值，并且我希望另一列 column b 有一个 default value 根据到列a

If i have a column say column a of any given values, and i want another column column b to have a default value according to the value of column a

换句话说:
if column a = 'peter' then column b 默认值 = 'doctor'.

In another words:
if column a = 'peter' then column b default value = 'doctor'.

推荐答案

使用简单的 DEFAULT 值不可能，例如 手册明确指出:

This is not possible with a simple DEFAULT value, as the manual clearly states:

该值是任何无变量表达式(子查询和不允许交叉引用当前表中的其他列).

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 trigger instead:

CREATE OR REPLACE FUNCTION trg_foo_b_default()
  RETURNS trigger
  LANGUAGE plpgsql 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, consider a lookup table:
   SELECT INTO NEW.b  t.b
   FROM   def_tbl t
   WHERE  t.a = NEW.a;
   */

   RETURN NEW;
END
$func$;


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();

为了提高效率，在触发器定义中使用 WHEN 子句(从 Postgres 9.0 开始可用):这样触发器函数只在它真正有用时才执行.(假设我们可以让 b IS NULL 在 a IS NULL 滑动.)

To make it more efficient use a WHEN clause in the trigger definition (available since Postgres 9.0): This way the trigger function is only executed, when it's actually useful. (Assuming we can let b IS NULL slide if a IS NULL.)

与 DEFAULT 值的工作方式类似，但略有不同.
使用默认值，您可以显式插入 NULL 以否决默认值.这在这里是不可能的，b 中的 NULL 被替换为从 a 派生的值.

Works in a similar, but subtly different fashion from a DEFAULT value.
With a default value, you can explicitly insert NULL to overrule the default. That's not possible here, NULL in b is replaced with the value derived from a.

这篇关于PostgreSQL - 根据另一个单元格值设置默认单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！