Функция для обновления флага состояния для действительности другого столбца?

Как создать функцию, которая сравнивает card_id с allowed_cards элементами массива?
Если он находится в этом массиве, статус столбца должен быть обновлен до TRUE.

CREATE TABLE client_1 (
  id bigint NOT NULL,
  "time" timestamp without time zone DEFAULT now(),
  status boolean,
  card_id character varying(10),
  CONSTRAINT client_1_pkey PRIMARY KEY (id)
);

CREATE TABLE allowed_cards (
  allowed_cards character varying(10)[]
);

INSERT INTO allowed_cards VALUES ('{DD3234,FF2342}');

postgresql sql database-design check-constraints referential-integrity
person Shohruh Raxmatov    schedule 23.07.2015    source источник
comment
Итак, у вас есть ответ?   -  person Erwin Brandstetter    schedule 28.07.2015

Ответы (1)


arrow_upward
1
arrow_downward

Постоянно применять действительный card_id

Прежде всего, если вам нужна таблица card_allowed, используйте простой столбец varchar или text (с несколькими элементами), а не массив:

CREATE TABLE card_allowed (
  card_id varchar(10) NOT NULL PRIMARY KEY
);

INSERT INTO card_allowed VALUES ('DD3234'), ('FF2342');

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

CREATE TABLE client_1 (
  client_1_id  bigint NOT NULL PRIMARY KEY,
  tstz         timestamptz DEFAULT now(),
  -- card_valid   boolean,
  card_id      varchar(10),
  CONSTRAINT client_1_card_id_fk FOREIGN KEY (card_id) REFERENCES card_allowed
);

Поскольку столбец может быть NULL, вы всегда можете оставить его пустым, если у вас нет действительного card_id.

Вам не нужен дополнительный столбец (вы назвали его status, я переименовал его в card_valid).

Я также изменил имена некоторых столбцов, чтобы сделать их более полезными. Среди прочего, я использую имя столбца tstz вместо time, которое является зарезервированным словом в стандартном SQL и также вводит в заблуждение для фактического столбца timestamptz.


Если вам нужно разрешить недопустимые значения в card_id (правда?), ограничение FK невозможно. Есть и другие варианты:

Проверять только новые записи

Вы можете «подделать» функцию IMMUTABLE, которая запускает проверку:

CREATE OR REPLACE FUNCTION f_card_allowed(text)
  RETURNS bool AS
$func$
SELECT EXISTS (SELECT 1 FROM card_allowed WHERE card_allowed = $1);
$func$
  LANGUAGE sql STABLE;  -- not actually IMMUTABLE

Функция не является действительно неизменной, поскольку зависит от значений из другой таблицы. Так что на самом деле это только STABLE. Результат одного и того же вызова может меняться между транзакциями. По определению CHECKограничения< /strong> ожидают IMMUTABLE функций, но для некоторой свободы действий (особенно с временными функциями) STABLE допускается. Вы должны пометить ограничение CHECK как NOT VALID, чтобы задокументировать это:

ALTER TABLE client_1 ADD CONSTRAINT client_1_card_allowed
CHECK (f_card_allowed(card_id)) NOT VALID;

Разница: Ссылочная целостность не применяется постоянно, как в случае с ограничением FK. Строки проверяются только при вставке/обновлении и в это время должны быть действительными. Никаких обещаний относительно статуса существующих строк не делается: возможно, вы уже изменили значения в card_allowed. Подробности:

Установить статус один раз

Теперь вам нужен дополнительный флаг card_valid, который я закомментировал в определении таблицы выше. У вас нет ограничения FK:

UPDATE client_1 c
SET    card_valid = EXISTS (SELECT 1 FROM card_allowed WHERE card_id = c.card_id);

Устанавливайте статус после каждого изменения

Вы можете сделать то же самое в функции триггера для каждой вставленной/обновленной строки:

CREATE OR REPLACE FUNCTION trg_client_1_insupbef()
  RETURNS trigger AS
$BODY$
BEGIN
   NEW.card_valid := EXISTS (SELECT 1 FROM card_allowed WHERE card_id = NEW.card_id);
   RETURN NEW;
END
$BODY$
  LANGUAGE plpgsql;

CREATE TRIGGER insupbef
BEFORE INSERT OR UPDATE ON client_1
FOR EACH ROW EXECUTE PROCEDURE trg_client_1_insupbef();
person Erwin Brandstetter    schedule 23.07.2015