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();
Top comments (0)