Instantly share code, notes, and snippets.

Embed
What would you like to do?
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();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment