do language plpgsql
 $$
declare
    my_version  integer[];
    my_cmd      text;
begin
    -- If on PostgreSQL 9.3.3 or above, add a DDL trigger to run
    -- fn_update_audit_fields() automatically. Use EXECUTE to avoid syntax
    -- errors during installation on older versions.

    my_version := regexp_matches(version(), 'PostgreSQL (\d)+\.(\d+)\.(\d+)');

    if my_version >= array[9,3,3]::integer[] then
        my_cmd := E'CREATE OR REPLACE FUNCTION @extschema@.fn_update_audit_fields_event_trigger() \n'
               || E'returns event_trigger \n'
               || E'language plpgsql as \n'
               || E'   $function$ \n'
               || E'begin \n'
               || E'     perform * \n'
               || E'        from @extschema@.tb_audit_field \n'
               || E'       limit 1 \n'
               || E'         for update; \n'
               || E'\n'
               || E'     if found then \n'
               || E'         perform @extschema@.fn_update_audit_fields(); \n'
               || E'     end if; \n'
               || E'exception \n'
               || E'     when insufficient_privilege \n'
               || E'     then return; \n'
               || E'end \n'
               || E'   $function$; \n';

        execute my_cmd;
    end if;
end;
 $$;


-- fn_update_audit_fields
CREATE OR REPLACE FUNCTION @extschema@.fn_update_audit_fields() returns void as
 $_$
begin
    perform *
       from @extschema@.tb_audit_field
      where audit_field = 0
        for update;

    if not found then
        insert into @extschema@.tb_audit_data_type
             values (0, '[unknown]');
        insert into @extschema@.tb_audit_field
             values (0, '[unknown]','[unknown]', 0, 0, false);
    end if;

    with tt_audit_fields as
    (
        select coalesce(
                   af.audit_field,
                   @extschema@.fn_get_or_create_audit_field(
                       a.attrelid::regclass::varchar,
                       a.attname::varchar
                   )
               ) as audit_field,
               (a.attrelid is null and af.active) as stale
          from pg_attribute a
          join pg_constraint cn
            on cn.conrelid = a.attrelid
           and cn.contype = 'p'
           and array_length(cn.conkey, 1) = 1
           and a.attnum > 0
           and a.attisdropped is false
          join pg_namespace n
            on cn.connamespace = n.oid
           and n.nspname::varchar = 'public'
     full join @extschema@.tb_audit_field af
            on a.attrelid::regclass::varchar = af.table_name
           and a.attname::varchar = af.column_name
    )
    update @extschema@.tb_audit_field af
       set active = false
      from tt_audit_fields afs
     where afs.stale
       and afs.audit_field = af.audit_field;
end
 $_$
    language 'plpgsql';

