view PostgreSQL/Plugins/FastCountResources.sql @ 255:d663d9e44f8d

reintroduction of IDatabaseFactory into DatabaseManager
author Sebastien Jodogne <s.jodogne@gmail.com>
date Wed, 14 Apr 2021 17:57:08 +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();