pgtl

README

pg-trigger-logs

pg-trigger-logs is a PostgreSQL change extraction driver using triggers, listen and notify.

Setup

pg-trigger-logs uses triggers and listeners for generating logs.
Firstly, create a triggering function that notifies the log in the event channel.
Now, create trigger for each table in the database, you want to get logs for.
Finally, start listening the event and use your logs.

CREATE OR REPLACE FUNCTION notifier() RETURNS TRIGGER AS
$body$
	DECLARE
		message json;
		old_row json;
		new_row json;
		data json;
	BEGIN
		IF (TG_OP = 'UPDATE') THEN
			old_row = row_to_json(OLD);
			new_row = row_to_json(NEW);
			data = json_build_object('old row', old_row,'new row', new_row);
		ELSIF (TG_OP = 'DELETE') THEN
			old_row = row_to_json(OLD);
			data = json_build_object('old row', old_row);
		ELSIF (TG_OP = 'INSERT') THEN
			new_row = row_to_json(NEW);
			data = json_build_object('new row', new_row);
		END IF;
		message = json_build_object(
						'table_name', TG_TABLE_NAME,
						'schema_name', TG_TABLE_SCHEMA,
						'operation', TG_OP,
						'data', data
					);
		PERFORM pg_notify('event',message::text);
		RETURN NULL;
	END;
$body$
LANGUAGE plpgsql;

CREATE TRIGGER notifier_trigger
AFTER INSERT OR UPDATE OR DELETE ON stest.test
FOR EACH ROW EXECUTE PROCEDURE notifier();

LISTEN event;

Contributing

Changes and improvements are more than welcome! Feel free to fork and open a pull request. And Please make your changes in a specific branch and request to pull into master! If you can, please make sure the game fully works before sending the PR, as that will help speed up the process.

License

GO-REST-API is licensed under the MIT license.

Documentation

Index

• Constants
• func CreateTriggerFunction(ctx context.Context, db *sql.DB) error
• func GetChangesLogs(ls *pq.Listener) <-chan *Response
• func Listen(ls *pq.Listener) error
• func SetupEverything(ctx context.Context, dbConfig string) (*pq.Listener, error)
• func TriggerAllTables(ctx context.Context, db *sql.DB) error
• func TriggerSomeTables(ctx context.Context, db *sql.DB, tables []*Table) error
• func Unlisten(ls *pq.Listener) error
• type Response
• type Table
  • func TablesInDatabase(ctx context.Context, db *sql.DB) ([]*Table, error)

Constants

const (
	ListenEvent     = `event`
	TriggerFunction = `notifier`
	Trigger         = `notifier_trigger`
)

Trigger Details

Functions

func CreateTriggerFunction

func CreateTriggerFunction(ctx context.Context, db *sql.DB) error

CreateTriggerFunction creates triggered function

func GetChangesLogs

func GetChangesLogs(ls *pq.Listener) <-chan *Response

GetChangesLogs gets the changes log

func Listen

func Listen(ls *pq.Listener) error

Listen ...

func SetupEverything

func SetupEverything(ctx context.Context, dbConfig string) (*pq.Listener, error)

SetupEverything sets everything up for the whole database to notify logs

func TriggerAllTables

func TriggerAllTables(ctx context.Context, db *sql.DB) error

TriggerAllTables adds triggers to all the tables in the connected database

func TriggerSomeTables

func TriggerSomeTables(ctx context.Context, db *sql.DB, tables []*Table) error

TriggerSomeTables adds triggers to the provided tables

func Unlisten

func Unlisten(ls *pq.Listener) error

Unlisten ...

Types

type Response

type Response struct {
	JSON []byte
	Map  map[string]interface{}
	Err  error
}

Response collects whole response from the notification log triggered in two formats: JSON and Map of string-interface

type Table

type Table struct {
	Name   string
	Schema string
}

Table struct for table details

func TablesInDatabase

func TablesInDatabase(ctx context.Context, db *sql.DB) ([]*Table, error)

TablesInDatabase returns list of tables in connected database