Notes

CREATE OR REPLACE FUNCTION buildPath(parentGuidDevice text, parentGuidVersion bigint, snapshotId bigint, fileDeleted boolean) RETURNS text AS
$$
declare
parentPath text;
tempId bigInt;
tempName text;
tempParentGuidDevice text;
tempParentGuidVersion bigInt;
isCurrent boolean;
snapshotTimestamp timestamp;
cloudFolderId bigInt;
tempSnapshot boolean;
currentSnapshotId bigInt;
snapshotIdToCheck bigint;
BEGIN
IF parentGuidDevice is null OR parentGuidDevice = '' THEN
RETURN parentGuidDevice;
END IF;

IF snapshotId = XXXXX THEN
RAISE WARNING 'buildPath (before loop): parentGuidDevice: % , parentGuidVersion: % , snapshotId: % , fileDeleted %', parentGuidDevice, parentGuidVersion, snapshotId, fileDeleted;
END IF;

isCurrent = false;
currentSnapshotId = 0;
snapshotIdToCheck = snapshotId;
WHILE parentGuidDevice != '' LOOP
IF isCurrent = false THEN
SELECT current, timestamp, cloud_folder_id, is_temp INTO isCurrent, snapshotTimestamp, cloudFolderId, tempSnapshot from snapshots where id = snapshotIdToCheck;
END IF;
IF isCurrent = true THEN
IF currentSnapshotId = 0 THEN
IF tempSnapshot = true THEN
select id, timestamp into snapshotIdToCheck, snapshotTimestamp from snapshots where cloud_folder_id = cloudFolderId and current = true and is_temp = false;
tempSnapshot = false;
END IF;
currentSnapshotId = snapshotIdToCheck;
END IF;

IF fileDeleted = false THEN
select files.id, name, parent_guid_device, parent_guid_version into tempId, tempName, tempParentGuidDevice, tempParentGuidVersion from files
where guid_device = parentGuidDevice and guid_version = parentGuidVersion and snapshot_id = currentSnapshotId and deleted = false;
ELSE
select files.id, name, parent_guid_device, parent_guid_version into tempId, tempName, tempParentGuidDevice, tempParentGuidVersion from files
where guid_device = parentGuidDevice and guid_version = parentGuidVersion and snapshot_id = currentSnapshotId;
END IF;
ELSE
IF fileDeleted = false THEN
select files.id, name, parent_guid_device, parent_guid_version, snapshot_id into tempId, tempName, tempParentGuidDevice, tempParentGuidVersion, snapshotIdToCheck from files,
(select * from snapshots where snapshots.cloud_folder_id = cloudFolderId and snapshots.timestamp >= snapshotTimestamp and snapshots.is_temp = false) as sId
where guid_device = parentGuidDevice and guid_version = parentGuidVersion and snapshot_id = sId.id and deleted = false
order by guid_device, guid_version, sId.timestamp, deleted limit 1;
ELSE
select files.id, name, parent_guid_device, parent_guid_version, snapshot_id into tempId, tempName, tempParentGuidDevice, tempParentGuidVersion, snapshotIdToCheck from files,
(select * from snapshots where snapshots.cloud_folder_id = cloudFolderId and snapshots.timestamp >= snapshotTimestamp and snapshots.is_temp = false) as sId
where guid_device = parentGuidDevice and guid_version = parentGuidVersion and snapshot_id = sId.id
order by guid_device, guid_version, sId.timestamp, deleted limit 1;
END IF;

END IF;

IF tempParentGuidDevice is null THEN
RETURN tempParentGuidDevice;
END IF;

IF tempName LIKE '%.ctera.deleted%' or tempName LIKE concat('%.', tempId) THEN
RETURN null;
END IF;

IF parentPath is not null and parentPath !='' THEN
parentPath = concat(tempName, '/', parentPath);
ELSE
parentPath = tempName;
END IF;

parentGuidDevice = tempParentGuidDevice;
parentGuidVersion = tempParentGuidVersion;

IF snapshotId = XXXXX THEN
RAISE WARNING 'buildPath (inside loop): parentPath: % , parentGuidDevice: % , parentGuidVersion: % , snapshotId: % , snapshotIdToCheck: % , fileDeleted %', parentPath, parentGuidDevice, parentGuidVersion, snapshotId, snapshotIdToCheck, fileDeleted;
END IF;

END LOOP;

RETURN parentPath;
END;
$$ LANGUAGE plpgsql;