blistovmhz

Untitled

Nov 10th, 2020
983
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE SCHEMA logging;
  2. CREATE TABLE logging.t_history (
  3.   id serial,
  4.   tstamp timestamp DEFAULT now(),
  5.   schemaname text,
  6.   tabname text,
  7.   operation text,
  8.   who text DEFAULT current_user,
  9.   new_val json,
  10.   old_val json
  11. );
  12.  
  13. CREATE FUNCTION change_trigger() RETURNS trigger AS $$
  14. BEGIN
  15.   IF TG_OP = 'INSERT'
  16.   THEN
  17.     INSERT INTO logging.t_history (tabname, schemaname, operation, new_val)
  18.       VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW));
  19.     RETURN NEW;
  20.   ELSIF TG_OP = 'UPDATE'
  21.   THEN
  22.     INSERT INTO logging.t_history (tabname, schemaname, operation, new_val, old_val)
  23.       VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP,
  24.       row_to_json(NEW), row_to_json(OLD));
  25.       RETURN NEW;
  26.   ELSIF TG_OP = 'DELETE'
  27.   THEN
  28.     INSERT INTO logging.t_history (tabname, schemaname, operation, old_val)
  29.       VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD));
  30.       RETURN OLD;
  31.   END IF;
  32. END;
  33. $$ LANGUAGE 'plpgsql' SECURITY DEFINER;
  34.  
  35. CREATE TABLE t_trig (id int, name text);
  36. CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON t_trig
  37.   FOR EACH ROW EXECUTE PROCEDURE change_trigger();
  38.  
RAW Paste Data

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×