Я пытался назначить локальную переменную (ну, фактически две), используя SET предложение update предложения, состоящего из нескольких строк. Хорошо, я делаю это в MySQL.

 drop table if exists stocks cascade;

create table stocks (
  id              serial,
  stock_available int,
  stock_locked    int
);

insert into stocks(stock_available, stock_locked) values 
(150, 10),
(150, 20),
(150, 0),
(100, 0),
(100, 100),
(100, 30),
(100, 0),
(100, 50),
(100, 0);

create or replace function lock_all ()
returns int
language plpgsql as $$
declare
  _amount int;
  _total int;
begin
  -- initialize accumulator
  _total = 0;

  -- update all the stocks table rows
  update stocks
  set    _amount = stock_available,
         stock_locked = stock_locked   _amount,
         _total = _total   _amount;
  
  -- returns the units locked 
  return _total;
end;
$$;
  

И, к сожалению, это не тот способ, которым PostgreSQL ожидает сделать такую вещь.

  SQL Error [42703]: ERROR: column "_amount" of relation "stocks" does not exist
  Where: PL/pgSQL function lock_all() line 10 at SQL statement
  

Это всего лишь упрощенный пример, иллюстрирующий реальную проблему подсчета / суммирования количества обновленных элементов в update предложении. Я уверен, что для этого конкретного примера могут быть хитрости или способы обхода, но меня интересует общее решение для подобных ситуаций, когда необходимо вычислить накопитель.

Есть идеи?

Редактировать

Следуя предложению @GMB, я связываю 3 cte

 create or replace function lock_all3 ()
returns int
language sql as $$
  with 
    cte1 as (
      select 
        sum(stock_locked)::int as initially_locked 
      from 
        stocks
    ), 
    cte2 as (
      update 
        stocks 
      set 
        stock_locked = stock_locked   stock_available,
        stock_available = 0
      returning 
        0 as dummy
    ),
    cte3 as (
      select 
        sum(stock_locked)::int as finally_locked 
      from 
        stocks
    )
  select 
    (cte3.finally_locked - initially_locked - dummy) 
  from 
    cte1, cte2, cte3;
$$;
  

Это должно сработать, но результирующее значение указывает, что оба выбора выполняются для предварительных значений таблицы stocks , поскольку разница равна 0.

 select lock_all3();

lock_all3|
---------|
        0|
  

Однако выполняется cte2, поскольку конечная ситуация указывает на то, что все доступные запасы были заблокированы.

 select * from stocks;

id|stock_available|stock_locked|
--|---------------|------------|
 1|              0|         160|
 2|              0|         170|
 3|              0|         150|
 4|              0|         100|
 5|              0|         200|
 6|              0|         130|
 7|              0|         100|
 8|              0|         150|
 9|              0|         100|
  

В этом приближении все еще должно быть что-то не так.

Я думаю, что хитрость заключается в том, чтобы вычислить общее значение перед обновлением.

Используя только SQL

   DROP TABLE x;
SELECT sum(stock_available) as total_moved
  INTO TEMP TABLE x
  FROM stocks as total_moved; 
UPDATE stocks 
   SET stock_locked = stock_available   stock_locked,
       stock_available = 0;
SELECT * from x;
  

 total_moved|
-----------|
       1050|
  

Использование хранимой процедуры

 create or replace function lock_all ()
returns int
language plpgsql as $$
declare
  _total int;
begin
    --calculate total before update
    SELECT sum(stock_available)
      INTO _total
      FROM stocks;

    UPDATE stocks
       SET stock_locked = stock_locked   stock_available,
           stock_available = 0;

    return _total;
end;
$$;
  

выберите * из lock_all;

 lock_all|
--------|
    1050|
  

выберите * из запасов;

 id|stock_available|stock_locked|
--|---------------|------------|
 1|              0|         160|
 2|              0|         170|
 3|              0|         150|
 4|              0|         100|
 5|              0|         200|
 6|              0|         130|
 7|              0|         100|
 8|              0|         150|
 9|              0|         100|
  

Я не думаю, что такая конструкция может работать в Postgres; даже в MySQL было бы невозможно — или, по крайней мере, безопасно — использовать переменные таким образом.

Я думаю, что понимаю, что вы хотите отслеживать общий запас, доступный до выполнения обновления. Возможно, было бы проще просто использовать два разных запроса для этого:

 select sum(stock_available) total from stocks returning total into _total;
update stocks set stock_locked = stock_locked   stock_available;
  

Если вы хотите избежать условий гонки, вы можете включить их в транзакцию или записать ее в виде одного оператора:

 with cte as (update stocks set stock_locked = stock_locked   stock_available)
select sum(stock_available) total from stocks returning total into _total;
  

Это грязное решение, которое выполняет столько, updates сколько строк в таблице stocks . Это работает, но это то решение, которого я пытался избежать.

 create or replace function lock_all ()
returns int
language plpgsql as $$
declare
  _amount int;
  _total int;
 r record;
begin
  -- initialize counter
  _total = 0;

    -- select stocks rows
    for r in (
        select * from stocks
    ) 
    loop
        _amount = r.stock_available;
    
      -- update the stock_fulfilled column in of_user_order_line_supply
      update stocks
      set        stock_locked = stock_locked   _amount
      where  id = r.id;
     
     _total = _total   _amount;
    end loop;

return _total;
end;
$$;

select lock_all();
select * from stocks;

lock_all|
--------|
    1050|