Forums PostgreSQL.fr

Michel Depiesse

Membre

Création de triggers pour toutes les tables d'une db

Bonjour,

J'ai fait :

--
-- receives table_name as parameter and adds columns to this table
--
create or replace function add_columns(_t text) returns void as $$
begin
        execute format('alter table %I
                                 add column creation_date       timestamp default current_timestamp
                                ,add column created_by           name default current_user
                                ,add column modification_date timestamp
                                ,add column modified_by         name
                        ', _t);
end
$$ language 'plpgsql';


--
-- calls add_columns for all the tables of the db
--
select
         add_columns(table_name)
from
        information_schema.tables
where
        table_schema = 'public'
order by
        table_name;

et ça marche.

Maintenant, je voudrais ajouter une trigger post update pour chaque table qui mettrait à jour les colonnes modification_date et modified_by avec now() and current_user.

Mais je ne vois pas dans quelle direction aller ? un execute format ? un meta-sql ? pourrais-je faire une procedure unique pour toutes les tables ?

Merci pour votre attention.

Mchl

Michel Depiesse

Membre

Re : Création de triggers pour toutes les tables d'une db

Ben voilà, j'ai fait les deux en un :

--
-- modifies db structure for sync
--

--
-- creates trigger post update for all tables
--
create or replace function update_modification_columns() returns trigger as $$
begin
  new.modification_date := now();
  new.modified_by := current_user;
  return new;
end;
$$ language 'plpgsql';

--
-- add columns and trigger for each tables
--
do $$
declare
    t record;
begin
    for t in select table_name from information_schema.tables where table_schema = 'public'
    loop

        execute format('alter table %I
                                 add column creation_date       timestamp default current_timestamp
                                ,add column created_by          name default current_user
                                ,add column modification_date   timestamp
                                ,add column modified_by         name
                        ', t.table_name);

        execute format('create trigger update_modification_columns
                                before update on %I
                                for each row 
                                when (old.* is distinct from new.*)
                                execute procedure update_modification_columns()
                        ', t.table_name);
    end loop;
end;
$$ language 'plpgsql';

Merci pour votre attention