如何避免在PostgreSQL中将select sum()设置为null

Question

这个select sum()是触发函数的一部分，它工作正常，

-- loop over all order lines without grouping
select
sum(tp.pack_volume),
sum(tp.pack_mass),
sum(tl.qty_ordered),
sum(tl.qty_fulfilled)
into
new.pack_expected_volume,
new.pack_expected_mass,
new.qty_ordered,
new.qty_fulfilled
from 
order_lines tl
join 
product tp on tl.product_id = tp.id
where
tl.order_id = new.id;
-- avoid nulls if no rows in of_order_lines
new.pack_expected_volume = coalesce (new.pack_expected_volume, 0);
new.pack_expected_mass = coalesce (new.pack_expected_mass, 0);
new.qty_ordered = coalesce (new.qty_ordered, 0);
new.qty_fulfilled = coalesce (new.qty_fulfilled, 0);

然而，我不得不添加那些可怕的coalesce行，以检查聚合结果中没有一个为空。当表order_lines中没有行时，肯定会发生这种情况。

有人知道一种更优雅/干净的方法来做到这一点，而不必在select之后重新检查null吗？

Answer 1

只需在SELECT列表中执行此操作：

select
coalesce(sum(tp.pack_volume),0),
coalesce(sum(tp.pack_mass),0),
coalesce(sum(tl.qty_ordered),0),
coalesce(sum(tl.qty_fulfilled),0)
into
new.pack_expected_volume,
new.pack_expected_mass,
new.qty_ordered,
new.qty_fulfilled
from 
order_lines tl
...