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)