Instantly share code, notes, and snippets.

Embed
What would you like to do?
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