/* | Job Scheduler Reporting Interface cleanup procedure for PostgreSQL | SOS GmbH, 2017-02-24 | | Works for PostgreSQL version 8.4 or higher | | Usage | 1) Remove entries older as n (14) days | 1.1) Remove all entries (reporting, dailyplan, yade) | SELECT REPORT_CLEANUP('all',14); | 1.2) Remove only reporting entries | SELECT REPORT_CLEANUP('reporting',14); | 1.3) Remove only dailyplan entries | SELECT REPORT_CLEANUP('dailyplan',14); | 1.4) Remove only yade entries | SELECT REPORT_CLEANUP('yade',14); | 2) Remove all entries | 2.1) Remove all entries (reporting, dailyplan, yade) | SELECT REPORT_CLEANUP('all',0); | 2.2) Remove only reporting entries | SELECT REPORT_CLEANUP('reporting',0); | 2.3) Remove only dailyplan entries | SELECT REPORT_CLEANUP('dailyplan',0); | 2.4) Remove only yade entries | SELECT REPORT_CLEANUP('yade',0); */ CREATE OR REPLACE FUNCTION REPORT_CLEANUP(cleanupRange VARCHAR, age NUMERIC) RETURNS void AS $$ DECLARE endDatetime TIMESTAMP; BEGIN IF age <= 0 THEN IF cleanupRange = 'all' OR cleanupRange = 'reporting' THEN TRUNCATE TABLE REPORTING_TRIGGERS; ALTER SEQUENCE REPORTING_SEQ_RT RESTART WITH 1; TRUNCATE TABLE REPORTING_EXECUTIONS; ALTER SEQUENCE REPORTING_SEQ_RE RESTART WITH 1; TRUNCATE TABLE REPORTING_TASKS; ALTER SEQUENCE REPORTING_SEQ_RTS RESTART WITH 1; TRUNCATE TABLE REPORTING_EXECUTION_DATES; ALTER SEQUENCE REPORTING_SEQ_RED RESTART WITH 1; END IF; IF cleanupRange = 'all' OR cleanupRange = 'dailyplan' THEN TRUNCATE TABLE DAILY_PLAN; ALTER SEQUENCE DAILY_PLAN_ID_SEQ RESTART WITH 1; END IF; IF cleanupRange = 'all' OR cleanupRange = 'yade' THEN TRUNCATE TABLE JADE_FILES; ALTER SEQUENCE JADE_FILES_ID_SEQ RESTART WITH 1; TRUNCATE TABLE JADE_FILES_HISTORY; TRUNCATE TABLE JADE_FILES_POSITIONS; END IF; ELSE endDatetime := CURRENT_DATE-(age-1)*INTERVAL '1 day'; -- yyyy-mm-dd 00:00:00.000 IF cleanupRange = 'all' OR cleanupRange = 'reporting' THEN DELETE FROM REPORTING_EXECUTION_DATES WHERE "REFERENCE_TYPE" = 0 AND "REFERENCE_ID" IN (SELECT "ID" FROM REPORTING_TRIGGERS WHERE "START_TIME" < endDatetime); DELETE FROM REPORTING_EXECUTION_DATES WHERE "REFERENCE_TYPE" = 1 AND "REFERENCE_ID" IN (SELECT "ID" FROM REPORTING_EXECUTIONS WHERE "TRIGGER_ID" IN (SELECT "ID" from REPORTING_TRIGGERS WHERE "START_TIME" < endDatetime)); DELETE FROM REPORTING_EXECUTION_DATES WHERE "REFERENCE_TYPE" = 2 AND "REFERENCE_ID" IN (SELECT "ID" FROM REPORTING_TASKS WHERE "START_TIME" < endDatetime); DELETE FROM REPORTING_EXECUTIONS WHERE "TRIGGER_ID" IN (SELECT "ID" from REPORTING_TRIGGERS WHERE "START_TIME" < endDatetime); DELETE FROM REPORTING_TRIGGERS WHERE "START_TIME" < endDatetime; DELETE FROM REPORTING_TASKS WHERE "START_TIME" < endDatetime; END IF; IF cleanupRange = 'all' OR cleanupRange = 'dailyplan' THEN DELETE FROM DAILY_PLAN WHERE "PLANNED_START" < endDatetime; END IF; IF cleanupRange = 'all' OR cleanupRange = 'yade' THEN DELETE FROM JADE_FILES WHERE "ID" IN (SELECT "JADE_ID" FROM JADE_FILES_HISTORY WHERE "CREATED" < endDatetime); DELETE FROM JADE_FILES_HISTORY WHERE "CREATED" < endDatetime; END IF; END IF; END; $$ LANGUAGE plpgsql; COMMIT;