WITH Tr AS (SELECT
    n.nspname AS trigger_schema,
    t.tgname AS trigger_name,
    em.text AS event_manipulation,        
    c.relname AS trigger_table, 
    np.nspname AS routine_schema,
    p.proname AS routine_name,
     p.prosrc AS routine_definition,
     (SELECT DISTINCT action_condition FROM INFORMATION_SCHEMA.triggers AS it WHERE it.trigger_schema=n.nspname AND it.event_object_table=c.relname AND it.trigger_name=t.tgname) AS action_condition,    
        CASE t.tgtype::integer & 1
            WHEN 1 THEN 'ROW'
            ELSE 'STATEMENT'
        END AS action_orientation,
        CASE t.tgtype::integer & 66
            WHEN 2 THEN 'BEFORE'
            WHEN 64 THEN 'INSTEAD OF'
            ELSE 'AFTER'
        END AS action_timing	
   FROM pg_namespace n,
    pg_class c,
    pg_trigger t,
    pg_proc p,
    pg_namespace np,
    ( VALUES (4,'INSERT'), (8,'DELETE'), (16,'UPDATE'), (32,'TRUNCATE')) em(num, text)
  WHERE n.oid = c.relnamespace 
  AND c.oid = t.tgrelid 
  AND t.tgfoid=p.oid
  AND p.pronamespace=np.oid
  AND (t.tgtype::integer & em.num) <> 0 
  AND NOT t.tgisinternal 
  AND NOT pg_is_other_temp_schema(n.oid)),
modification_time_trigger AS (
SELECT trigger_schema, trigger_table
FROM Tr
WHERE action_orientation='ROW' 
AND action_timing='BEFORE'
AND event_manipulation='UPDATE'
AND routine_definition~* 'NEW\.[^;]*(muutmis|update|change|modify)[^;]*\:=[[:space:]]*(localtimestamp|current_timestamp|now)'),
last_update_time AS (SELECT A.table_schema, A.table_name , A.column_name, A.data_type, coalesce(A.column_default, domain_default) AS default_value
FROM information_schema.columns A
INNER JOIN information_schema.tables T USING (table_schema, table_name)
INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name
LEFT JOIN information_schema.domains d USING (domain_schema, domain_name)
WHERE A.data_type~*'(date|timestamp)'
AND A.column_name~*'(muutmis|update|change|modify)'
AND A.column_name!~*'(jargmine|next)'
AND T.table_type='BASE TABLE'
AND NOT EXISTS (SELECT * FROM modification_time_trigger AS mtt WHERE mtt.trigger_schema=A.table_schema AND mtt.trigger_table=A.table_name)
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres'))
SELECT format('CREATE OR REPLACE FUNCTION f_%1$I_last_update_time() RETURNS TRIGGER AS  
$$
BEGIN    
	NEW.%2$I:=%3$s;
	RETURN NEW;
END
$$
LANGUAGE plpgsql SECURITY DEFINER
SET search_path=public,pg_temp;', table_name, column_name, default_value) AS statements
FROM last_update_time
ORDER BY table_schema, table_name, column_name;

WITH Tr AS (SELECT
    n.nspname AS trigger_schema,
    t.tgname AS trigger_name,
    em.text AS event_manipulation,        
    c.relname AS trigger_table, 
    np.nspname AS routine_schema,
    p.proname AS routine_name,
     p.prosrc AS routine_definition,
     (SELECT DISTINCT action_condition FROM INFORMATION_SCHEMA.triggers AS it WHERE it.trigger_schema=n.nspname AND it.event_object_table=c.relname AND it.trigger_name=t.tgname) AS action_condition,    
        CASE t.tgtype::integer & 1
            WHEN 1 THEN 'ROW'
            ELSE 'STATEMENT'
        END AS action_orientation,
        CASE t.tgtype::integer & 66
            WHEN 2 THEN 'BEFORE'
            WHEN 64 THEN 'INSTEAD OF'
            ELSE 'AFTER'
        END AS action_timing	
   FROM pg_namespace n,
    pg_class c,
    pg_trigger t,
    pg_proc p,
    pg_namespace np,
    ( VALUES (4,'INSERT'), (8,'DELETE'), (16,'UPDATE'), (32,'TRUNCATE')) em(num, text)
  WHERE n.oid = c.relnamespace 
  AND c.oid = t.tgrelid 
  AND t.tgfoid=p.oid
  AND p.pronamespace=np.oid
  AND (t.tgtype::integer & em.num) <> 0 
  AND NOT t.tgisinternal 
  AND NOT pg_is_other_temp_schema(n.oid)),
modification_time_trigger AS (
SELECT trigger_schema, trigger_table
FROM Tr
WHERE action_orientation='ROW' 
AND action_timing='BEFORE'
AND event_manipulation='UPDATE'
AND routine_definition~* 'NEW\.[^;]*(muutmis|update|change|modify)[^;]*\:=[[:space:]]*(localtimestamp|current_timestamp|now)'),
last_update_time AS (SELECT A.table_schema, A.table_name , A.column_name, A.data_type, coalesce(A.column_default, domain_default) AS default_value
FROM information_schema.columns A
INNER JOIN information_schema.tables T USING (table_schema, table_name)
INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name
LEFT JOIN information_schema.domains d USING (domain_schema, domain_name)
WHERE A.data_type~*'(date|timestamp)'
AND A.column_name~*'(muutmis|update|change|modify)'
AND A.column_name!~*'(jargmine|next)'
AND T.table_type='BASE TABLE'
AND NOT EXISTS (SELECT * FROM modification_time_trigger AS mtt WHERE mtt.trigger_schema=A.table_schema AND mtt.trigger_table=A.table_name)
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres'))
SELECT format('CREATE OR REPLACE TRIGGER trig_%1$I_last_update_time
BEFORE UPDATE ON %1$I
FOR EACH ROW 
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE PROCEDURE f_%1$I_last_update_time()', table_name) AS statements
FROM last_update_time
ORDER BY table_schema, table_name, column_name;