CREATE TABLE orders(order_id serial, customer varchar, goods_id numeric(5), date date, count numeric(3), price numeric(7,2));

CREATE OR REPLACE FUNCTION orders_after_insert()
returns TRIGGER AS'
DECLARE
whcount warehouse.count%type;
    rscount integer;
    rsorders integer;
    row reserved%rowtype;
BEGIN
--определяем количество на складе
select count into whcount
    from warehouse
    where goods_id = NEW.goods_id;
 if whcount < NEW.count then
--записываем сообщение о нехватке товара
    insert into goods_warnings(goods_id, message, date)
           values(NEW.goods_id, "Недостаточно на складе", now());
--проверяем, есть ли просроченные
    select count(count), sum(count) into rsorders, rscount
           from reserved
           where goods_id = NEW.goods_id and
                 date < now() - "5 day"::interval;
    if rscount + whcount >= NEW.count then
--отменяем просроченный заказ
           for row in select * from reserved where date < now() - "5 days"::interval loop
                update orders set status = "cancelled"
                        where order_id = row.order_id;
                 delete from reserved where order_id = row.order_id;
                 insert into orders_warnings(order_id, message, date)
                        values(row.order_id, "Заказ отменен", now());
           end loop;
--делаем новую запись в таблицу резерва
           insert into reserved(order_id, goods_id, count, date)
                 values(NEW.order_id, NEW.goods_id, NEW.count, NEW.date);
--заносим, если что-то осталось, на склад
           update warehouse set count = count + rscount - NEW.count
                 where goods_id = NEW.goods_id;
    else
--зарезервировать не удалось – заносим соответствующие пометки
           update orders set status = "not reserved"
                 where order_id = NEW.order_id;
           insert into orders_warnings(order_id, message, date)
                 values(NEW.order_id, 
"Товар по заказу не зарезервирован", now());
    end if;
else
--уменьшаем количество на складе
    update warehouse set count = count - NEW.count
          where goods_id = NEW.goods_id;
--делаем запись в таблице резерва
    insert into reserved(order_id, goods_id, count, date)
           values(NEW.order_id, NEW.goods_id, NEW.count, NEW.date);
end if;
return NEW;
End;'
LANGUAGE 'plpgsql';
END;
 
CREATE TRIGGER set_order after INSERT ON orders 
    execute procedure orders_after_insert();

INSERT INTO orders(customer, goods_id, count, price, date)
VALUES('Иванов Иван Иванович', 3, 1, 7302.00, '2004-07-15');

ERROR:  record "new" IS NOT assigned yet
DETAIL:  The tuple structure of a not-yet-assigned record IS indeterminate.
CONTEXT:  PL/pgSQL FUNCTION "orders_after_insert" line 16 at SQL statement
********** Ошибка **********
 
ERROR: record "new" IS NOT assigned yet
SQL state: 55000