view PostgreSQL/Plugins/FastTotalSize.sql @ 366:cd9521e04249 attach-custom-data

DatabaseBackendAdapterV4: added support for customData + revision when not already done
author Alain Mazy <am@osimis.io>
date Thu, 15 Sep 2022 18:12:34 +0200
parents 8dd29af7c844
children
line wrap: on
line source

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();