Mercurial > hg > orthanc-databases
diff 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 |
line wrap: on
line diff
--- 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;