Database restoration triggers

create
or replace function private.f_check_timestamp_proximity () returns trigger language plpgsql
set
  search_path = '' as $$
DECLARE
  v_field_name TEXT;
  v_new_jsonb JSONB;
  v_old_jsonb JSONB;
  v_timestamp_value TIMESTAMPTZ;
  v_now TIMESTAMPTZ := now(); 
  v_allowed_interval INTERVAL := '5 minutes';
  v_lower_bound TIMESTAMPTZ := v_now - v_allowed_interval;
  v_upper_bound TIMESTAMPTZ := v_now + v_allowed_interval;
BEGIN
  IF TG_NARGS = 0 THEN
    RAISE WARNING 'f_check_timestamp_proximity trigger called without arguments on table %.%', TG_TABLE_SCHEMA, TG_TABLE_NAME;
    RETURN NEW;
  END IF;

  v_new_jsonb := to_jsonb(NEW);
  
  IF TG_OP = 'UPDATE' THEN
    v_old_jsonb := to_jsonb(OLD);
  END IF;

  -- Loop through each field name passed as an argument from CREATE TRIGGER.
  -- TG_ARGV is a text array containing the arguments.
  FOREACH v_field_name IN ARRAY TG_ARGV
  LOOP
    IF TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND v_new_jsonb -> v_field_name IS DISTINCT FROM v_old_jsonb -> v_field_name) THEN
      
      v_timestamp_value := (v_new_jsonb ->> v_field_name)::TIMESTAMPTZ;
      
      IF v_timestamp_value IS NOT NULL AND (v_timestamp_value NOT BETWEEN v_lower_bound AND v_upper_bound) THEN
        RAISE EXCEPTION 'Constraint Violation on %.%: The value for "%" (%) is outside the allowed +/- % window from the current time.',
          TG_TABLE_SCHEMA, TG_TABLE_NAME, v_field_name, v_timestamp_value, v_allowed_interval
        USING
          ERRCODE = 'check_violation',
          HINT = 'The timestamp must be within 5 minutes of the current server time.';
      END IF;
    END IF;
  END LOOP;

  RETURN NEW;
END;
$$;

create
or replace function private.f_check_timestamp_proximity () returns trigger language plpgsql
set
  search_path = '' as $$
DECLARE
  v_field_name TEXT;
  v_new_jsonb JSONB;
  v_old_jsonb JSONB;
  v_timestamp_value TIMESTAMPTZ;
  v_now TIMESTAMPTZ := now(); 
  v_allowed_interval INTERVAL := '5 minutes';
  v_lower_bound TIMESTAMPTZ := v_now - v_allowed_interval;
  v_upper_bound TIMESTAMPTZ := v_now + v_allowed_interval;
BEGIN
  IF TG_NARGS = 0 THEN
    RAISE WARNING 'f_check_timestamp_proximity trigger called without arguments on table %.%', TG_TABLE_SCHEMA, TG_TABLE_NAME;
    RETURN NEW;
  END IF;

  v_new_jsonb := to_jsonb(NEW);
  
  IF TG_OP = 'UPDATE' THEN
    v_old_jsonb := to_jsonb(OLD);
  END IF;

  -- Loop through each field name passed as an argument from CREATE TRIGGER.
  -- TG_ARGV is a text array containing the arguments.
  FOREACH v_field_name IN ARRAY TG_ARGV
  LOOP
    IF TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND v_new_jsonb -> v_field_name IS DISTINCT FROM v_old_jsonb -> v_field_name) THEN
      
      v_timestamp_value := (v_new_jsonb ->> v_field_name)::TIMESTAMPTZ;
      
      IF v_timestamp_value IS NOT NULL AND (v_timestamp_value NOT BETWEEN v_lower_bound AND v_upper_bound) THEN
        RAISE EXCEPTION 'Constraint Violation on %.%: The value for "%" (%) is outside the allowed +/- % window from the current time.',
          TG_TABLE_SCHEMA, TG_TABLE_NAME, v_field_name, v_timestamp_value, v_allowed_interval
        USING
          ERRCODE = 'check_violation',
          HINT = 'The timestamp must be within 5 minutes of the current server time.';
      END IF;
    END IF;
  END LOOP;

  RETURN NEW;
END;
$$;