event_trigger_exemple.sql

create or replace function fet_exemple()
  returns event_trigger AS
$$
declare
	l_obj record;
	l_rename character varying := '';
	l_col character varying := '';
	l_function_table character varying := '';
	l_function_view character varying := '';
	l_trigger_table character varying := '';
	l_trigger_view character varying := '';
	l_view character varying := '';
	l_view_select character varying := '';
	l_insert_columns character varying := '';
	l_insert_values character varying := '';
	l_update_columns_values character varying := '';
	l_table_schema character varying := '';
	l_table_name character varying := '';
	l_exemple_table_name character varying := '';
	l_ins_upd_new_column_string character varying := '';

	/* Récupération des colonnes de la table en création */
	c1 cursor (c_exemple_table_name character varying) is
		(select column_name from information_schema.columns
		where table_name = c_exemple_table_name

		order by ordinal_position);
begin
/* Boucle sur le tableau des commandes DDL émises au travers de l'event trigger */
for l_obj in select * from pg_event_trigger_ddl_commands()
loop
	/* Détection de la création de la table */
	if l_obj.command_tag = 'CREATE TABLE' then
		l_table_name = substr(l_obj.object_identity,position('.' in l_obj.object_identity) + 1);
		l_exemple_table_name := 'exemple_' || l_table_name;

		l_insert_columns := '(';
		l_insert_values := '(';
		/* Récupération de la liste des colonnes de la table pour créer :
		1) Le select de la vue
		2) la liste des colonnes pour l'insert
		3) la liste des nouvelles valeurs pour l'insert
		4) la liste des colonnes et nouvelles valeurs pour l'update
		et construction des morceaux de requêtes nécessaires pour le trigger des mises à jour
		*/
		open c1(l_table_name);
		fetch c1 into l_col;
		while found
		loop
			if l_col != 'oid' then
				l_ins_upd_new_column_string := l_ins_upd_new_column_string || 'new.'|| l_col || ' := ' || ''''|| '####' || '''' || ' || ' || 'new.'|| l_col || ';' || chr(10);
				l_view_select := l_view_select || 'substr(' || l_col || ',5) ' || l_col || ',';
				l_insert_columns := l_insert_columns || l_col || ',';
				l_insert_values := l_insert_values || 'NEW.' || l_col || ',';
				l_update_columns_values := l_update_columns_values || l_col || ' = NEW.' || l_col || ',';
			end if;
			fetch c1 into l_col;
		end loop;
		close c1;
		l_view_select := substr(l_view_select,1,length(l_view_select) - 1);
		l_insert_columns := substr(l_insert_columns,1,length(l_insert_columns) - 1) || ')';
		l_insert_values := substr(l_insert_values,1,length(l_insert_values) - 1) || ')';
		l_update_columns_values := substr(l_update_columns_values,1,length(l_update_columns_values) - 1);
		
		/* Renommage de la table physique afin que la vue
		qui servira pour les accès à la table ait le nom de la table créée */
		l_rename := 'alter table ' || l_obj.object_identity || ' rename to ' || l_exemple_table_name;
		raise notice '%',l_rename;
		execute l_rename;

		/* Récupération du nom du schéma de la table en cours de création */
		select table_schema into l_table_schema from information_schema.tables where table_name = l_exemple_table_name;
		
		/* Création à la volée de la fonction trigger
		sur la table renommée pour les insertions et les mises à jour */
		l_function_table := 'create or replace function ' || l_table_schema || '.tf_' || l_exemple_table_name || '() returns trigger AS $TG$ 
		begin
			if tg_op = ''INSERT'' or tg_op = ''UPDATE'' then 
				' || l_ins_upd_new_column_string || '
				return new;
			end if;
		end; $TG$
		language plpgsql volatile
		cost 100;';
		raise notice '%',l_function_table;
		execute l_function_table;
		
		/* Création du trigger sur la table renommée pour les insertions et mises à jour */
		l_trigger_table := 'create trigger tg_' || l_exemple_table_name || ' before insert or update on ' || 
					l_table_schema || '.' || l_exemple_table_name || ' for each row 
					execute procedure ' || l_table_schema || '.tf_' || l_exemple_table_name || '();';
		raise notice '%',l_trigger_table;
		execute l_trigger_table;
		
		/* Création de la vue permettant le traitement à la volée des
		select, insert, update, delete */
		l_view := 'create or replace view ' || l_table_schema || '.' || l_table_name || ' as select oid,' || l_view_select || ' from ' || l_exemple_table_name;
		raise notice '%',l_view;
		execute l_view;
		
		/* Création de la fonction trigger qui traitera à la volée les insert, update et delete */
		l_function_view := 'create or replace function ' || l_table_schema || '.tf_' || l_table_name || '() returns trigger AS $FC$ 
		begin
			if tg_op = ''INSERT'' then 
				insert into ' || l_exemple_table_name || l_insert_columns || ' values ' || l_insert_values || ';
				return new;
			end if;
			if tg_op = ''UPDATE'' then
				raise notice ''%'', ''update ' || l_exemple_table_name || ' set ' || l_update_columns_values || ' where oid = OLD.oid'';
				update ' || l_exemple_table_name || ' set ' || l_update_columns_values || ' where oid = OLD.oid;
				return new;
			end if;
			if tg_op = ''DELETE'' then
				delete from ' || l_exemple_table_name || ' where oid = OLD.oid;
				return new;
			end if;
		end; $FC$
		language plpgsql volatile
		cost 100;';
		raise notice '%',l_function_view;
		execute l_function_view;

		/* Création du trigger sur la vue pour traitement des insert, update et delete */
		l_trigger_view := 'create trigger tg_' || l_table_name || ' instead of insert or update or delete on ' || 
					l_table_schema || '.' || l_table_name || ' for each row 
					execute procedure ' || l_table_schema || '.tf_' || l_table_name || '();';
		raise notice '%',l_trigger_view;
		execute l_trigger_view;
		
	end if;
end loop;
end;
$$
  language plpgsql volatile
  cost 100;
  
/* Création de l'event trigger qui déclenchera la gestion globale
de la création des fonctions triggers, des triggers et de la vue
permettant le retraitement des données à la volée */
drop event trigger et_exemple;
create event trigger et_exemple
on ddl_command_end
execute function fet_exemple();