maya.public

Parameters

Name	Type	Mode

Definition

DECLARE
r record;
_oidalertrecent oid;
BEGIN
/* To keep only the latest 8 partitions of alert linked alert_recent */
DROP table if exists a;
CREATE temporary table a as SELECT 'alert_p'||extract('year' from g)||'_'||extract('month' from g)||'_'||lpad(extract('day' from g)::varchar,2,'0')::varchar  as p FROM generate_series(now()::date - '6 days'::interval,now()::date + '1 day'::interval,'1 day'::interval) g;
/* New tables to inherit*/
SELECT oid INTO _oidalertrecent FROM pg_class WHERE relname = 'alert_recent'; 
FOR r IN SELECT p FROM a EXCEPT SELECT c.oid::pg_catalog.regclass::varchar FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = _oidalertrecent LOOP
EXECUTE format('ALTER TABLE %I INHERIT alert_recent; ',r.p);
END LOOP;

/* Old tables to uninherit*/
FOR r IN SELECT c.oid::pg_catalog.regclass::varchar as p FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = _oidalertrecent EXCEPT SELECT p FROM a LOOP
EXECUTE format('ALTER TABLE %I NO INHERIT alert_recent; ',r.p);
END LOOP;
RETURN true;
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '%,%', SQLSTATE,SQLERRM;
END

ot_rw_maint_alert_recent()

Parameters

Definition