afterlog.py

text/x-python-script

Download

CREATE OR REPLACE FUNCTION log_manipulation()
RETURNS TRIGGER LANGUAGE plpython3u AS
$python$
from Postgres import quote_nullable, quote_ident, notify

# parameter type extracted from the statement
record_manipulation = prepare("INSERT INTO replica_log (sql) VALUES ($1)")

def log(sql):
	record_manipulation(sql)
	# notify listeners that new data is available
	notify('replicas')

fmt_insert = "INSERT INTO {relname} ({columns}) VALUES ({values});".format
fmt_update = "UPDATE {relname} SET {changes} WHERE {keys};".format
fmt_delete = "DELETE FROM {relname} WHERE {keys};".format

def fmt_eqs(joinwith, keys, record, fmt = "{0} = {1}".format):
	pairs = [fmt(quote_ident(k), quote_nullable(record[k])) for k in keys]
	return joinwith.join(pairs)

##
# entry points

def after_insert(td, new):
	relname = td.args[0]
	sql = fmt_insert(
		relname = relname,
		columns = ', '.join(map(quote_ident, new.keys())),
		values = ', '.join(map(quote_nullable, new.values())),
	)
	log(sql)

def after_update(td, old, new):
	relname, *pkeys = td.args
	modified_columns = {k : v for k,v in new.items() if old[k] != v}

	# only log if there were modified columns
	if modified_columns:
		sql = fmt_update(
			relname = relname,
			changes = fmt_eqs(', ', modified_columns.keys(), modified_columns),
			keys = fmt_eqs(' AND ', pkeys, old)
		)
		log(sql)

def after_delete(td, old):
	relname, *pkeys = td.args
	sql = fmt_delete(
		relname = relname, 
		keys = fmt_eqs(' AND ', pkeys, old)
	)
	log(sql)
$python$;