Postgres create trigger function to copy value from one column to another before allowing insert

Question

I am trying to create a trigger function in Postgres 13 that, before an insert or update operation is done, will copy values from two columns to two other columns.

Before an update/insert operation my table test.run_conf looks like this:

hostname	run_config_current	run_config_current_hash	run_config_last	run_config_last_hash
switch01	old_txt_str	32314322

When an update/insert occurs I want the value from run_config_current copied to run_config_last and run_config_current_hash copied to run_config_last_hash. Then allow the update/insert operation to occur against column run_config_current - after which the trigger function will recalculate the value for run_config_current_hash.

For testing, I run the following query to insert new data into the column run_config_current:

INSERT INTO test.run_conf (hostname, run_config_current) VALUES ('switch01', 'new_txt_str' )
ON CONFLICT (hostname) DO UPDATE SET run_config_current = excluded.run_config_current

However what happens is the table test.run_conf gets updated as follows - the new value gets inserted into both columns run_config_current and run_config_last:

hostname	run_config_current	run_config_current_hash	run_config_last	run_config_last_hash
switch01	new_txt_str	47173234	new_txt_str	32314322

The trigger and function I have in place now that is not working properly is:

CREATE OR REPLACE FUNCTION test.run_conf_hash_gen_func()
    RETURNS TRIGGER
    SET SCHEMA 'test'
    LANGUAGE plpgsql
    AS $$
    BEGIN
        NEW.run_config_last := NEW.run_config_current;
        NEW.run_config_last_hash := NEW.run_config_current_hash;
        NEW.run_config_current_hash := MD5(NEW.run_config_current);
    RETURN NEW;
    END;
    $$

CREATE TRIGGER run_conf_hash_gen_trig
    BEFORE INSERT OR UPDATE on test.run_conf
    FOR EACH ROW
    EXECUTE PROCEDURE test.run_conf_hash_gen_func();

Please help me to understand why my code seems to be copying the new value for run_config_current into run_config_last. Thank you!

Because you need the OLD.run_config_current when doing the UPDATE. This means that in the trigger function you will need to distinguish between and INSERT or UPDATE as INSERT does not have OLD values. See TG_OP here plpgsql trigger function. — Adrian Klaver
– Adrian Klaver, Commented Oct 26, 2021 at 22:19
Turns out in Postgres 11+ OLD for INSERT is set to NULL so you do not have to guard against the pre-11 behavior of OLD being unassigned in an INSERT. — Adrian Klaver
– Adrian Klaver, Commented Oct 26, 2021 at 22:45

refriedjello · Accepted Answer · 2021-10-26 22:18:32Z

0

I figured this out. My issue was that I was referencing the NEW var as reference for the values to assign. Since the vars NEW and OLD are set at trigger execution, but before changes, my old code was using the values I sent via my INSERT/UPDATE statement as what to assign to my _last columns.

Here is my updated trigger function that works as expected. I commented out the old/bad lines and the new lines underneath are what is working:

CREATE OR REPLACE FUNCTION test.run_conf_hash_gen_func()
    RETURNS TRIGGER
    SET SCHEMA 'test'
    LANGUAGE plpgsql
    AS $$
    BEGIN
        -- NEW.run_config_last := NEW.run_config_current;
        NEW.run_config_last := OLD.run_config_current;
        -- NEW.run_config_last_hash := NEW.run_config_current_hash;
        NEW.run_config_last_hash := OLD.run_config_current_hash;
        NEW.run_config_current_hash := MD5(NEW.run_config_current);
    RETURN NEW;
    END;
    $$

answered Oct 26, 2021 at 22:18

refriedjello

83711 silver badges28 bronze badges

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

5 Comments

Adrian Klaver Over a year ago

That will not work when the action is an INSERT as there is no OLD row in an insert.

refriedjello Over a year ago

@AdrianKlaver yep and that is actually the desired behavior. If its an INSERT, its a brand new entry for the device in question, so there is no "last" comparison to make. On the next run of my code, it will re-fetch configurations from devices and then do an UPDATE where an entry already exists, causing those other 2 columns to populate.

Adrian Klaver Over a year ago

When I said it will not work I meant just that, the INSERT will fail totally. Pretty sure that is not what you want.

refriedjello Over a year ago

@AdrianKlaver Hmm. My OP includes the insertion query my code will use with an "ON CONFICT" clause. Running it several times it is working as expected. I also run simple test INSERTs using unique hostnames and those work as well.

Adrian Klaver Over a year ago

Hmm, it turns out I need to keep up with the docs. In Postgres 10- 'This variable is unassigned in statement-level triggers and for INSERT operations.' and in 11+ 'This variable is null in statement-level triggers and for INSERT operations.'. Habit has me coding for the pre-11 behavior. Probably still will, but your code is good.

Collectives™ on Stack Overflow

Postgres create trigger function to copy value from one column to another before allowing insert

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related