Добрый есть такая структура:
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 нормальные данные, они точно там есть !!!!