view PostgreSQL/Plugins/FastCountResources.sql @ 241:a063bbf10a3e

simplification of class DatabaseManager::Transaction
author Sebastien Jodogne <s.jodogne@gmail.com>
date Tue, 13 Apr 2021 12:07:30 +0200
parents cb0aac9bbada
children
line wrap: on
line source

-- https://wiki.postgresql.org/wiki/Count_estimate

INSERT INTO GlobalIntegers
SELECT 2, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 0;  -- Count patients

INSERT INTO GlobalIntegers
SELECT 3, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 1;  -- Count studies

INSERT INTO GlobalIntegers
SELECT 4, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 2;  -- Count series

INSERT INTO GlobalIntegers
SELECT 5, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 3;  -- Count instances


CREATE OR REPLACE FUNCTION CountResourcesTrackerFunc()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE GlobalIntegers SET value = value + 1 WHERE key = new.resourceType + 2;
    RETURN new;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE GlobalIntegers SET value = value - 1 WHERE key = old.resourceType + 2;
    RETURN old;
  END IF;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER CountResourcesTracker
AFTER INSERT OR DELETE ON Resources
FOR EACH ROW
EXECUTE PROCEDURE CountResourcesTrackerFunc();