如何在PostgreSQL中引用` `insert select on conflict update`语句中的选定行

Question

这是一个将供应库存从一个仓库移动到另一个仓库的简单函数。它将所有源库存插入到目标仓库中，然后从源仓库中删除该库存。

这可能是一个愚蠢的语法错误，但我正在努力解析这个函数，因为on conflict (..) update子句无法识别tsrc.stock，错误为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
$$;

我已经尝试了许多方法，甚至使用with，但是找不到一种方法来让第一个update子句知道源数据是tsrc的数据。

有什么想法吗？

Answer 1

您可以使用伪表excluded，它包含建议插入的行：

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