CREATE TYPE tipooperacion AS ENUM (
    'INSERT',
    'UPDATE',
    'DELETE'
);

ALTER TYPE tipooperacion OWNER TO sicatpi;

CREATE TRIGGER trigger_tabla AFTER INSERT OR DELETE OR UPDATE ON tabla FOR EACH ROW EXECUTE PROCEDURE bitacora();

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;

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;

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;

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;

CREATE TABLE sesion (
    idsesion integer NOT NULL,
    idusuario integer NOT NULL,
    fecha timestamp without time zone NOT NULL
);

ALTER TABLE sesion OWNER TO sicatpi;