如何在触发器PostgreSQL中存储值 [英] How to store values in trigger PostgreSQL

问题描述

我有一个类似于以下内容的触发器：

I have a trigger that looks something like this:

CREATE OR REPLACE FUNCTION CHECK_SCHEDULE()
RETURNS TRIGGER AS
$BODY$
BEGIN
  IF EXISTS(
    SELECT DAY, TIME FROM MEETING
    WHERE NEW.DAY = MEETING.DAY AND NEW.TIME > MEETING.TIME
    ) THEN
    RAISE EXCEPTION 'THERE IS A MEETING HAPPENING ON % % ', NEW.DAY, NEW.TIME;
  ELSE
    RETURN NEW;
  END IF;
END;
$BODY$ LANGUAGE PLPGSQL;

除了我希望消息是与之冲突的时间之外，此方法可以正常工作：在MEETING.DAY和MEETING.TIME上召开的会议。

This works fine except I want the message to be the time it's conflicting with: There is a meeting happening on MEETING.DAY and MEETING.TIME.

但是我不能这样做，因为它不知道这些变量是什么。可以将值存储在我的select子句中，以便以后使用吗？

However I cannot do this because it doesn't know what these variables are. Is it possible to store the values in my select clause so I can use them later?

推荐答案

您可以移动天和时间到声明的变量（例如 RECORD ）中以供参考

You can move the day and time into a declared variable (e.g. a RECORD) for reference later.

CREATE OR REPLACE FUNCTION CHECK_SCHEDULE()
RETURNS TRIGGER AS
$BODY$
DECLARE

    meetinginfo RECORD;

BEGIN

    SELECT meeting.day, meeting.time
    INTO meetinginfo
    FROM meeting
    WHERE new.day = meeting.day
    AND new.time > meeting.time
    ORDER BY new.time
    LIMIT 1;

    IF FOUND THEN
        RAISE EXCEPTION 'THERE IS A MEETING HAPPENING ON % %', meetinginfo.day, meetinginfo.time;
    END IF;

    RETURN NEW;

END;
$BODY$ LANGUAGE plpgsql;

这篇关于如何在触发器PostgreSQL中存储值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！