maya.public

Parameters

Name	Type	Mode

Definition

DECLARE 
BEGIN
/*

To refresh the materialized view mvw_mayauser_device

*/
SET work_mem = '160MB';
SET statement_timeout = '30 s';
SET temp_buffers = '64MB';
SET client_min_messages = 'error';
DROP TABLE IF EXISTS tmp_mayauser_device;
CREATE TEMPORARY TABLE tmp_mayauser_device AS 
SELECT DISTINCT mu.idmayauser,d.iddevice,now() lastupdate
FROM mayauser mu 
JOIN mayauser_mayausergroup mmu ON mu.idmayauser = mmu.idmayauser 
JOIN mayausergroup mug ON mmu.idmayausergroup = mug.idmayausergroup
JOIN coverage_usergroup cou ON cou.idmayausergroup = mmu.idmayausergroup
JOIN coverage co ON co.idcoverage = cou.idcoverage
JOIN coverage_unitgroup coug ON coug.idcoverage = cou.idcoverage
JOIN unitgroup ug ON ug.idunitgroup = coug.idunitgroup
JOIN unit_unitgroup uug ON uug.idunitgroup = ug.idunitgroup
JOIN unit u ON u.idunit =  uug.idunit
JOIN device d ON d.idunit = u.idunit
WHERE 
mu.enabled = true AND 
mug.enabled = true AND
co.enabled = true AND
ug.enabled = true AND
uug.enabled = true AND
d.enabled = true AND
d.status = 1 AND /* enabled from the billing system */
u.enabled = true AND
mmu.enabled = true
;

WITH q AS (SELECT iddevice,idmayauser FROM mvw_mayauser_device EXCEPT SELECT iddevice,idmayauser FROM tmp_mayauser_device)

DELETE FROM mvw_mayauser_device m using q where m.idmayauser = q.idmayauser AND m.iddevice = q.iddevice;

WITH q AS (SELECT iddevice,idmayauser FROM tmp_mayauser_device EXCEPT SELECT iddevice,idmayauser FROM mvw_mayauser_device)

INSERT INTO mvw_mayauser_device (idmayauser,iddevice,lastupdate) SELECT idmayauser,iddevice,now() FROM q;

DROP TABLE tmp_mayauser_device;

END;

ot_rw_update_mvw_mayauser_device()

Parameters

Definition