1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
CREATE TABLE GlobalIntegers(
key INTEGER PRIMARY KEY,
value BIGINT);
INSERT INTO GlobalIntegers
SELECT 0, CAST(COALESCE(SUM(compressedSize), 0) AS BIGINT) FROM AttachedFiles;
INSERT INTO GlobalIntegers
SELECT 1, CAST(COALESCE(SUM(uncompressedSize), 0) AS BIGINT) FROM AttachedFiles;
CREATE FUNCTION AttachedFileIncrementSizeFunc()
RETURNS TRIGGER AS $body$
BEGIN
UPDATE GlobalIntegers SET value = value + new.compressedSize WHERE key = 0;
UPDATE GlobalIntegers SET value = value + new.uncompressedSize WHERE key = 1;
RETURN NULL;
END;
$body$ LANGUAGE plpgsql;
CREATE FUNCTION AttachedFileDecrementSizeFunc()
RETURNS TRIGGER AS $body$
BEGIN
UPDATE GlobalIntegers SET value = value - old.compressedSize WHERE key = 0;
UPDATE GlobalIntegers SET value = value - old.uncompressedSize WHERE key = 1;
RETURN NULL;
END;
$body$ LANGUAGE plpgsql;
CREATE TRIGGER AttachedFileIncrementSize
AFTER INSERT ON AttachedFiles
FOR EACH ROW
EXECUTE PROCEDURE AttachedFileIncrementSizeFunc();
CREATE TRIGGER AttachedFileDecrementSize
AFTER DELETE ON AttachedFiles
FOR EACH ROW
EXECUTE PROCEDURE AttachedFileDecrementSizeFunc();
|
