我想写一个这样的存储过程：
@H_404_1@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值.
像这样的东西：
@H_404_1@INSERT INTO my_table(val_1,COALESCE(param_2,DEFAULT));显然是错误的,因为INSERT语句规范明确声明了一个表达式OR DEFAULT可以使用,DEFAULT本身在表达式中不可用.
我自己找到了两种解决方案,但我对它们并不满意.
>从信息模式中选择DEFAULT值,并在COALESCE表达式中使用它.
我不是专家,但似乎应该有一种更简单,更优雅的方式来做到这一点.
>使用INSERT,然后使用UPDATE
像这样：
@H_404_1@-- ... 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语句上运行,因此我通常希望尽可能避免使用更新.
一般来说,我可能会坚持使用第二种解决方案,但这可能需要为上述触发器添加一些黑客攻击.但如果有办法避免这种情况 – 我将非常感激地指出它.
由于param_2只能是null或非null,因此只有其中一个选择将返回要插入的行：
@H_404_1@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
如果有必要返回插入的值：
@H_404_1@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