Last active
June 15, 2020 09:42
-
-
Save achampav/ae03639bc80314134cbde914fa42140f to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Je le sais, oui. Ce fichier fait partie d'une article fait à une époque précédant la version 12.
Merci tout de même de l'avoir précisé pour d'autres qui tomberaient sur ce script qui est obsolète à partir de la version 12