Mercurial > hg > orthanc-databases
comparison PostgreSQL/Plugins/ResourceDeletedFunc.sql @ 435:326f8304daa1 pg-transactions
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; |