[HELP] PG Trigger on before update

Hey everyone,

I'm seeking assistance with implementing a trigger in PostgreSQL. I'm currently struggling with crafting a trigger that functions as follows:

When a new record is inserted, if it contains a value for a specific column, I want to update the corresponding column in the existing record with this new value.

However, if the new record doesn't contain a value for this column (i.e., it's null, an empty object, or an empty array), I'd like to retain the old value in the existing record.

I've made an attempt by updating the NEW statement, but unfortunately, it hasn't worked as expected. Any guidance or assistance you can provide would be greatly appreciated. Thank you in advance for your help!

CREATE OR REPLACE FUNCTION coalesce_only_valuable_columns()
    RETURNS TRIGGER AS
$$
DECLARE
    col_name  text;
    col_value text;
BEGIN
    -- Loop through each column in the NEW row
    FOR col_name IN SELECT column_name
                    FROM information_schema.columns
                    WHERE table_name = TG_TABLE_NAME
        LOOP
            -- Get the value of the column
            EXECUTE format('SELECT $1.%I::text', col_name) INTO col_value USING NEW;

            -- If the new value is null or empty, set it to the old value
            IF col_value IS NULL OR col_value = '{}' OR col_value = '[]' THEN
                EXECUTE format('SELECT $1.%I::text', col_name) INTO col_value USING OLD;

                -- It does not work, so need another solution
                NEW[col_name] := col_value;
            END IF;
        END LOOP;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert_update_companies_coalesce_only_valuable_columns_trigger
    BEFORE UPDATE
    ON "companies"
    FOR EACH ROW
EXECUTE FUNCTION coalesce_only_valuable_columns();

Blog

[HELP] PG Trigger on before update

Ilotus

Join Our Newsletter. No Spam, Only the good stuff.

Related