В Postgres PL / pgSQL как мне проверить, есть ли числовое значение в списке разрешенных значений?

#postgresql #plpgsql

Вопрос:

У меня есть таблица Postgres с вызываемым столбцом dosage типа numeric. Я хочу написать триггер для проверки того, что дозировка является целым числом, кратным 3,75, допустимые значения равны 0, 3,75, 7,5 … вплоть до 75 и включают в себя.

Итак, я написал триггер, подобный этому. Здесь я использую generate_series для создания списка разрешенных значений, а затем проверяю, входит ли поле записи dosage в число разрешенных значений. Но это не работает.

   CREATE FUNCTION validate_dosage_value() returns trigger as $
    DECLARE
      allowed_values numeric[];
    BEGIN
      allowed_values := (SELECT array_agg(i)::numeric[] from generate_series(0, 75, 3.75) as i)

      IF NEW.dosage NOT IN allowed_values THEN
        RAISE EXCEPTION 'dosage contains an invalid value';
      END IF;

      RETURN NEW;
    END;
    $ language plpgsql;

Есть идеи, как я могу заставить это работать? (Я знаю, что одна из альтернатив — выполнить деление и проверить результат, но меня больше интересует подход проверки членства в массиве.)

1. Я бы не стал помещать бизнес-логику в жестко запрограммированную функцию или проверять ограничение. Создайте таблицу с разрешенными значениями и выполните проверку по этой таблице. Это дает вам гораздо больше гибкости

Ответ №1:

Синтаксис вашего триггера должен быть примерно таким:

   IF NOT NEW.dosage = ANY(allowed_values) THEN

Но почему бы не использовать ограничение проверки?

 create table drugs (
  id serial primary key,
  dosage float not null 
    check (    dosage >= 0 
           and dosage <= 75 
           and floor(dosage/3.75) = ceiling(dosage/3.75))
);
CREATE TABLE

insert into drugs (dosage) values (-1);
ERROR:  new row for relation "drugs" violates check constraint "drugs_dosage_check"
DETAIL:  Failing row contains (1, -1).

insert into drugs (dosage) values (3.75);
INSERT 0 1

insert into drugs (dosage) values (7.50);
INSERT 0 1

insert into drugs (dosage) values (7);
ERROR:  new row for relation "drugs" violates check constraint "drugs_dosage_check"
DETAIL:  Failing row contains (4, 7).

insert into drugs (dosage) values (75);
INSERT 0 1

insert into drugs (dosage) values (78.75);
ERROR:  new row for relation "drugs" violates check constraint "drugs_dosage_check"
DETAIL:  Failing row contains (6, 78.75).

Ответ №2:

Это работает, хорошо, что оно также короче и устраняет локальную переменную.

 CREATE FUNCTION validate_dosage_value() returns trigger as $
  BEGIN
    IF NEW.dosage NOT IN (SELECT generate_series(0, 75, 3.75)) THEN
      RAISE EXCEPTION 'dosage contains an invalid value';
    END IF;

    RETURN NEW;
  END;
  $ language plpgsql;