A value of a NEW record seems to be kept as the OLD record between two if/elseif blocks in a Postgre-CodePudding

I have an enum ('not created', 'in creation', 'created', 'updated', 'rejected') and a trigger in PostgreSQL which holds some of the logic for changing the status of a record on an INSERT or UPDATE:

CREATE OR REPLACE FUNCTION set_status()
    RETURNS trigger AS
    $BODY$
    BEGIN
        IF TG_OP ILIKE 'UPDATE' THEN -- this line was added after Pavel Stehule's answer
            IF (NEW.field1 IS NULL) THEN
                NEW.status = 'not created';
            ELSEIF (NEW.field1 IS NOT NULL) THEN
                IF OLD.status IN ('not created')
                THEN
                    NEW.status = 'created'; -- point 1
                ELSEIF OLD.status IN ('created', 'updated', 'rejected') -- point 2
                AND NEW.status NOT IN ('not created')
                THEN
                    NEW.status = 'updated';
                ELSEIF NEW.status NOT IN ('not created', 'created', 'updated', 'rejected')
                THEN
                    NEW.status = 'in creation';
                ELSE
                    NEW.status = 'not created';
                END IF;
            END IF;
        END IF; -- this line was added after Pavel Stehule's answer
        RETURN NEW;
    END
    $BODY$
LANGUAGE 'plpgsql';

CREATE TRIGGER update_status_biut
    BEFORE INSERT OR UPDATE
    ON table
    FOR EACH ROW
    EXECUTE PROCEDURE set_status();

Everything works fine, except that when the status starts with a 'not created' value (it's the default for new records), it directly switch to 'updated' on UPDATE or INSERT where it should stay on 'created' the very first time the record is processed.

To me, it sounds like where I placed the "-- point 1", that the NEW.status is taking the value 'created' here, which is correct, but on the next ELSEIF block, it re-uses that value as the 'OLD.status' (?!), hence verifying the condition, which makes it switch to 'updated'.

I also verified this by trying to remove the 'created' value from the list on "-- point 2". In that case, the status stays gently on 'created' after an UPDATE/INSERT.

How to avoid that strange behaviour and keep the first value which has been met ('created' in that case) in the if-blocks and 'pass' the (following) others?

Version info (Dockerized PostgreSQL):

# psql --version
psql (PostgreSQL) 13.3 (Debian 13.3-1.pgdg100 1)

EDIT: I found the source of my troubles...

Source:

CodePudding user response：

The behaviour of NEW and OLD variables depends on used PostgreSQL version. NEW is well defined for INSERT or UPDATE event. OLD is well defined for UPDATE and DELETE. When you use OLD in INSERT event, then you got an exception on older Postgreses, and today you will get NULL.

Your design is not good. For this case you should to use implicit variable Source: https://www.postgresql.org/docs/13/sql-createtrigger.html

By using this option, the trigger is firing only when updating the specified column(s), which avoid the "artifact" (well, it's not really an artifact, but it's super strange when you first meet that behaviour) of a NEW.not_specified_field (during a partial UPDATE request) which instantaneously (i.e. 'on-the-fly', i.e. prior to its usage inside the trigger function) get assigned the corresponding OLD value from the DB (which you may not want if it's necessary to check for an empty incoming value!).

So that I was able to drop messy checks I had done in the trigger function. It's much more cleaner now.

This post also helped me figuring it out: https://stackoverflow.com/a/8762116/6630397