In PostgreSQL I need to code a trigger function that checks if all the fields in
NEW.* exist in another table that has the exact same name as the table that fired the trigger but appended with "_hv".
For example, if the table
my_example fires the trigger I need to check if all the fields in
my_example exist in
my_example_hv, and if it does not, alter
my_example_hv to add the fields that are missing.
I am very new to plpgsql and I starting trying to code it with no success:
CREATE OR REPLACE FUNCTION trigger_hv() RETURNS trigger AS $BODY$ DECLARE typeoffield character varying; BEGIN FOR field IN NEW LOOP IF NEW.field not exists ON TG_TABLE_NAME||'_hv' THEN typeoffield := typeof(NEW.columns); -- of course does not work EXECUTE 'ALTER '||TG_RELNAME||'_hv ADD COLUMN '||NEW.column ||' '||typeoffield; END IF; END LOOP; RETURN NEW; END; $BODY$ LANGUAGE plpgsql;
I think I need some expert help ...