#postgresql
Вопрос:
В Postgresql, каков наилучший способ автоматического создания/обновления/вычисления столбца на основе значения из другой строки в той же таблице?
Например, учитывая эту структуру:
CREATE TABLE "public"."timeline" ( "id" int4 NOT NULL DEFAULT nextval('timeline_id_seq'::regclass), "start_date" date NOT NULL, "end_date" date NOT NULL, PRIMARY KEY ("id") ); INSERT INTO timeline (start_date, end_date) VALUES ('2020-01-01', '2020-11-30'); INSERT INTO timeline (start_date, end_date) VALUES ('2020-12-01', '2021-08-25'); INSERT INTO timeline (start_date, end_date) VALUES ('2021-08-26', '2022-12-31'); Я бы хотел end_date всегда быть на один день раньше следующего start_date . Если start_date он обновлен, он должен автоматически изменить end_date предыдущую запись.
Строки могут быть в любом порядке, и в моем сценарии в одной таблице есть несколько разных временных рядов, принадлежащих разным пользователям.
Как лучше всего реализовать что-то подобное в Postgres? Насколько я понимаю, сгенерированные столбцы, похоже, могут вычисляться только на основе одной и той же строки.
Комментарии:
1. Если ваш идентификатор-это серийный номер(или вы можете использовать ctid в качестве идентификатора последовательности), вы можете запустить триггер, который после обновления переходит непосредственно в id-1 и внести изменения.
Ответ №1:
Вы можете обновить «предыдущую» строку с помощью триггера :
CREATE OR REPLACE FUNCTION after_update_start_date() RETURNS trigger LANGUAGE plpgsql AS $ BEGIN IF NEW.start_date IS DISTINCT FROM OLD.start_date THEN UPDATE timeline AS tl SET end_date = NEW.start_date - 1 WHERE tl.end_date = OLD.start_date - 1 ; END IF; RETURN NEW ; END ; $ ; DROP TRIGGER IF EXISTS after_update_start_date ON timeline ; CREATE TRIGGER after_update_start_date AFTER UPDATE OF start_date ON timeline FOR EACH ROW EXECUTE PROCEDURE after_update_start_date() ; Затем вы можете заменить столбцы start_date и end_date уникальным типом столбца daterange time_period, и триггером будет :
CREATE OR REPLACE FUNCTION after_update_time_period() RETURNS trigger LANGUAGE plpgsql AS $ BEGIN IF lower(NEW.time_period) IS DISTINCT FROM lower(OLD.time_period) THEN UPDATE timeline AS tl SET time_period = daterange(lower(tl.time_period), lower(NEW.time_period)) WHERE upper(tl.time_period) = lower(OLD.time_period) ; END IF; RETURN NEW ; END ; $ ; DROP TRIGGER IF EXISTS after_update_time_period ON timeline ; CREATE TRIGGER after_update_time_period AFTER UPDATE OF time_period ON timeline FOR EACH ROW EXECUTE PROCEDURE after_update_time_period() ; Пожалуйста, обратите внимание, что с daterange типом нижняя граница включена по умолчанию, а верхняя граница исключена по умолчанию, так что в вашем случае верхняя(предыдущая_период) = нижняя(следующая_период).