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 a very newbie plpgsql coder 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 VOLATILE
COST 100;
ALTER FUNCTION trigger_hv() OWNER TO postgres;
I think I need some expert help...
Aucun commentaire:
Enregistrer un commentaire