maya
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
ot_rw_message_cancel_old(interval)
Parameters
Name
Type
Mode
retention_period
interval
IN
Definition
/* v20181112 RRB To cancel all pending messages older than retention_period, starting from now() */ DECLARE senddate_threshold timestamptz; total_rows bigint; BEGIN senddate_threshold := now() - retention_period; WITH q AS (SELECT idmessage FROM message m INNER JOIN device d USING(iddevice) INNER JOIN technicalaccount t USING(idtechnicalaccount) WHERE messagestatus = 1 AND m.senddate < senddate_threshold), /* PRODUCTS THAT INCLUDE A KEYBOARD */ Q1 AS (UPDATE message m set messagestatus = 5 FROM q WHERE q.idmessage = m.idmessage AND m.senddate < senddate_threshold RETURNING *) SELECT count(*) INTO total_rows FROM Q1; RETURN total_rows; END;