albertcito / postgresql-audit Goto Github PK

audit_table_copy(destiny_name_db varchar , name_schema varchar , name_table varchar)

• return message: already exists or created. It assumes that bd an schema already exists in the destiny_db.
• Use get_columns(schema, table) and create table (created in issue #1)

Add this columns:

• id_audit
• event: insert, created, deleted
• event_at: datetime
• plus original columns tables, all nullable

Updated table log if the original table has a new column.

• Create trigger to don't allow modification in the rows for the audit tables

audit_db()

• Run audit_schema(name_schema) for each schema of the DB

Updated table log if the original table changed the column data type. For instance: varchar 255 to text.

audit_table_triggers(destiny_name_db varchar, name_schema varchar, name_table varchar)

This function creates or updated a trigger in the name_table to insert a new row in its log table each time that a new row is updated, inserted or deleted in this table.

audit_schema(name_schema)

• Run audit_table(name_schema varchar, name_table varchar) for each table in the schema

(schema, table) => return all columns and it's type of the table.

The db by default has lang table. You can do it with that table.

This is a code that I made long time ago about a function in Postgres. You will ned to use the information_schema to get the table columns information.

Inside of the folder db_init create a new file called get_table_colums_data.sql.

CREATE OR REPLACE FUNCTION get_table_colums_data (name_schema varchar, name_table varchar)
	RETURNS TABLE (
      column_name information_schema.columns.column_name%TYPE,
	  data_type information_schema.columns.data_type%TYPE,
	  character_maximum_length information_schema.columns.character_maximum_length%TYPE,
	  numeric_precision information_schema.columns.numeric_precision%TYPE,
	  datetime_precision information_schema.columns.datetime_precision%TYPE
	)
	AS $func$
BEGIN
	RETURN QUERY 
	SELECT 
	    columns.column_name,
        columns.data_type,
        columns.character_maximum_length,
        columns.numeric_precision,
        columns.datetime_precision
    FROM 
        information_schema.columns
    WHERE
        columns.table_schema = name_schema
        AND columns.table_name = name_table
    ORDER BY
        ordinal_position ASC;
END
$func$
LANGUAGE plpgsql;

Test it: SELECT * FROM audit_get_table_columns('public', 'lang');

audit_table(name_schema varchar, name_table varchar)

• destiny_name_db = create DB {current_db_name}_audit (if not exist)
• create schema (if not exist)
• audit_table_copy(destiny_name_db, name_schema, name_table) (#2)
• audit_table_column_added(destiny_name_db, name_schema, name_table) (#5 )
• audit_table_column_updated(destiny_name_db, name_schema, name_table) (#6)
• audit_table_triggers(destiny_name_db, name_schema, name_table) (#3)

• audit_table(conn_data varchar, name_schema varchar, name_table varchar)
  • create schema if does not exists.
  • audit_table_copy(destiny_name_db, name_schema, name_table) (#2)
  • audit_table_triggers(conn_data, destiny_name_db, name_schema, name_table) (#3)