How to refer to the selected rows in an `insert select on conflict update` sentence in PostgreSQL

Question

This is a simple function moving supply stocks from one warehouse to another one. It upserts all the source stock into the destination warehouse and then removes the stock from the source warehouse.

This might be a silly syntax error but I am struggling to parse this function, as the on conflict (..) update clause fails to recognize tsrc.stock with the error ERROR: missing FROM-clause entry for table "tsrc".

create or replace function move_stock_to_another_warehouse (
  _src_warehouse_id int,
  _dst_warehouse_id int,
  _supply_id    int
)
returns int
volatile language sql as $$
  -- try to insert a new row into table warehouse_supply for the destination warehouse
  insert into warehouse_supply as tdst (
    warehouse_id, 
    supply_id,
    stock
  ) 
  select 
    _dst_warehouse_id, 
    _supply_id, 
    stock
  from 
    warehouse_supply as tsrc
  where
    warehouse_id = _src_warehouse_id and 
    supply_id = _supply_id
  on conflict (warehouse_id, supply_id) do update
  -- a row in table warehouse_supply existed for the destination warehouse, just increase the stock
  set
    stock = tdst.stock + tsrc.stock;
    
  -- zero the stocks in the source warehouse
  update
    warehouse_supply as tnew
  set
    stock = 0
  from
    warehouse_supply as told
  where 
    tnew.warehouse_id = _src_warehouse_id and 
    tnew.supply_id = _supply_id and
    told.id = tnew.id
  returning
    coalesce(told.stock, 0); -- returns 0 even if the supply did not exist in the source warehouse
$$;

I have tried a number of approaches even using with, but cannot find a way of letting know the first update clause that the source data is that of tsrc.

Any idea?

Answer 1

You can use pseudo-table excluded, which contain the row that was proposed for insertion:

...
on conflict (warehouse_id, supply_id) do update
set stock = tdst.stock + excluded.stock