Last active
December 19, 2016 15:22
-
-
Save ernesmb/8df19afadc296f1e73cf40dfdf132b33 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- CREATE A STORED PROCEDURE AND TRIGGER TO KEEP A LOG OF CHANGES ON ANY CARTO TABLE | |
| ---- based on @andrewxhill's: https://gist.github.com/andrewxhill/0645add72c093f2f4908 | |
| -- | |
| --- Create the log table and cartodbfy it | |
| CREATE TABLE version_control(); | |
| SELECT CDB_CartodbfyTable('username','version_control'); -- username parameter is only needed in organizational accounts | |
| -- | |
| --- Add extra columns | |
| ALTER TABLE version_control ADD COLUMN data json; -- to store the complete row in JSON format | |
| ALTER TABLE version_control ADD COLUMN source_id integer; -- cartodb_id of the row | |
| ALTER TABLE version_control ADD COLUMN table_name text; -- table name | |
| ALTER TABLE version_control ADD COLUMN tg_op text; -- writing operation that was performed | |
| ALTER TABLE version_control ADD COLUMN logged_at timestamp; -- timestamp for the operation | |
| -- | |
| --- Create the function itself | |
| CREATE OR REPLACE FUNCTION carto_version_control() RETURNS TRIGGER AS $$ | |
| BEGIN | |
| IF (TG_OP = 'DELETE') THEN | |
| INSERT INTO version_control(the_geom, tg_op, data, source_id, table_name, logged_at) | |
| SELECT OLD.the_geom, 'DELETE', row_to_json(OLD), OLD.cartodb_id, TG_TABLE_NAME::text, now(); | |
| RETURN OLD; | |
| ELSIF (TG_OP = 'UPDATE') THEN | |
| INSERT INTO version_control(the_geom, tg_op, data, source_id, table_name, logged_at) | |
| SELECT NEW.the_geom, 'UPDATE', row_to_json(NEW), NEW.cartodb_id, TG_TABLE_NAME::text, now(); | |
| RETURN NEW; | |
| ELSIF (TG_OP = 'INSERT') THEN | |
| INSERT INTO version_control(the_geom, tg_op, data, source_id, table_name, logged_at) | |
| SELECT NEW.the_geom, 'INSERT', row_to_json(NEW), NEW.cartodb_id, TG_TABLE_NAME::text, now(); | |
| RETURN NEW; | |
| END IF; | |
| RETURN NULL; | |
| END; | |
| $$ LANGUAGE plpgsql; | |
| -- | |
| --- Add the trigger to any table you want to log | |
| CREATE TRIGGER carto_version_trigger | |
| AFTER UPDATE OR DELETE OR INSERT ON dummy_dataset -- Remember to use the proper table name | |
| FOR EACH ROW EXECUTE PROCEDURE version_control(); | |
| -- | |
| --- Explode json data in a row, filtering by any column in `version_control` | |
| SELECT (json_populate_record(null::dummy_dataset, data)).* FROM version_control WHERE table_name LIKE 'dummy_dataset'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment