Задержать исполнение тригера.

From: Aln Kapa <alnkapa(at)gmail(dot)com>
To: pgsql-ru-general(at)lists(dot)postgresql(dot)org
Subject: Задержать исполнение тригера.
Date: 2018-03-14 12:16:44
Message-ID: CAJqqVEX2u+fJyLRbSpzZfJk1DDazgKsWWZpQjWnyqSZ++PJLjw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-ru-general

Добрый есть такая структура:
CREATE TABLE "MAIN"."DEVICES" (
"ID" BIGSERIAL PRIMARY KEY,
"EXTRA_DATA" JSONB DEFAULT '{}'
);

CREATE TABLE "MAIN"."USERS_DEVICES" (
"ID_DEVICE" BIGINT NOT NULL REFERENCES "MAIN"."DEVICES" ("ID") ON UPDATE
CASCADE ON DELETE CASCADE,
"ID_USER" BIGINT NOT NULL REFERENCES "MAIN"."USERS" ("ID") ON UPDATE
CASCADE ON DELETE CASCADE,
"ACCESS" INTEGER DEFAULT 0,
CONSTRAINT "UD_PK" PRIMARY KEY ("ID_DEVICE", "ID_USER")
);
CREATE OR REPLACE FUNCTION "MAIN"."after_device_update_insert_delete"()
RETURNS TRIGGER AS
$$
BEGIN
IF TG_OP = 'UPDATE' THEN
IF OLD."ID" <> NEW."ID" THEN
RAISE EXCEPTION 'Нельзя менять id';
END IF;
PERFORM pg_notify('devices_event_update',json_build_object('id',NEW."ID",
'ext',NEW."EXTRA_DATA",
'user_access_list',(SELECT json_object_agg("ID_USER", "ACCESS") from
"MAIN"."USERS_DEVICES" WHERE "ACCESS" > 0 AND "ID_DEVICE"=NEW."ID")
)::text);
RETURN NEW;
ELSIF TG_OP = 'INSERT' THEN
--RAISE EXCEPTION 'ID % SELECT %',NEW."ID", (SELECT "ID_USER"
from "MAIN"."USERS_DEVICES" WHERE "ID_DEVICE"=NEW."ID");
PERFORM pg_notify('devices_event_update',json_build_object('id',NEW."ID",
'ext',NEW."EXTRA_DATA",
'user_access_list',(SELECT json_object_agg("ID_USER", "ACCESS") from
"MAIN"."USERS_DEVICES" WHERE "ACCESS" > 0 AND "ID_DEVICE"=NEW."ID")
)::text);
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
PERFORM pg_notify('devices_event_delete',json_build_object('id',OLD."ID",
'ext',OLD."EXTRA_DATA",
'user_access_list',(SELECT json_object_agg("ID_USER", "ACCESS") from
"MAIN"."USERS_DEVICES" WHERE "ACCESS" > 0 AND "ID_DEVICE"=OLD."ID")
)::text);
RETURN OLD;
END IF;
END
$$
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER trig01_after_update_insert_delete AFTER INSERT OR UPDATE OR
DELETE ON "MAIN"."DEVICES" FOR EACH ROW EXECUTE PROCEDURE
"MAIN"."after_device_update_insert_delete"();
Из программы делаю:
BEGIN
INSERT в "MAIN"."DEVICES" (узнали "ID")
INSERT в "MAIN"."USERS_DEVICES"
COMMIT
В документации пишут что тригер стартует после COMMIT, но при этом в
user_access_list приходит null.
Каким образом получить в user_access_list нормальные данные, они точно там
есть !!!!

Responses

Browse pgsql-ru-general by date

  From Date Subject
Next Message Dmitry Sinina 2018-03-14 13:32:39 Re: Задержать исполнение тригера.
Previous Message Aln Kapa 2018-03-14 12:06:52 Отложить исполнение тригера.