database with triggers and history example

example.sql

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;