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

问题描述

如果我有列说明了任何给定值的 列a ，我想另一列 列b 根据 <$ c $的值 默认值 c>列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

换句话说：

如果 列a ='peter' 然后 列b默认值='doctor' p>

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

推荐答案

这是不可能简单的一个 DEFAULT 值，如手册明确说明：

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

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

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

为了使其更有效我使用 WHEN 子句（自Postgres 9.0起可用）与触发器定义。这样触发功能只有在实际使用时才会执行。我假设在这里，我们可以让 b IS NULL slide if a IS NULL 。

To make this more effective I use a WHEN clause (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 NULL slide if a IS NULL.

与 DEFAULT 值相似但略有不同的方式。

With一个默认值，您可以显式地插入 NULL 来覆盖默认值。这不可能在这里， NULL 替换为 b 中的值。

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 is replaced with the value from b.

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