--- a/PostgreSQL/Plugins/ResourceDeletedFunc.sql	Wed Dec 13 16:52:06 2023 +0100
+++ b/PostgreSQL/Plugins/ResourceDeletedFunc.sql	Thu Dec 14 09:25:45 2023 +0100
@@ -37,6 +37,25 @@
 
 BEGIN
 
+    SET client_min_messages = warning;   -- suppress NOTICE:  relation "deletedresources" already exists, skipping
+    
+    -- note: temporary tables are created at session (connection) level -> they are likely to exist
+    -- these tables are used by the triggers
+    CREATE TEMPORARY TABLE IF NOT EXISTS DeletedResources(
+        resourceType INTEGER NOT NULL,
+        publicId VARCHAR(64) NOT NULL
+        );
+
+    RESET client_min_messages;
+
+    -- clear the temporary table in case it has been created earlier in the session
+    DELETE FROM DeletedResources;
+    
+    -- create/clear the DeletedFiles temporary table
+    PERFORM CreateDeletedFilesTemporaryTable();
+
+
+    -- delete the resource itself
     DELETE FROM Resources WHERE internalId=id RETURNING * INTO deleted_row;
     -- note: there is a ResourceDeletedFunc trigger that will execute here and delete the parents if there are no remaining children + 
 
@@ -50,3 +69,30 @@
 END;
 
 $body$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION CreateDeletedFilesTemporaryTable(
+) RETURNS VOID AS $body$
+
+BEGIN
+
+    SET client_min_messages = warning;   -- suppress NOTICE:  relation "deletedresources" already exists, skipping
+    
+    -- note: temporary tables are created at session (connection) level -> they are likely to exist
+    CREATE TEMPORARY TABLE IF NOT EXISTS DeletedFiles(
+        uuid VARCHAR(64) NOT NULL,
+        fileType INTEGER,
+        compressedSize BIGINT,
+        uncompressedSize BIGINT,
+        compressionType INTEGER,
+        uncompressedHash VARCHAR(40),
+        compressedHash VARCHAR(40)
+        );
+
+    RESET client_min_messages;
+
+    -- clear the temporary table in case it has been created earlier in the session
+    DELETE FROM DeletedFiles;
+END;
+
+$body$ LANGUAGE plpgsql;