pg-trigger-logs
pg-trigger-logs copied to clipboard
pg-trigger-logs is a PostgreSQL change extraction driver using triggers, listen and notify.
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.