Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/FastCountResources.sql @ 79:cb0aac9bbada db-changes

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
optimization for /statistics URI
author Sebastien Jodogne <s.jodogne@gmail.com>
date Tue, 08 Jan 2019 14:37:41 +0100
parents
children
comparison
equal deleted inserted replaced
78:2ee166f77501 79:cb0aac9bbada
1 -- https://wiki.postgresql.org/wiki/Count_estimate
2
3 INSERT INTO GlobalIntegers
4 SELECT 2, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 0; -- Count patients
5
6 INSERT INTO GlobalIntegers
7 SELECT 3, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 1; -- Count studies
8
9 INSERT INTO GlobalIntegers
10 SELECT 4, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 2; -- Count series
11
12 INSERT INTO GlobalIntegers
13 SELECT 5, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 3; -- Count instances
14
15
16 CREATE OR REPLACE FUNCTION CountResourcesTrackerFunc()
17 RETURNS TRIGGER AS $$
18 BEGIN
19 IF TG_OP = 'INSERT' THEN
20 UPDATE GlobalIntegers SET value = value + 1 WHERE key = new.resourceType + 2;
21 RETURN new;
22 ELSIF TG_OP = 'DELETE' THEN
23 UPDATE GlobalIntegers SET value = value - 1 WHERE key = old.resourceType + 2;
24 RETURN old;
25 END IF;
26 END;
27 $$ LANGUAGE plpgsql;
28
29
30 CREATE TRIGGER CountResourcesTracker
31 AFTER INSERT OR DELETE ON Resources
32 FOR EACH ROW
33 EXECUTE PROCEDURE CountResourcesTrackerFunc();