Блог

order.date должен находиться между item.date_from и item.date_to… каковы различные способы сделать это?

 CREATE TABLE "item" (
  "id" SERIAL PRIMARY KEY,
  "date_from" DATE NOT NULL,
  "date_to" DATE NOT NULL
);

CREATE TABLE "order" (
  "id" SERIAL PRIMARY KEY,
  "date" DATE NOT NULL
);

CREATE TABLE "order_item" (
  "order" INTEGER NOT NULL REFERENCES "order",
  "item" INTEGER NOT NULL REFERENCES "item" 
);
 

Проверяющие ограничения работают с простыми выражениями. Например, простая проверка на вменяемость заказа: check( date > '2010-01-01') . Существуют также ограничения исключения, которые проверяют, что никакие две строки не имеют одного и того же значения, определенного исключением. Но, за исключением ограничений внешнего ключа, ограничения не запрашивают другие таблицы.

Вы можете решить эту проблему с помощью триггера insert и update , и я расскажу об этом ниже, но лучше решать такого рода проблемы с помощью ссылочной целостности. Однако я не могу придумать, как это сделать.

Вы можете просмотреть доступные товары для заказа. Вот $1 дата заказа.

 create temporary view items_available_to_order
  select *
  -- pluralize table names to avoid conflicting with keywords and columns
  from items
  -- date_from and date_to has become a single daterange when_available
  where items.when_available @> $1
 

Затем вставляйте только элементы из этого представления.

Если вы хотите пройти маршрут запуска (вы можете сделать и то, и другое), напишите функцию, которая проверяет, является ли товар заказа действительным. Он либо вызывает исключение, либо возвращает триггер. new является вставленной строкой или строкой после обновления.

Я изменил некоторые имена и типы таблиц и столбцов, чтобы избежать распространенных ошибок.

 create function check_item_order_is_valid()
  returns trigger
  language 'plpgsql'
as $body$
declare
  item_is_available boolean;
begin
  select
    items.when_available @> orders.ordered_on into item_is_available
  from item_orders
  join items on items.id = new.order_id
  join orders on orders.id = new.item_id;

  if( not item_is_available) then
    raise exception 'Item #% is not available for order #%',
      new.item_id, new.order_id;
  end if;

  return new;
end
$body$
 

Затем определите триггер для вызова функции при вставке или обновлении строк в таблице номенклатура/заказ.

 create trigger check_item_orders
  before insert or update
  on item_orders
  for each row
  execute function check_item_order_is_valid();
 

Демонстрация.

Что делать, если допустимый диапазон элемента изменится? Вам нужен триггер обновления товара, чтобы проверить, что его заказы все еще действительны. Может быть. Зависит от вашей бизнес-логики.

Тестовый пример:

 CREATE OR REPLACE FUNCTION public.item_date()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    order_date date;
    from_date date;
    to_date date;
BEGIN
    select into order_date "date" from "order" where id = new.order;
    select into from_date, to_date date_from, date_to from item where id = new.item;
    --Use date range to test whether order date is in item date range.
    if order_date <@ daterange(from_date, to_date, '[]') then
        return new;
    else
        return null;
    end if;
END;
$function$

create trigger item_date_check before insert or update on order_item for each row execute function item_date();

insert into item values (1, '09/01/2021', '10/31/2021');
insert into item values (2, '07/01/2021', '08/31/2021');
insert into "order" values (1, '09/05/2021');

insert into order_item values (1, 1);
NOTICE:  Order date 2021-09-05, from_date 2021-09-01, to_date 2021-10-31
INSERT 0 1

--Returning NULL causes the INSERT not to happen.
insert into order_item values (1, 2);
NOTICE:  Order date 2021-09-05, from_date 2021-07-01, to_date 2021-08-31
INSERT 0 0


 

Обратите внимание, что мне пришлось процитировать «заказ», так как это тоже зарезервированное слово. Вы можете взглянуть на ключевые(зарезервированные) слова. range Функции/операторы см. в разделе Функция диапазона. Для получения общей информации о range (ов) см. Типы диапазонов