当参数为NULL时将DEFAULT值插入到列中 [英] Inserting DEFAULT value into a column when a parameter is NULL
问题描述
我想这样写一个存储过程:
I would like to write a stored procedure like this:
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;
但是,如果要提供NULL
作为param_2
值,我想使用val_2
列的DEFAULT
值而不是NULL
.
However, I would like to use val_2
column's DEFAULT
value instead of NULL
- if NULL
is provided as the param_2
value.
类似这样的东西:
INSERT INTO my_table(val_1, val_2) VALUES (param_1, COALESCE(param_2, DEFAULT));
显然是错误的,因为INSERT
语句规范明确声明了表达式 OR DEFAULT
可以使用,所以DEFAULT
本身在表达式中不可用.
is obviously wrong, since the INSERT
statement specification explicitly states an expression OR DEFAULT
can be used, DEFAULT
itself is not available in expressions.
我自己找到了两个解决方案,但我对它们不满意.
I found two solutions myself but I'm not satisfied with them.
- 从信息模式中选择
DEFAULT
值,并在COALESCE
表达式中使用它.
- Select the
DEFAULT
value from the information schema and use it in theCOALESCE
expression.
我不是专家,但似乎应该有一种更简单,更优雅的方法来实现它.
I'm no expert but it seems like there should be a simpler and more elegant way to do it.
- 使用
INSERT
,然后使用UPDATE
- Use
INSERT
and thenUPDATE
赞:
-- ...
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
语句上运行,因此,我通常希望避免使用更新.
There is however a catch in this solution. The actual table of the production system has some intricate triggers which run on UPDATE
statements on this table so I would generally like to avoid using updates if possible.
通常,我可能会坚持第二种解决方案,但这可能需要向上述触发器添加一些技巧.但是,如果有办法避免这种情况,我将非常感谢您指出这一点.
Generally, I'll possibly stick to the second solution but that would possibly require adding some hacks to the aforementioned triggers. But if there is a way to avoid this - I will be very grateful for pointing it out.
推荐答案
由于param_2
只能是null
或not null
之一,因此只有一个select会返回要插入的行:
As param_2
can only be one of null
or not null
only one of the selects will return a row to be inserted:
with i as (
insert into my_table (val_1)
select param_1
where param_2 is null
)
insert into my_table (val_1, val_2)
select param_1, param_2
where param_2 is not null
如果有必要返回插入的值:
If it is necessary to return the inserted values:
with i_null as (
insert into my_table (val_1)
select param_1
where param_2 is null
returning *
), i_notnull as (
insert into my_table (val_1, val_2)
select param_1, param_2
where param_2 is not null
returning *
)
select * from i_null
union all
select * from i_notnull
这篇关于当参数为NULL时将DEFAULT值插入到列中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!