Create Audit fields with ease in your postgres database

postgres_audit_fields.sql

  /*
  Legal:
  This GIST is provided without any sort of warranty and is to be used by the user at their own risk.
  CC-BY 2019
  Daniel Lee Van Den Bosch - SQL Developer Stone Plastics Zeeland, Michigan 49464 United States
  Description:
  This SQL is used to create standard audit fields in an existing table.
  to use it, run the SQL below and then run the following function
  SELECT it.audit_fields('my_schema.my_table');
  NOTE: if you already have a column created_at, created_by, updated_at, updated_by you may need to run this in pieces.
  IE SELECT it.audit_fields_at('my_schema.my_table');
  or SELECT it.audit_fields_by('my_schema.my_table');

to run it on everything do the following
(Make sure you do this in your test environment first as I don't even dare mess with my production db for this.)
SELECT it.audit_fields_at((table_schema || '.' || table_name)::TEXT)
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema <> 'pg_catalog'
AND table_schema <> 'information_schema';

*/
CREATE SCHEMA it;
create or replace function it.command(text) returns void
	language plpgsql
as $fun$
BEGIN
                    /*
              ***************************************************************************************************
              Create Date:          2019-03-25
              Author:               Dan Van Den Bosch
              ****************************************************************************************************
              */
--usage: select * from it.exec($$select now()$$) as t(dt timestamptz)
--https://stackoverflow.com/questions/12780275/dynamic-sql-query-in-postgres
    EXECUTE $1 ;
END
$fun$;


create or replace function it.exec(text) returns SETOF record
	language plpgsql
as $fun$
BEGIN
                    /*
              ***************************************************************************************************
              Create Date:          2019-03-25
              Author:               Dan Van Den Bosch
              ****************************************************************************************************
              */

--usage: select * from it.exec($$select now()$$) as t(dt timestamptz)
--https://stackoverflow.com/questions/12780275/dynamic-sql-query-in-postgres
    RETURN QUERY EXECUTE $1 ;
END
$fun$;

create or replace function update_modified_column() returns trigger
	language plpgsql
as $$
BEGIN
                  /*
              ***************************************************************************************************
              Create Date:          2017
              Author:               Dan Van Den Bosch
              Used By:              SQL consoles.
              Why:                  This is to be ran by SQL developers on tables to create the audit fields
              ****************************************************************************************************
              */
	  --do not change this function
	  --it is used everywhere!!!!!!!!
    NEW.updated_at = now();
    RETURN NEW;
END;
$$;


create or replace function update_modified_column_who() returns trigger
	language plpgsql
as $$
BEGIN

              /*
              ***************************************************************************************************
              Create Date:          2019-03-25
              Author:               Dan Van Den Bosch
              ****************************************************************************************************
              */
	  --do not change this function
	  --it is used everywhere!!!!!!!!
    NEW.updated_by = CURRENT_USER::TEXT;
    RETURN NEW;
END;
$$;


create or replace function it.audit_fields(_schema_dot_table text) returns void
	language sql
as $$

                  /*
              ***************************************************************************************************
              Create Date:          2019-03-25
              Author:               Dan Van Den Bosch
              Used By:              SQL consoles.
              Why:                  This is to be ran by SQL developers on tables to create the audit fields
              ****************************************************************************************************
              */
SELECT it.command('
alter table ' || _schema_dot_table || '
	add created_at TIMESTAMP default current_timestamp not null;

alter table ' || _schema_dot_table || '
	add updated_at TIMESTAMP default CURRENT_TIMESTAMP not null;

alter table ' || _schema_dot_table || '
	add created_by TEXT default CURRENT_USER not null;

alter table ' || _schema_dot_table || '
	add updated_by TEXT default CURRENT_USER not null;

create trigger updated_at
  before update
  on ' || _schema_dot_table || '
  for each row
execute procedure public.update_modified_column();

  create trigger updated_at_who
  before update
  on ' || _schema_dot_table || '
  for each row
execute procedure public.update_modified_column_who();



  ')
;
$$;



create or replace function it.audit_fields_at(_schema_dot_table text) returns void
	language sql
as $$

                    /*
              ***************************************************************************************************
              Create Date:          2019-03-25
              Author:               Dan Van Den Bosch
              Used By:              SQL consoles.
              Why:                  This is to be ran by SQL developers on tables to create the audit fields
              ****************************************************************************************************
              */
SELECT it.command('
alter table ' || _schema_dot_table || '
	add created_at TIMESTAMP default current_timestamp not null;

alter table ' || _schema_dot_table || '
	add updated_at TIMESTAMP default CURRENT_TIMESTAMP not null;


create trigger updated_at
  before update
  on ' || _schema_dot_table || '
  for each row
execute procedure public.update_modified_column();
  ')
;
$$;


create or replace function it.audit_fields_by(_schema_dot_table text) returns void
	language sql
as $$

                    /*
              ***************************************************************************************************
              Create Date:          2019-03-25
              Author:               Dan Van Den Bosch
              ****************************************************************************************************
              */
SELECT it.command('


alter table ' || _schema_dot_table || '
	add created_by TEXT default CURRENT_USER not null;

alter table ' || _schema_dot_table || '
	add updated_by TEXT default CURRENT_USER not null;



  create trigger updated_at_who
  before update
  on ' || _schema_dot_table || '
  for each row
execute procedure public.update_modified_column_who();
  ')
;
$$;

TODO: Update with the adt_flds function I have created