postgresql - 当参数为 NULL 时将 DEFAULT 值插入列

Question

我想写一个这样的存储过程:

CREATE OR REPLACE FUNCTION my_function(param_1 text, param_2 text DEFAULT NULL::text) RETURNS bigint AS
$$
DECLARE ret bigint;
BEGIN
    INSERT INTO my_table(val_1, val_2) VALUES (param_1, param_2);

    -- do some more stuff

    RETURN ret;
END;
$$
LANGUAGE plpgsql;

但是，我想使用 val_2 列的 DEFAULT 值而不是 NULL - 如果提供了 NULL作为 param_2 值。

像这样:

INSERT INTO my_table(val_1, val_2) VALUES (param_1, COALESCE(param_2, DEFAULT));

显然是错误的，因为 INSERT 语句规范明确指出可以使用表达式 OR DEFAULT，DEFAULT 本身在表达式中不可用。

我自己找到了两个解决方案，但我对它们都不满意。

1. 从信息架构中选择 DEFAULT 值并将其用于 COALESCE 表达式。

我不是专家，但似乎应该有一种更简单、更优雅的方法来做到这一点。

1. 使用INSERT，然后使用UPDATE

像这样:

-- ...
INSERT INTO my_table(val_1) VALUES (param_1)
RETURNING id INTO id_var;

IF (param_2) IS NOT NULL THEN
    UPDATE my_table SET val_2 = param_2 WHERE id = id_var;
END IF;
-- ...

但是这个解决方案有一个问题。生产系统的实际表有一些复杂的触发器，它们在此表上的 UPDATE 语句上运行，因此我通常希望尽可能避免使用更新。

一般来说，我可能会坚持使用第二种解决方案，但这可能需要对上述触发器添加一些 hack。但如果有办法避免这种情况 - 我将非常感谢您指出这一点。

Answer 1

评论有点长。

一种方法是声明列 NOT NULL。插入 NULL 值会产生约束违例，您可以使用 on constraint 在 insert 中捕获它。

这似乎是一种正确的方法。如果列有默认值，那么您可能不希望它为 NULL。