Say I have a db schema as follows:

CREATE TYPE letter_enum AS ENUM ('A', 'B', 'C', 'D');

CREATE TABLE foo(
  id int,
  letter letter_enum
);

I want to create a procedure such that:

• accepts id and letter
• updates the given id's letter conditionally such that:

1. for A, allows updates A -> B, A -> C, disallows A -> D
2. for B, allows updates B -> D, disallows B -> A, B -> C
3. for C, allows updates C -> D, disallows all other
4. for D, disallows all for D -> *

The reason why there is no pattern between update rules is that, my actual case allows only particular state changes regardless of their showing order. That's why I wanted to simulate the actual case by providing some dummy rules between letter change rules. Thus answers can be explanatory.

I tried to get a query working by MAX/MINs but enum type did restrict me so badly. Also, other questions on SOF are not covering enum type or they rely on the enum definition order. That is where my question differs from others. Thanks in advance.

CodePudding user response：

You need a trigger for that. Something like this:

CREATE FUNCTION letter_check() RETURNS trigger AS
$$
BEGIN
        IF OLD.letter != NEW.letter THEN
                IF OLD.letter='A' AND NEW.letter IN ('B', 'C') THEN
                        RETURN NEW;
                ELSIF OLD.letter='B' AND NEW.letter='D' THEN
                        RETURN NEW;
                ELSIF OLD.letter='C' AND NEW.letter='D' THEN
                        RETURN NEW;
                end if;
                RAISE EXCEPTION 'Changing letter from % to % is not allowed!', OLD.letter, NEW.letter;
        ELSE
                RETURN NEW;
        END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER foo_letter_check BEFORE INSERT OR UPDATE ON foo
    FOR EACH ROW EXECUTE FUNCTION letter_check();

Then:

=> INSERT INTO foo(id, letter) VALUES (1, 'A');
UPDATE foo SET letter='D' WHERE id=1;
ERROR:  Changing letter from A to D is not allowed!
CONTEXT:  PL/pgSQL function letter_check() line 11 at RAISE

=> UPDATE foo SET letter='B' WHERE id=1;
UPDATE 1