How to store values in trigger PostgreSQL

Question

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;

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.

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?

Why don't you use a range datatype and a constraint? This is available as of version 9.2 and works better than your solution: With concurrent inserts your check might fail because the other insert hasn't been committed yet. postgresql.org/docs/current/interactive/… — Frank Heikens
– Frank Heikens, Commented Mar 6, 2015 at 9:12

user1322772 · Accepted Answer · 2015-03-05 22:03:57Z

2

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;

answered Mar 5, 2015 at 22:03

user1322772

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to store values in trigger PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related