How to avoid a `select sum() into` set target variables to null in PostgreSQL

Question

This select sum() is part of a trigger function and it is woking correctly,

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

However, I had to add those horrible coalesce lines to check none of the aggregated results is null. This certainly happens when there are no rows in table order_lines.

Does someone know a more elegant/clean way of doing this, without rechecking for nulls after the select?

Answer 1

Just do this in the SELECT list:

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
...