Snippets

Elias Tutungi Bitacora Postgresql

Updated by Elias Tutungi
View revision

File ENUM tipoOperacion Added

  • Ignore whitespace
  • Hide word diff 
+CREATE TYPE tipooperacion AS ENUM (
+    'INSERT',
+    'UPDATE',
+    'DELETE'
+);
+
+ALTER TYPE tipooperacion OWNER TO sicatpi;

File Tabla Sesion Added

  • Ignore whitespace
  • Hide word diff 
+CREATE TABLE sesion (
+    idsesion integer NOT NULL,
+    idusuario integer NOT NULL,
+    fecha timestamp without time zone NOT NULL
+);
+
+ALTER TABLE sesion OWNER TO sicatpi;
Created by Elias Tutungi
View revision

File Ejemplo Trigger Added

  • Ignore whitespace
  • Hide word diff 
+CREATE TRIGGER trigger_tabla AFTER INSERT OR DELETE OR UPDATE ON tabla FOR EACH ROW EXECUTE PROCEDURE bitacora();

File Funcion Bitacora Added

  • Ignore whitespace
  • Hide word diff 
+CREATE OR REPLACE FUNCTION bitacora()
+  RETURNS trigger AS
+$BODY$
+DECLARE 
+    Usuario INT;
+    Tabla VARCHAR(100):= TG_TABLE_NAME;
+    Operacion VARCHAR(50) := TG_OP;
+    Campo VARCHAR(100);
+    Old_value VARCHAR(100);
+    New_value VARCHAR(100);
+    Fecha TIMESTAMP WITHOUT TIME ZONE := NOW();
+
+    Field INT := 0;
+    MaxField INT := MAX(ORDINAL_POSITION)
+        FROM INFORMATION_SCHEMA.COLUMNS
+        WHERE TABLE_NAME = Tabla;
+BEGIN
+    Usuario := get_sesion( pg_backend_pid() );
+    WHILE Field < MaxField LOOP
+        SELECT MIN(ORDINAL_POSITION) INTO Field
+            FROM INFORMATION_SCHEMA.COLUMNS
+            WHERE TABLE_NAME = Tabla AND ORDINAL_POSITION > Field;
+
+        SELECT COLUMN_NAME INTO Campo
+            FROM INFORMATION_SCHEMA.COLUMNS
+            WHERE TABLE_NAME = Tabla AND ORDINAL_POSITION = Field;
+
+        CASE Operacion
+            WHEN 'INSERT' THEN
+                Old_value := NULL;
+                EXECUTE 'SELECT ($1).' || quote_ident(Campo) || '::text' INTO New_value USING NEW;
+            WHEN 'UPDATE' THEN   
+                EXECUTE 'SELECT ($1).' || quote_ident(Campo) || '::text' INTO Old_value USING OLD;
+                EXECUTE 'SELECT ($1).' || quote_ident(Campo) || '::text' INTO New_value USING NEW;
+            WHEN 'DELETE' THEN   
+                EXECUTE 'SELECT ($1).' || quote_ident(Campo) || '::text' INTO Old_value USING OLD;
+                New_value := NULL;
+            ELSE
+                RAISE EXCEPTION 'OPERACION NO DEFINIDA';
+        END CASE;
+           
+        IF Old_value IS DISTINCT FROM New_value THEN
+            INSERT INTO
+                bitacora (
+                    idusuario,
+                    tabla,
+                    campo,
+                    operacion,
+                    old_value,
+                    new_value,
+                    fecha
+                ) VALUES (
+                    Usuario,
+                    Tabla,
+                    Campo,
+                    CAST(Operacion AS tipooperacion),
+                    Old_value,
+                    New_value,
+                    Fecha
+                );
+        END IF;
+    END LOOP;
+    RETURN NEW;
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE
+  COST 100;
+ALTER FUNCTION bitacora()
+  OWNER TO sicatpi;

File Funcion Get Sesion Added

  • Ignore whitespace
  • Hide word diff 
+CREATE FUNCTION get_sesion(id integer) RETURNS integer
+    LANGUAGE plpgsql STABLE
+    AS $$ 
+BEGIN 
+  PERFORM relname
+  FROM pg_class
+  WHERE relname = 'sesion'
+    AND CASE WHEN has_schema_privilege(relnamespace, 'USAGE')
+             THEN pg_table_is_visible(oid) ELSE false END;
+  
+  IF not found THEN 
+    RETURN NULL;
+  ELSE
+    RETURN (SELECT idusuario FROM sesion WHERE idsesion = id);
+  END IF;
+END;
+$$;
+
+
+ALTER FUNCTION get_sesion(id integer) OWNER TO sicatpi;

File Funcion Set Sesion Added

  • Ignore whitespace
  • Hide word diff 
+CREATE FUNCTION set_sesion(id integer, usuario integer) RETURNS void
+    LANGUAGE plpgsql
+    AS $$ 
+BEGIN
+  PERFORM relname
+  FROM pg_class
+  WHERE relname = 'sesion'
+    AND CASE WHEN has_schema_privilege(relnamespace, 'USAGE')
+             THEN pg_table_is_visible(oid) ELSE false END;
+
+  IF not found THEN
+	CREATE TABLE sesion (
+	    idsesion integer NOT NULL,
+	    idusuario integer NOT NULL,
+	    fecha timestamp without time zone NOT NULL
+	);
+
+	ALTER TABLE sesion OWNER TO sicatpi;
+  END IF;
+
+  INSERT INTO sesion VALUES (id, usuario, now());
+END;
+$$;
+
+ALTER FUNCTION public.set_sesion(id integer, usuario integer) OWNER TO sicatpi;

File Tabla Bitacora Added

  • Ignore whitespace
  • Hide word diff 
+CREATE TABLE bitacora (
+   idusuario integer,
+   tabla character varying(50) NOT NULL, 
+   operacion tipooperacion NOT NULL,
+   campo character varying(100) NOT NULL,
+   old_value character varying(100), 
+   new_value character varying(100), 
+   fecha timestamp without time zone NOT NULL
+);
+
+ALTER TABLE bitacora OWNER TO sicatpi;
HTTPS SSH

You can clone a snippet to your computer for local editing. Learn more.