Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/ResourceDeletedFunc.sql @ 435:326f8304daa1 pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
new creating temporary tables inside functions
author Alain Mazy <am@osimis.io>
date Thu, 14 Dec 2023 09:25:45 +0100
parents 23c7af6f671a
children
comparison
equal deleted inserted replaced
434:23c7af6f671a 435:326f8304daa1
35 DECLARE 35 DECLARE
36 deleted_row RECORD; 36 deleted_row RECORD;
37 37
38 BEGIN 38 BEGIN
39 39
40 SET client_min_messages = warning; -- suppress NOTICE: relation "deletedresources" already exists, skipping
41
42 -- note: temporary tables are created at session (connection) level -> they are likely to exist
43 -- these tables are used by the triggers
44 CREATE TEMPORARY TABLE IF NOT EXISTS DeletedResources(
45 resourceType INTEGER NOT NULL,
46 publicId VARCHAR(64) NOT NULL
47 );
48
49 RESET client_min_messages;
50
51 -- clear the temporary table in case it has been created earlier in the session
52 DELETE FROM DeletedResources;
53
54 -- create/clear the DeletedFiles temporary table
55 PERFORM CreateDeletedFilesTemporaryTable();
56
57
58 -- delete the resource itself
40 DELETE FROM Resources WHERE internalId=id RETURNING * INTO deleted_row; 59 DELETE FROM Resources WHERE internalId=id RETURNING * INTO deleted_row;
41 -- note: there is a ResourceDeletedFunc trigger that will execute here and delete the parents if there are no remaining children + 60 -- note: there is a ResourceDeletedFunc trigger that will execute here and delete the parents if there are no remaining children +
42 61
43 -- If this resource still has siblings, keep track of the remaining parent 62 -- If this resource still has siblings, keep track of the remaining parent
44 -- (a parent that must not be deleted but whose LastUpdate must be updated) 63 -- (a parent that must not be deleted but whose LastUpdate must be updated)
48 AND EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_row.parentId); 67 AND EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_row.parentId);
49 68
50 END; 69 END;
51 70
52 $body$ LANGUAGE plpgsql; 71 $body$ LANGUAGE plpgsql;
72
73
74 CREATE OR REPLACE FUNCTION CreateDeletedFilesTemporaryTable(
75 ) RETURNS VOID AS $body$
76
77 BEGIN
78
79 SET client_min_messages = warning; -- suppress NOTICE: relation "deletedresources" already exists, skipping
80
81 -- note: temporary tables are created at session (connection) level -> they are likely to exist
82 CREATE TEMPORARY TABLE IF NOT EXISTS DeletedFiles(
83 uuid VARCHAR(64) NOT NULL,
84 fileType INTEGER,
85 compressedSize BIGINT,
86 uncompressedSize BIGINT,
87 compressionType INTEGER,
88 uncompressedHash VARCHAR(40),
89 compressedHash VARCHAR(40)
90 );
91
92 RESET client_min_messages;
93
94 -- clear the temporary table in case it has been created earlier in the session
95 DELETE FROM DeletedFiles;
96 END;
97
98 $body$ LANGUAGE plpgsql;