I'm creating a trigger that triggers
ON INSERT to a table, and I wish to log the structure of tables inserted so I wrote this function:
CREATE OR REPLACE FUNCTION update_table_log_received() RETURNS TRIGGER AS $$ DECLARE added_column TEXT; target_table_name TEXT; old_column text; BEGIN -- Check if a new column has been added IF (TG_OP = 'INSERT') THEN added_column := NEW."COLUMN_NAME"; target_table_name := NEW."TABLE_NAME"; END IF; SELECT column_name into old_column FROM information_schema."columns" WHERE table_schema = 'items' and table_name = LOWER(NEW."TABLE_NAME") and column_name = LOWER(NEW."COLUMN_NAME"); if (coalesce(old_column,'')='' or old_column='' or old_column = added_column) THEN -- If a new column has been added IF (Lower(added_column) != 'sync') then -- Add the new column to the target table EXECUTE 'ALTER TABLE items.' || LOWER(target_table_name)|| ' ADD COLUMN ' || LOWER(added_column) || ' VARCHAR(50)'; END IF; end if; RETURN NEW; END; $$ LANGUAGE plpgsql;
Executed by this TRIGGER:
CREATE TRIGGER update_table_log_received_trigger AFTER INSERT ON items."TABLE_LOG_RECEIVED" FOR EACH ROW EXECUTE FUNCTION update_table_log_received();
I also tried using a temp table:
CREATE TEMP TABLE temp_table AS SELECT column_name FROM information_schema."columns" WHERE table_schema = 'items' AND table_name = LOWER(target_table_name) AND column_name = LOWER(added_column); -- Check if the column exists in the target table SELECT column_name INTO old_column FROM temp_table;
The returned exception:
ERROR: the column « x » of the relation « y » already exists Where: instruction SQL « ALTER TABLE items. ADD COLUMN x VARCHAR(50) »
My problem now is that it isn't supposed to pass the
IF checks (I pasted the code after many alterations I have two
IF conditions that do the same thing). I debugged and logged the statements to note that the select query inside my function returns null apparently. I also tried to use
USING NEW but I am no expert so I couldn't make it work.
Is it a problem with the declared variable not being populated from the
NEW record or am I executing the select statement wrong?
EDIT : tl;dr for my problem, I would like to update a table in Database2 whenever the same table (that had the same structre) is altered from Database1, be it added column or changed column, at this point i'm stuck at the first problem to add the column.