mercredi 25 mars 2015

plpgsql trigger function check if all NEW.* fields exist in another table


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