CREATE OR REPLACE FUNCTION genera_schema() RETURNS trigger AS $$
BEGIN
EXECUTE ‘CREATE SCHEMA “‘|| to_char(current_timestamp, ‘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
RETURN NEW;
ELSIF (TG_OP = ‘UPDATE’) THEN
RETURN NEW;
ELSE
RAISE EXCEPTION ‘ :: Operacion invalida, estado sin eliminarse :: ‘;
RETURN NULL;
END IF;
END IF;
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 trischema AFTER INSERT 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