Skip to content

Instantly share code, notes, and snippets.

@ad
Last active February 23, 2018 22:14
Show Gist options
  • Save ad/8d711ea1b3768a4250b21ed277437dc1 to your computer and use it in GitHub Desktop.
Save ad/8d711ea1b3768a4250b21ed277437dc1 to your computer and use it in GitHub Desktop.
database with triggers and history example
CREATE OR REPLACE FUNCTION "json_append"(IN "data" json, IN insert_data json) RETURNS "json" AS $BODY$
SELECT ('{'||string_agg(to_json(key)||':'||value, ',')||'}')::json
FROM (
SELECT * FROM json_each(data)
UNION ALL
SELECT * FROM json_each(insert_data)
) t;
$BODY$
LANGUAGE sql
COST 100
CALLED ON NULL INPUT
SECURITY INVOKER
IMMUTABLE;
ALTER FUNCTION "json_append"(IN "data" json, IN insert_data json) OWNER TO "postgres";
CREATE OR REPLACE FUNCTION "change_trigger"() RETURNS "trigger" AS $BODY$
DECLARE
_json json := '{}';
_name text;
_data_type text;
oldValue TEXT;
newValue TEXT;
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO history (
tabname,
schemaname,
OPERATION,
changes,
item_id
) VALUES (
TG_RELNAME,
TG_TABLE_SCHEMA,
'INSERT',
row_to_json (NEW),
NEW . ID
);
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
FOR _name, _data_type IN SELECT column_name, data_type FROM information_schema.Columns WHERE table_schema = TG_TABLE_SCHEMA AND table_name = TG_TABLE_NAME LOOP
EXECUTE 'SELECT ($1).' || _name || '::text' INTO newValue USING NEW;
EXECUTE 'SELECT ($1).' || _name || '::text' INTO oldValue USING OLD;
IF oldValue IS NULL THEN
oldValue = '';
END IF;
IF newValue IS NULL THEN
newValue = '';
END IF;
IF newValue != oldValue THEN
IF _data_type = 'json' THEN
_json = json_append(_json, ('{' || '"' || _name || '": ' || newValue::json || '}')::json);
ELSE
IF newValue != '' AND substr(newValue, 0, 2) = '{'::text THEN
newValue = newValue::json;
ELSE
newValue = '"'|| newValue || '"';
END IF;
_json = json_append(_json, ('{' || '"' || _name || '": ' || newValue || '}')::json);
END IF;
END IF;
END LOOP;
if _json::text = '{}'::text then
RETURN NEW;
end if;
INSERT INTO history (
tabname,
schemaname,
OPERATION,
changes,
item_id
) VALUES (
TG_RELNAME,
TG_TABLE_SCHEMA,
'UPDATE',
_json,
NEW . ID
);
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
INSERT INTO history (
tabname,
schemaname,
OPERATION,
changes,
item_id
) VALUES (
TG_RELNAME,
TG_TABLE_SCHEMA,
'DELETE',
row_to_json (OLD),
OLD . ID
);
RETURN OLD;
END IF;
END;
$BODY$
LANGUAGE plpgsql
COST 100
CALLED ON NULL INPUT
SECURITY DEFINER
VOLATILE;
ALTER FUNCTION "change_trigger"() OWNER TO "postgres";
CREATE OR REPLACE FUNCTION "history_notify_trigger"() RETURNS "trigger" AS $BODY$
DECLARE
_json json := '{}';
BEGIN
IF TG_OP = 'DELETE' THEN
_json = json_append(row_to_json(OLD), ('{' || '"_table": "' || TG_RELNAME || '", ' || '"_operation": "' || TG_OP || '"}')::json);
ELSE
_json = json_append(row_to_json(NEW), ('{' || '"_table": "' || TG_RELNAME || '", ' || '"_operation": "' || TG_OP || '"}')::json);
END IF;
PERFORM pg_notify('history_watchers', _json::text );
RETURN new;
END;
$BODY$
LANGUAGE plpgsql
COST 100
CALLED ON NULL INPUT
SECURITY INVOKER
VOLATILE;
ALTER FUNCTION "history_notify_trigger"() OWNER TO "postgres";
CREATE OR REPLACE FUNCTION "update_column"() RETURNS "trigger" AS $BODY$
BEGIN
NEW.ts_updated = now();
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql
COST 100
CALLED ON NULL INPUT
SECURITY INVOKER
VOLATILE;
ALTER FUNCTION "update_column"() OWNER TO "postgres";
CREATE TYPE "history_operation" AS ENUM (
'INSERT',
'UPDATE',
'DELETE'
);
ALTER TYPE "history_operation" OWNER TO "postgres";
CREATE TABLE IF NOT EXISTS "history" (
"id" bigserial NOT NULL,
"tstamp" timestamp(6) NULL DEFAULT now(),
"schemaname" text COLLATE "default",
"tabname" text COLLATE "default",
"operation" "history_operation",
"who" text DEFAULT "current_user"() COLLATE "default",
"changes" json,
"item_id" int8
) WITH (OIDS=FALSE);
ALTER TABLE "history" OWNER TO "postgres";
ALTER TABLE "history" DROP CONSTRAINT history_pkey; ALTER TABLE "history" ADD PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE;
CREATE INDEX IF NOT EXISTS "history_id_idx" ON "history" USING btree("id" "pg_catalog"."int8_ops" ASC NULLS LAST);
CREATE INDEX IF NOT EXISTS "history_tabname_item_id_idx" ON "history" USING btree(tabname COLLATE "default" "pg_catalog"."text_ops" ASC NULLS LAST, item_id "pg_catalog"."int8_ops" ASC NULLS LAST);
DROP TABLE IF EXISTS "errors";
CREATE TABLE IF NOT EXISTS "errors" (
"id" bigserial NOT NULL,
"source" text COLLATE "default",
"ts_added" timestamp(6) NULL DEFAULT now(),
"text" text COLLATE "default"
) WITH (OIDS=FALSE);
ALTER TABLE "errors" OWNER TO "postgres";
ALTER TABLE "errors" ADD PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE;
CREATE INDEX IF NOT EXISTS "errors_id_idx" ON "errors" USING btree("id" "pg_catalog"."int8_ops" ASC NULLS LAST);
CREATE TABLE IF NOT EXISTS "table_name" (
"id" bigserial NOT NULL,
"ts_added" timestamp(6) NOT NULL DEFAULT now(),
"ts_updated" timestamp(6) NOT NULL DEFAULT now()
) WITH (OIDS=FALSE);
ALTER TABLE "table_name" OWNER TO "postgres";
ALTER TABLE "table_name" DROP CONSTRAINT table_name_pkey; ALTER TABLE "table_name" ADD PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE;
CREATE INDEX IF NOT EXISTS "table_name_id_idx" ON "table_name" USING btree("id" "pg_catalog"."int8_ops" ASC NULLS LAST);
DROP TRIGGER IF EXISTS "table_name_ts_updated" ON "table_name"; CREATE TRIGGER "table_name_ts_updated" BEFORE UPDATE ON "table_name" FOR EACH ROW EXECUTE PROCEDURE "update_column"();
DROP TRIGGER IF EXISTS "table_name_change_trigger" ON "table_name"; CREATE TRIGGER "table_name_change_trigger" BEFORE DELETE OR INSERT OR UPDATE ON "table_name" FOR EACH ROW EXECUTE PROCEDURE "change_trigger"();
DROP TRIGGER IF EXISTS "table_name_history_notify_trigger" ON "table_name"; CREATE TRIGGER "table_name_history_notify_trigger" AFTER DELETE OR INSERT OR UPDATE ON "table_name" FOR EACH ROW EXECUTE PROCEDURE "history_notify_trigger"();
-- ALTER TABLE "table_name" ADD CONSTRAINT "fk_table_name_users" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON UPDATE NO ACTION ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment