Contents

CREATE OR REPLACE FUNCTION jsonb_minus(arg1 jsonb, arg2 jsonb) RETURNS jsonb
AS $$

SELECT 
  COALESCE(json_object_agg(key, value), '{}')::jsonb
FROM 
  jsonb_each(arg1)
WHERE 
  arg1 -> key <> arg2 -> key 
  OR arg2 -> key IS NULL;

$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION jsonb_minus_logger() RETURNS TRIGGER AS $body$
  DECLARE
    changes_h jsonb;
    size integer;
    buffer jsonb;
  BEGIN
    size := jsonb_array_length(NEW.log);
    
    changes_h := jsonb_minus(row_to_json(OLD)::jsonb, row_to_json(NEW)::jsonb);

    NEW.log := jsonb_set(
      NEW.log,
      ARRAY[size::text],
      jsonb_build_object(
        'ts',
        extract(epoch from now())::int,
        'i',
        (NEW.log#>>ARRAY[(size - 1)::text, 'i'])::int + 1,
        'd',
        changes_h
      ),
      true
    );
    return NEW;
  END;
  $body$
  LANGUAGE plpgsql;


ALTER TABLE pgbench_accounts ADD COLUMN log jsonb DEFAULT '[]' NOT NULL;

UPDATE pgbench_accounts SET log = to_jsonb(ARRAY[json_build_object('i', 0)])::jsonb;

CREATE TRIGGER hstore_log_accounts
BEFORE UPDATE ON pgbench_accounts FOR EACH ROW
EXECUTE PROCEDURE jsonb_minus_logger();

Version data entries

25 entries across 25 versions & 1 rubygems

Version	Path
logidze-0.12.0	bench/triggers/jsonb_minus_setup.sql
logidze-0.11.0	bench/triggers/jsonb_minus_setup.sql
logidze-0.10.0	bench/triggers/jsonb_minus_setup.sql
logidze-0.9.0	bench/triggers/jsonb_minus_setup.sql
logidze-0.8.1	bench/triggers/jsonb_minus_setup.sql
logidze-0.8.0	bench/triggers/jsonb_minus_setup.sql
logidze-0.7.0	bench/triggers/jsonb_minus_setup.sql
logidze-0.6.5	bench/triggers/jsonb_minus_setup.sql
logidze-0.6.4	bench/triggers/jsonb_minus_setup.sql
logidze-0.6.3	bench/triggers/jsonb_minus_setup.sql
logidze-0.6.2	bench/triggers/jsonb_minus_setup.sql
logidze-0.6.1	bench/triggers/jsonb_minus_setup.sql
logidze-0.6.0	bench/triggers/jsonb_minus_setup.sql
logidze-0.5.3	bench/triggers/jsonb_minus_setup.sql
logidze-0.5.2	bench/triggers/jsonb_minus_setup.sql
logidze-0.5.1	bench/triggers/jsonb_minus_setup.sql
logidze-0.5.0	bench/triggers/jsonb_minus_setup.sql
logidze-0.4.1	bench/triggers/jsonb_minus_setup.sql
logidze-0.4.0	bench/triggers/jsonb_minus_setup.sql
logidze-0.3.1	bench/triggers/jsonb_minus_setup.sql