CREATE TABLE ctl_schema (
id BIGSERIAL NOT NULL,
tabla_schema text NOT NULL,
index_tabla_schema bigint NOT NULL,
vars_schema text NOT NULL,
tipo_cambio_schema integer NOT NULL,
PRIMARY KEY (id));

CREATE TABLE ctl_log (
id BIGSERIAL NOT NULL,
nombre_log varchar(255) NOT NULL,
descripcion_log text,
PRIMARY KEY (id));

CREATE TABLE syslog (
id SERIAL NOT NULL,
registro_log timestamp without time zone DEFAULT (now())::timestamp(0) without time zone NOT NULL,
descripcion_log text,
ctl_log_id bigint NOT NULL,
PRIMARY KEY (id));

ALTER TABLE syslog ADD CONSTRAINT FK_syslog_ctl_log FOREIGN KEY (ctl_log_id) REFERENCES ctl_log (id);

–#—————————————-

DO $$
DECLARE
row record;
cmd text;
BEGIN
FOR row IN SELECT schemaname, tablename FROM pg_tables WHERE schemaname = ‘public’ LOOP

–row.schemaname
cmd := format(‘
ALTER TABLE ‘||row.tablename||’ ADD COLUMN  registro_schema timestamp without time zone DEFAULT (now())::timestamp(0) without time zone NOT NULL;
ALTER TABLE ‘||row.tablename||’ ADD COLUMN  detalle_schema text DEFAULT ”Agregado antes de gestor de maestros” NOT NULL;
ALTER TABLE ‘||row.tablename||’ ADD COLUMN  user_id_schema bigint DEFAULT 1 NOT NULL;
ALTER TABLE ‘||row.tablename||’ ADD COLUMN  ip_user_schema inet DEFAULT ”0.0.0.0”::inet NOT NULL;
ALTER TABLE ‘||row.tablename||’ ADD COLUMN  estado_schema int DEFAULT 0 NOT NULL;
ALTER TABLE ‘||row.tablename||’ ADD COLUMN  enable_schema int DEFAULT 0 NOT NULL;’);
RAISE NOTICE ‘%’, cmd;
EXECUTE cmd;
END LOOP;
END
$$ LANGUAGE plpgsql;

–#—————————————-

CREATE OR REPLACE FUNCTION genera_schema() RETURNS trigger AS $BODY$
DECLARE
v_time timestamp;
BEGIN
v_time := current_timestamp;
EXECUTE ‘CREATE SCHEMA “‘|| to_char(v_time, ‘yyyy-MM-dd.HH24.MI.SS.MS’)::text ||'”‘;
IF ‘ctl_schema’ LIKE TG_TABLE_NAME THEN
RAISE INFO ‘Tabla no afectada % ‘, TG_TABLE_NAME;
return new;
ELSE
IF (TG_OP = ‘INSERT’) THEN
—    INSERT INTO ctl_schema(registro_schema, user_id, vars_schema, tipo_cambio_schema, ip_schema) VALUES (v_time, 1, TG_TABLE_NAME||’/’||NEW.id::text, 1, ‘::1’::inet);
RETURN NEW;
ELSIF (TG_OP = ‘UPDATE’) THEN
—    INSERT INTO ctl_schema(registro_schema, user_id, vars_schema, tipo_cambio_schema, ip_schema) VALUES (v_time, 1, TG_TABLE_NAME||’/’||OLD.id::text, 2, ‘::1’::inet);
RETURN NEW;
ELSE
—    INSERT INTO ctl_schema(registro_schema, user_id, vars_schema, tipo_cambio_schema, ip_schema) VALUES (v_time, 1, TG_TABLE_NAME||’/’||OLD.id::text, 3, ‘::1’::inet);
RAISE EXCEPTION ‘ :: Operacion invalida, estado sin eliminarse :: ‘;
RETURN NULL;
END IF;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

–#—————————————-

DO $$
DECLARE
row record;
cmd text;
BEGIN
FOR row IN SELECT schemaname, tablename FROM pg_tables WHERE schemaname = ‘public’ LOOP
cmd := format(‘CREATE TRIGGER tr_i_schema AFTER INSERT OR UPDATE ON ‘||row.tablename||’ FOR EACH ROW EXECUTE PROCEDURE genera_schema();’, row.schemaname, row.tablename);
RAISE NOTICE ‘%’, cmd;
EXECUTE cmd;
END LOOP;
END
$$ LANGUAGE plpgsql;

DO $$
DECLARE
row record;
cmd text;
BEGIN
FOR row IN SELECT schemaname, tablename FROM pg_tables WHERE schemaname = ‘public’ LOOP
cmd := format(‘CREATE TRIGGER tr_i_schema AFTER INSERT OR UPDATE ON ‘||row.tablename||’ FOR EACH ROW EXECUTE PROCEDURE genera_schema();’, row.schemaname, row.tablename);
RAISE NOTICE ‘%’, cmd;
EXECUTE cmd;
END LOOP;
END
$$ LANGUAGE plpgsql;

Anuncios