Работа с базой данных

Средний

BPMSoft поддерживает работу с несколькими системами управления базами данных. Подробнее: Системные требования.

Для взаимодействия с базами данных могут использоваться следующие популярные инструменты: Microsoft SQL Server Management Studio (для Microsoft SQL Server), PgAdmin4 (для PostgreSQL), а также универсальные решения, такие как DBeaver, DataGrip и другие.

Общие рекомендации для работы в PostgreSQL

Если база данных BPMSoft развернута на системе управления базами данных PostgreSQL, рекомендуется следовать следующим советам для упрощения работы:

В PostgreSQL вместо схемы "dbo" следует использовать схему "public".
Имена таблиц, представлений, колонок и других объектов рекомендуется указывать в двойных кавычках ("").
Допускается сокращение проверки значения поля типа BOOLEAN до конструкции WHERE "boolColumn" или WHERE NOT "boolColumn".
Допускается использование сокращенного вида явного преобразования ::TEXT.
Для регистронезависимого сравнения строк можно использовать iLIKE либо UPPER+LIKE (работает быстрее). У комбинации UPPER+LIKE менее строгие правила применимости индексов, чем у iLIKE.
Если автоматическое приведение типов отсутствует, преобразование можно настроить вручную с помощью CREATE CAST. Приведение типов описано в официальной документации PostgreSQL.
Всегда проверяйте наличие функций, представлений и триггеров в базе данных перед их созданием с помощью конструкции
DROP … IF EXISTS (при необходимости допускается использование команды CASCADE).
Для хранения текущего уровня рекурсии создайте специальный параметр процедуры, поскольку в рекурсивных процедурах PostgreSQL отсутствует встроенная функция NESTLEVEL.
В PostgreSQL для имен системных объектов используется NAME. В Microsoft SQL Server этому типу соответствует SYSNAME.
Вместо пустого INSTEAD-триггера целесообразно использовать RULE. Например:

			 CREATE RULE RU_VwContactRelationship AS
ON UPDATE TO "VwContactRelationship"
DO INSTEAD NOTHING;

При обновлении данных преобразование значения типа INT в BOOL следует выполнять явно, поскольку в PostgreSQL не всегда срабатывает неявное приведение такого типа в подобных сценариях.
Для формирования строковых литералов и идентификаторов следует использовать корректные и безопасные способы форматирования. Строковые литералы подробно описаны в официальной документации PostgreSQL (quote_ident, quote_literal, format).
Вместо @@ROWCOUNT используйте конструкцию:

			 DECLARE rowsCount BIGINT = 0;
GET DIAGNOSTICS rowsCount = row_count;

Используйте конструкцию:

			 EXISTS (
	SELECT 1
	FROM "SysSSPEntitySchemaAccessList"
	WHERE "EntitySchemaUId" = BaseSchema."UId"
)
"IsInSSPEntitySchemaAccessList"
		

 
	

Вместо Microsoft SQL Server-конструкции:

			 (CASE
	WHEN EXISTS
		(SELECT 1
			FROM [SysSSPEntitySchemaAccessList]
			WHERE
[SysSSPEntitySchemaAccessList].[EntitySchemaUId] = [BaseSchemas].[UId] ) THEN 1
	ELSE 0
END) AS
[IsInSSPEntitySchemaAccessList]
		

 
	

Поле, полученное в результате выполнения запроса, будет иметь тип BOOLEAN.

Соответствие типов данных

Таблица 1 — Соответствие между типами данных системы и баз данных

Тип данных в дизайнере объектов BPMSoft	Тип данных Microsoft SQL Server	Тип данных PostgreSQL
Двоичные данные	VARBINARY	BYTEA
Логическое	BIT	BOOLEAN
Цвет	NVARCHAR	CHARACTER VARYING
Контрольная сумма строки	NVARCHAR	CHARACTER VARYING
Деньги	DECIMAL	NUMERIC
Дата	DATE	DATE
Дата/Время	DATETIME2	TIMESTAMP WITHOUT TIME ZONE
Дробное число (0.00000001)	DECIMAL	NUMERIC
Дробное число (0.0001)	DECIMAL	NUMERIC
Дробное число (0.001)	DECIMAL	NUMERIC
Дробное число (0.01)	DECIMAL	NUMERIC
Дробное число (0.1)	DECIMAL	NUMERIC
Зашифрованная строка	NVARCHAR	CHARACTER VARYING
Файл	VARBINARY	BYTEA
Изображение	VARBINARY	BYTEA
Ссылка на изображение	UNIQUEIDENTIFIER	UUID
Целое число	INTEGER	INTEGER
Справочник	UNIQUEIDENTIFIER	UUID
Строка (50 символов)	NVARCHAR(50)	CHARACTER VARYING
Строка (250 символов)	NVARCHAR(250)	CHARACTER VARYING
Строка (500 символов)	NVARCHAR(500)	CHARACTER VARYING
Время	TIME	TIME WITHOUT TIME ZONE
Уникальный идентификатор	UNIQUEIDENTIFIER	UUID
Строка неограниченной длины	NVARCHAR(MAX)	TEXT

Примеры скриптов для Microsoft SQL Server и PostgreSQL

Пример 1 (представления)

Пример. SQL-скрипт создает представления и триггеры для добавления, изменения и удаления записей из целевой таблицы «Contact».

Microsoft SQL Server

-- Создание представления и триггеров для редактирования таблицы Contact
-- MS SQL
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[VwContact]'))
DROP VIEW [dbo].[VwContact]
GO
CREATE VIEW [dbo].[VwContact]
AS
	SELECT [Contact].[Id]
	,[Contact].[CreatedOn]
	,[Contact].[CreatedById]
	,[Contact].[ModifiedOn]
	,[Contact].[ModifiedById]
	,[Contact].[Name]
	,[Contact].[Description]
	,[Account].[Id] AS [AccountId]
	,[Contact].[OwnerId]
	,[Contact].[ProcessListeners]
	,[Contact].[Dear]
	,[Contact].[SalutationTypeId]
	,[Contact].[GenderId]
	,[Contact].[DecisionRoleId]
	,[Contact].[TypeId]
	,[Contact].[JobId]
	,[Contact].[JobTitle]
	,[Contact].[DepartmentId]
	,[Contact].[BirthDate]
	,[Contact].[Phone]
	,[Contact].[MobilePhone]
	,[Contact].[HomePhone]
	,[Contact].[Skype]
	,[Contact].[Email]
	,[Contact].[AddressTypeId]
	,[Contact].[Address]
	,[Contact].[CityId]
	,[Region].[Id] AS [RegionId]
	,[Contact].[Zip]
	,[Country].[Id] AS [CountryId]
	,[Contact].[DoNotUseEmail]
	,[Contact].[DoNotUseCall]
	,[Contact].[DoNotUseFax]
	,[Contact].[DoNotUseSms]
	,[Contact].[DoNotUseMail]
	,[Contact].[Notes]
	,[Contact].[ContactPhoto]
	,[SysImage].[Id] AS [SysImageId]
	,[Contact].[GPSN]
	,[Contact].[GPSE]
	,[Contact].[Surname]
	,[Contact].[GivenName]
	,[Contact].[MiddleName]
	,[Contact].[Confirmed]
	,[Contact].[LanguageId]
	,[Contact].[Completeness]
	,[Contact].[Age]
	,[Contact].[IsEmailConfirmed]
	FROM [Contact]
	INNER JOIN [Account] ON [Contact].[AccountId] = [Account].[Id]
	LEFT JOIN [Region] ON [Contact].[RegionId] = [Region].[Id]
	LEFT JOIN [Country] ON [Contact].[CountryId] = [Country].[Id]
	LEFT JOIN [SysImage] ON [Contact].[PhotoId] = [SysImage].[Id]
GO
CREATE TRIGGER [dbo].[ITR_VwContact_I]
ON [dbo].[VwContact]
INSTEAD OF INSERT
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO [Contact](
		[Id]
		,[CreatedOn]
		,[CreatedById]
		,[ModifiedOn]
		,[ModifiedById]
		,[Name]
		,[Description]
		,[AccountId]
		,[OwnerId]
		,[ProcessListeners]
		,[Dear]
		,[SalutationTypeId]
		,[GenderId]
		,[DecisionRoleId]
		,[TypeId]
		,[JobId]
		,[JobTitle]
		,[DepartmentId]
		,[BirthDate]
		,[Phone]
		,[MobilePhone]
		,[HomePhone]
		,[Skype]
		,[Email]
		,[AddressTypeId]
		,[Address]
		,[CityId]
		,[RegionId]
		,[Zip]
		,[CountryId]
		,[DoNotUseEmail]
		,[DoNotUseCall]
		,[DoNotUseFax]
		,[DoNotUseSms]
		,[DoNotUseMail]
		,[Notes]
		,[ContactPhoto]
		,[PhotoId]
		,[GPSN]
		,[GPSE]
		,[Surname]
		,[GivenName]
		,[MiddleName]
		,[Confirmed]
		,[LanguageId]
		,[Completeness]
		,[Age]
		,[IsEmailConfirmed])
	SELECT [Id]
	,[CreatedOn]
	,[CreatedById]
	,[ModifiedOn]
	,[ModifiedById]
	,[Name]
	,[Description]
	,(SELECT COALESCE(
		(SELECT [Account].[Id] FROM [Account]
		WHERE [Account].[Id] = [INSERTED].[AccountId]), NULL))
		,[OwnerId]
		,[ProcessListeners]
		,[Dear]
		,[SalutationTypeId]
		,[GenderId]
		,[DecisionRoleId]
		,[TypeId]
		,[JobId]
		,[JobTitle]
		,[DepartmentId]
		,[BirthDate]
		,[Phone]
		,[MobilePhone]
		,[HomePhone]
		,[Skype]
		,[Email]
		,[AddressTypeId]
		,[Address]
		,[CityId]
		,(SELECT COALESCE(
			(SELECT [Region].[Id] FROM [Region]
			WHERE [Region].[Id] = [INSERTED].[RegionId]), NULL))
			,[Zip]
			,(SELECT COALESCE(
				(SELECT [Country].[Id] FROM [Country]
				WHERE [Country].[Id] = [INSERTED].[CountryId]), NULL))
				,[DoNotUseEmail]
				,[DoNotUseCall]
				,[DoNotUseFax]
				,[DoNotUseSms]
				,[DoNotUseMail]
				,[Notes]
				,[ContactPhoto]
				,(SELECT COALESCE(
					(SELECT [SysImage].[Id] FROM [SysImage]
					WHERE [SysImage].[Id] = [INSERTED].[SysImageId]), NULL))
					,[GPSN]
					,[GPSE]
					,[Surname]
					,[GivenName]
					,[MiddleName]
					,[Confirmed]
					,[LanguageId]
					,[Completeness]
					,[Age]
					,[IsEmailConfirmed]
					FROM [INSERTED]
END
GO
CREATE TRIGGER [dbo].[ITR_VwContact_U]
ON [dbo].[VwContact]
INSTEAD OF UPDATE
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE [Contact]
	SET [Contact].[CreatedOn] = [INSERTED].[CreatedOn]
	,[Contact].[CreatedById] = [INSERTED].[CreatedById]
	,[Contact].[ModifiedOn] = [INSERTED].[ModifiedOn]
	,[Contact].[ModifiedById] = [INSERTED].[ModifiedById]
	,[Contact].[Name] = [INSERTED].[Name]
	,[Contact].[Description] = [INSERTED].[Description]
	,[Contact].[AccountId] = (SELECT COALESCE(
		(SELECT [Account].[Id] FROM [Account]
		WHERE [Account].[Id] = [INSERTED].[AccountId]), NULL))
		,[Contact].[OwnerId] = [INSERTED].[OwnerId]
		,[Contact].[ProcessListeners] = [INSERTED].[ProcessListeners]
		,[Contact].[Dear] = [INSERTED].[Dear]
		,[Contact].[SalutationTypeId] = [INSERTED].[SalutationTypeId]
		,[Contact].[GenderId] = [INSERTED].[GenderId]
		,[Contact].[DecisionRoleId] = [INSERTED].[DecisionRoleId]
		,[Contact].[TypeId] = [INSERTED].[TypeId]
		,[Contact].[JobId] = [INSERTED].[JobId]
		,[Contact].[JobTitle] = [INSERTED].[JobTitle]
		,[Contact].[DepartmentId] = [INSERTED].[DepartmentId]
		,[Contact].[BirthDate] = [INSERTED].[BirthDate]
		,[Contact].[Phone] = [INSERTED].[Phone]
		,[Contact].[MobilePhone] = [INSERTED].[MobilePhone]
		,[Contact].[HomePhone] = [INSERTED].[HomePhone]
		,[Contact].[Skype] = [INSERTED].[Skype]
		,[Contact].[Email] = [INSERTED].[Email]
		,[Contact].[AddressTypeId] = [INSERTED].[AddressTypeId]
		,[Contact].[Address] = [INSERTED].[Address]
		,[Contact].[CityId] = [INSERTED].[CityId]
		,[Contact].[RegionId] = (SELECT COALESCE(
			(SELECT [Region].[Id] FROM [Region]
			WHERE [Region].[Id] = [INSERTED].[RegionId]), NULL))
			,[Contact].[Zip] = [INSERTED].[Zip]
			,[Contact].[CountryId] = (SELECT COALESCE(
				(SELECT [Country].[Id] FROM [Country]
				WHERE [Country].[Id] = [INSERTED].[CountryId]), NULL))
				,[Contact].[DoNotUseEmail] = [INSERTED].[DoNotUseEmail]
				,[Contact].[DoNotUseCall] = [INSERTED].[DoNotUseCall]
				,[Contact].[DoNotUseFax] = [INSERTED].[DoNotUseFax]
				,[Contact].[DoNotUseSms] = [INSERTED].[DoNotUseSms]
				,[Contact].[DoNotUseMail] = [INSERTED].[DoNotUseMail]
				,[Contact].[Notes] = [INSERTED].[Notes]
				,[Contact].[ContactPhoto] = [INSERTED].[ContactPhoto]
				,[Contact].[PhotoId] = (SELECT COALESCE(
					(SELECT [SysImage].[Id] FROM [SysImage]
					WHERE [SysImage].[Id] = [INSERTED].[SysImageId]), NULL))
					,[Contact].[GPSN] = [INSERTED].[GPSN]
					,[Contact].[GPSE] = [INSERTED].[GPSE]
					,[Contact].[Surname] = [INSERTED].[Surname]
					,[Contact].[GivenName] = [INSERTED].[GivenName]
					,[Contact].[MiddleName] = [INSERTED].[MiddleName]
					,[Contact].[Confirmed] = [INSERTED].[Confirmed]
					,[Contact].[LanguageId] = [INSERTED].[LanguageId]
					,[Contact].[Completeness] = [INSERTED].[Completeness]
					,[Contact].[Age] = [INSERTED].[Age]
					,[Contact].[IsEmailConfirmed] = [INSERTED].[IsEmailConfirmed]
					FROM [Contact]
					INNER JOIN [INSERTED] ON [Contact].[Id] = [INSERTED].[Id]
END
GO
CREATE TRIGGER [dbo].[ITR_VwContact_D]
ON [dbo].[VwContact]
INSTEAD OF DELETE
AS
BEGIN
	SET NOCOUNT ON;
	DELETE FROM [Contact]
	WHERE EXISTS(SELECT * FROM [DELETED] WHERE [Contact].[Id] = [DELETED].[Id])
END
GO

PostgreSQL

-- Создание представления и триггеров для редактирования таблицы Contact
-- PostgreSql
DROP FUNCTION IF EXISTS "public"."ITR_VwContact_IUD_Func" CASCADE;
DROP VIEW IF EXISTS "public"."VwContact";

CREATE VIEW "public"."VwContact" AS
	SELECT "Contact"."Id"
	,"Contact"."CreatedOn"
	,"Contact"."CreatedById"
	,"Contact"."ModifiedOn"
	,"Contact"."ModifiedById"
	,"Contact"."Name"
	,"Contact"."Description"
	,"Account"."Id" AS "AccountId"
	,"Contact"."OwnerId"
	,"Contact"."ProcessListeners"
	,"Contact"."Dear"
	,"Contact"."SalutationTypeId"
	,"Contact"."GenderId"
	,"Contact"."DecisionRoleId"
	,"Contact"."TypeId"
	,"Contact"."JobId"
	,"Contact"."JobTitle"
	,"Contact"."DepartmentId"
	,"Contact"."BirthDate"
	,"Contact"."Phone"
	,"Contact"."MobilePhone"
	,"Contact"."HomePhone"
	,"Contact"."Skype"
	,"Contact"."Email"
	,"Contact"."AddressTypeId"
	,"Contact"."Address"
	,"Contact"."CityId"
	,"Region"."Id" AS "RegionId"
	,"Contact"."Zip"
	,"Country"."Id" AS "CountryId"
	,"Contact"."DoNotUseEmail"
	,"Contact"."DoNotUseCall"
	,"Contact"."DoNotUseFax"
	,"Contact"."DoNotUseSms"
	,"Contact"."DoNotUseMail"
	,"Contact"."Notes"
	,"Contact"."ContactPhoto"
	,"SysImage"."Id" AS "SysImageId"
	,"Contact"."GPSN"
	,"Contact"."GPSE"
	,"Contact"."Surname"
	,"Contact"."GivenName"
	,"Contact"."MiddleName"
	,"Contact"."Confirmed"
	,"Contact"."LanguageId"
	,"Contact"."Completeness"
	,"Contact"."Age"
	,"Contact"."IsEmailConfirmed"
	FROM "public"."Contact"
	INNER JOIN "Account" ON "Contact"."AccountId" = "Account"."Id"
	LEFT JOIN "Region" ON "Contact"."RegionId" = "Region"."Id"
	LEFT JOIN "Country" ON "Contact"."CountryId" = "Country"."Id"
	LEFT JOIN "SysImage" ON "Contact"."PhotoId" = "SysImage"."Id";

CREATE FUNCTION "public"."ITR_VwContact_IUD_Func"() RETURNS TRIGGER AS $$
BEGIN
	IF TG_OP = 'INSERT' THEN
		INSERT INTO "public"."Contact"(
			"Id"
			,"CreatedOn"
			,"CreatedById"
			,"ModifiedOn"
			,"ModifiedById"
			,"Name"
			,"Description"
			,"AccountId"
			,"OwnerId"
			,"ProcessListeners"
			,"Dear"
			,"SalutationTypeId"
			,"GenderId"
			,"DecisionRoleId"
			,"TypeId"
			,"JobId"
			,"JobTitle"
			,"DepartmentId"
			,"BirthDate"
			,"Phone"
			,"MobilePhone"
			,"HomePhone"
			,"Skype"
			,"Email"
			,"AddressTypeId"
			,"Address"
			,"CityId"
			,"RegionId"
			,"Zip"
			,"CountryId"
			,"DoNotUseEmail"
			,"DoNotUseCall"
			,"DoNotUseFax"
			,"DoNotUseSms"
			,"DoNotUseMail"
			,"Notes"
			,"ContactPhoto"
			,"PhotoId"
			,"GPSN"
			,"GPSE"
			,"Surname"
			,"GivenName"
			,"MiddleName"
			,"Confirmed"
			,"LanguageId"
			,"Completeness"
			,"Age"
			,"IsEmailConfirmed")
		SELECT NEW."Id"
		,NEW."CreatedOn"
		,NEW."CreatedById"
		,NEW."ModifiedOn"
		,NEW."ModifiedById"
		,NEW."Name"
		,NEW."Description"
		,NEW."AccountId"
		,NEW."OwnerId"
		,NEW."ProcessListeners"
		,NEW."Dear"
		,NEW."SalutationTypeId"
		,NEW."GenderId"
		,NEW."DecisionRoleId"
		,NEW."TypeId"
		,NEW."JobId"
		,NEW."JobTitle"
		,NEW."DepartmentId"
		,NEW."BirthDate"
		,NEW."Phone"
		,NEW."MobilePhone"
		,NEW."HomePhone"
		,NEW."Skype"
		,NEW."Email"
		,NEW."AddressTypeId"
		,NEW."Address"
		,NEW."CityId"
		,NEW."RegionId"
		,NEW."Zip"
		,NEW."CountryId"
		,NEW."DoNotUseEmail"
		,NEW."DoNotUseCall"
		,NEW."DoNotUseFax"
		,NEW."DoNotUseSms"
		,NEW."DoNotUseMail"
		,NEW."Notes"
		,NEW."ContactPhoto"
		,NEW."PhotoId"
		,NEW."GPSN"
		,NEW."GPSE"
		,NEW."Surname"
		,NEW."GivenName"
		,NEW."MiddleName"
		,NEW."Confirmed"
		,NEW."LanguageId"
		,NEW."Completeness"
		,NEW."Age"
		,NEW."IsEmailConfirmed";
		RETURN NEW;
		ELSIF TG_OP = 'UPDATE' THEN
		UPDATE "public"."Contact"
		SET "CreatedOn" = NEW."CreatedOn"
		,"CreatedById" = NEW."CreatedById"
		,"ModifiedOn" = NEW."ModifiedOn"
		,"ModifiedById" = NEW."ModifiedById"
		,"Name" = NEW."Name"
		,"Description" = NEW."Description"
		,"AccountId" = NEW."AccountId"
		,"OwnerId" = NEW."OwnerId"
		,"ProcessListeners" = NEW."ProcessListeners"
		,"Dear" = NEW."Dear"
		,"SalutationTypeId" = NEW."SalutationTypeId"
		,"GenderId" = NEW."GenderId"
		,"DecisionRoleId" = NEW."DecisionRoleId"
		,"TypeId" = NEW."TypeId"
		,"JobId" = NEW."JobId"
		,"JobTitle" = NEW."JobTitle"
		,"DepartmentId" = NEW."DepartmentId"
		,"BirthDate" = NEW."BirthDate"
		,"Phone" = NEW."Phone"
		,"MobilePhone" = NEW."MobilePhone"
		,"HomePhone" = NEW."HomePhone"
		,"Skype" = NEW."Skype"
		,"Email" = NEW."Email"
		,"AddressTypeId" = NEW."AddressTypeId"
		,"Address" = NEW."Address"
		,"CityId" = NEW."CityId"
		,"RegionId" = NEW."RegionId"
		,"Zip" = NEW."Zip"
		,"CountryId" = NEW."CountryId"
		,"DoNotUseEmail" = NEW."DoNotUseEmail"
		,"DoNotUseCall" = NEW."DoNotUseCall"
		,"DoNotUseFax" = NEW."DoNotUseFax"
		,"DoNotUseSms" = NEW."DoNotUseSms"
		,"DoNotUseMail" = NEW."DoNotUseMail"
		,"Notes" = NEW."Notes"
		,"ContactPhoto" = NEW."ContactPhoto"
		,"PhotoId" = NEW."PhotoId"
		,"GPSN" = NEW."GPSN"
		,"GPSE" = NEW."GPSE"
		,"Surname" = NEW."Surname"
		,"GivenName" = NEW."GivenName"
		,"MiddleName" = NEW."MiddleName"
		,"Confirmed" = NEW."Confirmed"
		,"LanguageId" = NEW."LanguageId"
		,"Completeness" = NEW."Completeness"
		,"Age" = NEW."Age"
		,"IsEmailConfirmed" = NEW."IsEmailConfirmed"
		WHERE "Contact"."Id" = NEW."Id";
		RETURN NEW;
		ELSIF TG_OP = 'DELETE' THEN
		DELETE FROM "public"."Contact" WHERE OLD."Id" = "Contact"."Id";
		RETURN OLD;
	END IF;
	RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER "ITR_VwContact_IUD"
INSTEAD OF INSERT OR UPDATE OR DELETE ON "public"."VwContact"
FOR EACH ROW EXECUTE PROCEDURE "public"."ITR_VwContact_IUD_Func"();

Пример 2 (представления)

Пример. SQL-скрипт создает представление и правило обновления данных (только для PostgreSQL).

Microsoft SQL Server

-- Использование rule вместо instead of триггера невозможно
-- MSSQL
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[VwAdministrativeObjects]'))
DROP VIEW [dbo].[VwAdministrativeObjects]
GO
CREATE VIEW [dbo].[VwAdministrativeObjects]
AS
	WITH
	[SysSchemaAdministrationProperties] AS (
		SELECT [AdministrationPropertiesAll].[Id] AS [SysSchemaId],
		max([AdministrationPropertiesAll].[AdministratedByOperations]) AS [AdministratedByOperations],
		max([AdministrationPropertiesAll].[AdministratedByColumns]) AS [AdministratedByColumns],
		max([AdministrationPropertiesAll].[AdministratedByRecords]) AS [AdministratedByRecords],
		max([AdministrationPropertiesAll].[IsTrackChangesInDB]) AS [IsTrackChangesInDB]
		FROM (
			SELECT [SysSchema].[Id],
			(CASE WHEN EXISTS (
				SELECT 1
				FROM [SysSchemaProperty]
				WHERE (([SysSchemaProperty].[SysSchemaId] = [SysSchema].[Id] AND [SysSchema].[ExtendParent] = 0)
				OR [SysSchemaProperty].[SysSchemaId] = [DerivedSysSchema].[Id])
				AND [SysSchemaProperty].[Name] = 'AdministratedByOperations'
				AND [SysSchemaProperty].[Value] = 'True'
				AND [SysSchemaProperty].[SysSchemaId] IS NOT NULL
			)
			THEN 1 ELSE 0 END) AS [AdministratedByOperations],
			(CASE WHEN EXISTS (
				SELECT 1
				FROM [SysSchemaProperty]
				WHERE (([SysSchemaProperty].[SysSchemaId] = [SysSchema].[Id] AND [SysSchema].[ExtendParent] = 0)
				OR [SysSchemaProperty].[SysSchemaId] = [DerivedSysSchema].[Id])
				AND [SysSchemaProperty].[Name] = 'AdministratedByColumns'
				AND [SysSchemaProperty].[Value] = 'True'
				AND [SysSchemaProperty].[SysSchemaId] IS NOT NULL
			)
			THEN 1 ELSE 0 END) AS [AdministratedByColumns],
			(CASE WHEN EXISTS (
				SELECT 1
				FROM [SysSchemaProperty]
				WHERE (([SysSchemaProperty].[SysSchemaId] = [SysSchema].[Id] AND [SysSchema].[ExtendParent] = 0)
				OR [SysSchemaProperty].[SysSchemaId] = [DerivedSysSchema].[Id])
				AND [SysSchemaProperty].[Name] = 'AdministratedByRecords'
				AND [SysSchemaProperty].[Value] = 'True'
				AND [SysSchemaProperty].[SysSchemaId] IS NOT NULL
			)
			THEN 1 ELSE 0 END) AS [AdministratedByRecords],
			(CASE WHEN EXISTS (
				SELECT 1
				FROM [SysSchemaProperty]
				WHERE (([SysSchemaProperty].[SysSchemaId] = [SysSchema].[Id] AND [SysSchema].[ExtendParent] = 0)
				OR [SysSchemaProperty].[SysSchemaId] = [DerivedSysSchema].[Id])
				AND [SysSchemaProperty].[Name] = 'IsTrackChangesInDB'
				AND [SysSchemaProperty].[Value] = 'True'
				AND [SysSchemaProperty].[SysSchemaId] IS NOT NULL
			)
			THEN 1 ELSE 0 END) AS [IsTrackChangesInDB]
			FROM [SysSchema]
			LEFT OUTER JOIN [SysSchema] AS [DerivedSysSchema] ON ([SysSchema].[Id] = [DerivedSysSchema].[ParentId] AND [DerivedSysSchema].[ExtendParent] = 1)
			WHERE [SysSchema].[ManagerName] = 'EntitySchemaManager'
			AND [SysSchema].[ExtendParent] = 0
			) AS [AdministrationPropertiesAll]
			GROUP BY [AdministrationPropertiesAll].[Id]
			)
			SELECT [BaseSchemas].[UId] AS [Id],
			[BaseSchemas].[UId],
			[BaseSchemas].[CreatedOn],
			[BaseSchemas].[CreatedById],
			[BaseSchemas].[ModifiedOn],
			[BaseSchemas].[ModifiedById],
			[BaseSchemas].[Name],
			[VwSysSchemaExtending].[TopExtendingCaption] as Caption,
			[BaseSchemas].[Description],
			(CASE WHEN EXISTS (
				SELECT 1
				FROM [SysLookup]
			WHERE [SysLookup].[SysEntitySchemaUId] = [BaseSchemas].[UId])
			THEN 1 ELSE 0 END) AS [IsLookup],
			(CASE WHEN EXISTS (
				SELECT 1 FROM [SysModule]
				INNER JOIN [SysModuleEntity] ON [SysModuleEntity].[Id] = [SysModule].[SysModuleEntityId]
			WHERE [BaseSchemas].[UId] = [SysModuleEntity].[SysEntitySchemaUId])
			THEN 1 ELSE 0 END) AS [IsModule],
			[SysSchemaAdministrationProperties].[AdministratedByOperations],
			[SysSchemaAdministrationProperties].[AdministratedByColumns],
			[SysSchemaAdministrationProperties].[AdministratedByRecords],
			[SysSchemaAdministrationProperties].[IsTrackChangesInDB],
			[SysWorkspaceId],
			[BaseSchemas].[ProcessListeners],
			(CASE WHEN EXISTS (
				SELECT 1
				FROM [SysSSPEntitySchemaAccessList]
				WHERE [SysSSPEntitySchemaAccessList].[EntitySchemaUId] = [BaseSchemas].[UId]
			)
			THEN 1 ELSE 0 END) AS [IsInSSPEntitySchemaAccessList]
			FROM [SysSchema] as [BaseSchemas]
			INNER JOIN [VwSysSchemaExtending] ON BaseSchemas.[Id] = [VwSysSchemaExtending].[BaseSchemaId]
			INNER JOIN [SysPackage] on [BaseSchemas].[SysPackageId] = [SysPackage].[Id]
			INNER JOIN [SysSchemaAdministrationProperties] ON [BaseSchemas].[Id] = [SysSchemaAdministrationProperties].[SysSchemaId]
			GO
			CREATE TRIGGER [dbo].[TRVwAdministrativeObjects_IU]
			ON [dbo].[VwAdministrativeObjects]
			INSTEAD OF UPDATE
			AS
			BEGIN
			SET NOCOUNT ON;
			RETURN
			END
			GO

PostgreSQL

-- Использование rule вместо instead of триггера
-- PostgreSql
DROP VIEW IF EXISTS public."VwAdministrativeObjects";
DROP RULE IF EXISTS RU_VwAdministrativeObjects ON "VwAdministrativeObjects";

CREATE VIEW public."VwAdministrativeObjects" AS
	WITH SysSchemaAdministrationProperties AS (
		SELECT AdministrationPropertiesAll.Id "SysSchemaId",
		MAX(AdministrationPropertiesAll.AdministratedByOperations) "AdministratedByOperations",
		MAX(AdministrationPropertiesAll.AdministratedByColumns) "AdministratedByColumns",
		MAX(AdministrationPropertiesAll.AdministratedByRecords) "AdministratedByRecords",
		MAX(AdministrationPropertiesAll.IsTrackChangesInDB) "IsTrackChangesInDB"
		FROM (
			SELECT ss."Id" Id
			,(CASE WHEN EXISTS (
				SELECT 1
				FROM "SysSchemaProperty" ssp
				WHERE ((ssp."SysSchemaId" = ss."Id" AND NOT ss."ExtendParent") OR ssp."SysSchemaId" = DerivedSysSchema."Id")
				AND ssp."Name" = 'AdministratedByOperations'
				AND ssp."Value" = 'True'
			AND ssp."SysSchemaId" IS NOT NULL
			) THEN 1 ELSE 0 END) AdministratedByOperations
			,(CASE WHEN EXISTS (
				SELECT 1
				FROM "SysSchemaProperty" ssp
				WHERE ((ssp."SysSchemaId" = ss."Id" AND NOT ss."ExtendParent") OR ssp."SysSchemaId" = DerivedSysSchema."Id")
				AND ssp."Name" = 'AdministratedByColumns'
				AND ssp."Value" = 'True'
			AND ssp."SysSchemaId" IS NOT NULL
			) THEN 1 ELSE 0 END) AdministratedByColumns
			,(CASE WHEN EXISTS (
				SELECT 1
				FROM "SysSchemaProperty" ssp
				WHERE ((ssp."SysSchemaId" = ss."Id" AND NOT ss."ExtendParent") OR ssp."SysSchemaId" = DerivedSysSchema."Id")
				AND ssp."Name" = 'AdministratedByRecords'
				AND ssp."Value" = 'True'
			AND ssp."SysSchemaId" IS NOT NULL
			) THEN 1 ELSE 0 END) AdministratedByRecords
			,(CASE WHEN EXISTS (
				SELECT 1
				FROM "SysSchemaProperty" ssp WHERE ((ssp."SysSchemaId" = ss."Id" AND NOT ss."ExtendParent")
				OR ssp."SysSchemaId" = DerivedSysSchema."Id")
				AND ssp."Name" = 'IsTrackChangesInDB'
				AND ssp."Value" = 'True'
			AND ssp."SysSchemaId" IS NOT NULL
			) THEN 1 ELSE 0 END) IsTrackChangesInDB
			FROM "SysSchema" ss
			LEFT OUTER JOIN "SysSchema" DerivedSysSchema ON (ss."Id" = DerivedSysSchema."ParentId" AND DerivedSysSchema."ExtendParent")
			WHERE ss."ManagerName" = 'EntitySchemaManager' AND NOT ss."ExtendParent"
			) AdministrationPropertiesAll
			GROUP BY AdministrationPropertiesAll.Id
			)
			SELECT BaseSchema."UId" "Id"
			,BaseSchema."UId"
			,BaseSchema."CreatedOn"
			,BaseSchema."CreatedById"
			,BaseSchema."ModifiedOn"
			,BaseSchema."ModifiedById"
			,BaseSchema."Name"
			,public."VwSysSchemaExtending"."TopExtendingCaption" "Caption"
			,BaseSchema."Description"
			,EXISTS (
				SELECT 1
				FROM "SysLookup"
				WHERE "SysEntitySchemaUId" = BaseSchema."UId"
			) "IsLookup"
			,EXISTS (
				SELECT 1
				FROM "SysModule" sm
				INNER JOIN "SysModuleEntity" sme ON sme."Id" = sm."SysModuleEntityId"
				WHERE BaseSchema."UId" = sme."SysEntitySchemaUId"
			) "IsModule"
			,SysSchemaAdministrationProperties."AdministratedByOperations"::BOOLEAN
			,SysSchemaAdministrationProperties."AdministratedByColumns"::BOOLEAN
			,SysSchemaAdministrationProperties."AdministratedByRecords"::BOOLEAN
			,SysSchemaAdministrationProperties."IsTrackChangesInDB"::BOOLEAN
			,"SysWorkspaceId"
			,BaseSchema."ProcessListeners"
			,EXISTS (
				SELECT 1
				FROM "SysSSPEntitySchemaAccessList"
				WHERE "EntitySchemaUId" = BaseSchema."UId"
			) "IsInSSPEntitySchemaAccessList"
			FROM "SysSchema" BaseSchema
			INNER JOIN "VwSysSchemaExtending" ON BaseSchema."Id" = "VwSysSchemaExtending"."BaseSchemaId"
			INNER JOIN "SysPackage" on BaseSchema."SysPackageId" = "SysPackage"."Id"
			INNER JOIN SysSchemaAdministrationProperties ON BaseSchema."Id" = SysSchemaAdministrationProperties."SysSchemaId";
			
			CREATE RULE RU_VwAdministrativeObjects AS
			ON UPDATE TO "VwAdministrativeObjects"
			DO INSTEAD NOTHING;

Пример 3 (хранимые процедуры)

Пример. Хранимая процедура, использующая циклы, курсоры и транзакции.

Microsoft SQL Server

IF EXISTS(SELECT * FROM sys.procedures WHERE object_id =
OBJECT_ID(N'[dbo].[tsp_ActualizeAdminUnitInRole]'))
DROP PROCEDURE [dbo].[tsp_ActualizeAdminUnitInRole]
GO
CREATE PROCEDURE [dbo].[tsp_ActualizeAdminUnitInRole]
AS
BEGIN
	SET NOCOUNT ON;
	IF OBJECT_ID('tempdb..#AdminUnitListTemp') IS NOT NULL
	BEGIN
		DROP TABLE [#AdminUnitListTemp];
	END;
	CREATE TABLE [#AdminUnitListTemp]
	(
		[UserId] uniqueidentifier NOT NULL,
		[Id] uniqueidentifier NOT NULL,
		[Name] NVARCHAR(250) NOT NULL,
		[ParentRoleId] uniqueidentifier NULL,
		[Granted] BIT NULL
	);
	
	DECLARE @SysAdminUnitId uniqueidentifier;
	DECLARE @getUserAdminUnits CURSOR;
	DECLARE @SysAdminUnitRoles TABLE ([Id] uniqueidentifier,
		[Name] nvarchar(260),
	[ParentRoleId] uniqueidentifier);
	
	SET @getUserAdminUnits = CURSOR FOR
	SELECT [Id]
	FROM [dbo].[SysAdminUnit]
	WHERE [SysAdminUnitTypeValue] = 4;
	
	OPEN @getUserAdminUnits
	FETCH NEXT
	FROM @getUserAdminUnits INTO @SysAdminUnitId
	WHILE @@FETCH_STATUS = 0
	BEGIN
		DELETE FROM @SysAdminUnitRoles;
		
		INSERT INTO @SysAdminUnitRoles
		EXEC [tsp_GetAdminUnitList] @UserId=@SysAdminUnitId;
		
		BEGIN TRAN;
			DELETE FROM [dbo].[SysAdminUnitInRole] WHERE SysAdminUnitId = @SysAdminUnitId;
			
			INSERT INTO [dbo].[SysAdminUnitInRole] (SysAdminUnitId, SysAdminUnitRoleId)
			SELECT @SysAdminUnitId, [Id] FROM @SysAdminUnitRoles;
			COMMIT;
			
			FETCH NEXT
			FROM @getUserAdminUnits INTO @SysAdminUnitId;
		END;
		CLOSE @getUserAdminUnits;
		DEALLOCATE @getUserAdminUnits;
		
		IF OBJECT_ID('tempdb..#AdminUnitListTemp') IS NOT NULL
		BEGIN
			DROP TABLE [#AdminUnitListTemp];
		END;
		END

PostgreSQL

CREATE OR REPLACE FUNCTION public."tsp_ActualizeAdminUnitInRole"()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
getUserAdminUnits CURSOR
FOR SELECT
"Id"
FROM "SysAdminUnit"
WHERE "SysAdminUnitTypeValue" = 4;
SysAdminUnitId UUID;
BEGIN
	set jit = false;
	DROP TABLE IF EXISTS "#AdminUnitListTemp";
	DROP TABLE IF EXISTS "SysAdminUnitRolesTemp";
	CREATE TEMP TABLE "#AdminUnitListTemp"(
		"UserId" UUID NOT NULL,
		"Id" UUID NOT NULL,
		"Name" VARCHAR(250) NOT NULL,
		"ParentRoleId" UUID NULL,
		"Granted" BOOLEAN NULL
	);
	CREATE TEMP TABLE "SysAdminUnitRolesTemp"(
		"Id" UUID,
		"Name" VARCHAR(260),
		"ParentRoleId" UUID
	);
	FOR SysAdminUnitRecord IN getUserAdminUnits LOOP
		EXIT WHEN SysAdminUnitRecord = NULL;
		SysAdminUnitId = SysAdminUnitRecord."Id";
		DELETE FROM "SysAdminUnitRolesTemp";
		INSERT INTO "SysAdminUnitRolesTemp"
		SELECT * FROM "tsp_GetAdminUnitList"(SysAdminUnitId);
		DELETE FROM "SysAdminUnitInRole"
		WHERE "SysAdminUnitId" = SysAdminUnitId;
		INSERT INTO "SysAdminUnitInRole" (
			"SysAdminUnitId",
			"SysAdminUnitRoleId"
		) SELECT
		SysAdminUnitId,
		"Id"
		FROM "SysAdminUnitRolesTemp";
	END LOOP;
	DROP TABLE IF EXISTS "#AdminUnitListTemp";
	DROP TABLE IF EXISTS "SysAdminUnitRolesTemp";
END;
$function$

Пример 4 (хранимые процедуры)

Пример. Хранимая процедура, использующая циклы и кастомные запросы.

Microsoft SQL Server

IF EXISTS(SELECT * FROM sys.procedures WHERE object_id =
OBJECT_ID(N'[dbo].[tsp_CompletenessRenew]'))
DROP PROCEDURE [dbo].[tsp_CompletenessRenew]
GO
CREATE PROCEDURE [dbo].[tsp_CompletenessRenew]
@CompletenessId uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE
	@SchemaTableName varchar(MAX),
	@SchemaTypeColumn varchar(MAX),
	@SchemaTypeValue uniqueidentifier,
	@SchemaResultColumn varchar(MAX),
	@Id uniqueidentifier,
	@ColumnName varchar(MAX),
	@DetailName varchar(MAX),
	@IsColumn bit,
	@IsDetail bit,
	@DetailColumn varchar(MAX),
	@MasterColumn varchar(MAX),
	@TypeColumn varchar(MAX),
	@TypeValue uniqueidentifier,
	@Percentage varchar(MAX),
	@MainSelect nvarchar(MAX),
	@AlterTableSql nvarchar(max),
	@OldCompleteness int,
	@NewCompleteness int,
	@vsql nvarchar(max),
	@records CURSOR,
	@UpdateSql nvarchar(max),
	@InsertIntoTempTable nvarchar(MAX),
	@SelectNeededData nvarchar(MAX),
	@AttributesCount int,
	@DetailSelectColumnName nvarchar(MAX);
	
	SELECT @SchemaTableName = cmn.EntitySchemaName, @SchemaTypeColumn = cmn.TypeColumnName,
	@SchemaTypeValue = cmn.TypeColumnValue, @SchemaResultColumn = cmn.ResultColumnName FROM Completeness cmn WITH (NOLOCK)
	WHERE Id = @CompletenessId;
	
	SET @MainSelect = 'SELECT Id, Completeness, SUM(';
	SET @InsertIntoTempTable = 'INSERT INTO #tempTable (Id, Completeness, ';
	SET @SelectNeededData = 'SELECT Id, [' + @SchemaResultColumn + '], ';
	SET @AttributesCount = 0;
	
	Create table #tempTable(
		Id uniqueidentifier,
		Completeness int,
	NewCompleteness int);
	
	DECLARE Attributes CURSOR FOR
	SELECT
	cp.Id,
	cp.ColumnName,
	cp.DetailEntityName,
	cp.IsColumn,
	cp.IsDetail,
	cp.DetailColumn,
	cp.MasterColumn,
	cp.Percentage,
	cp.TypeColumn,
	cp.TypeValue
	FROM CompletenessParameter cp WITH (NOLOCK)
	WHERE cp.CompletenessId = @CompletenessId;
	
	OPEN Attributes;
	FETCH NEXT FROM Attributes
	INTO @Id, @ColumnName, @DetailName, @IsColumn, @IsDetail,
	@DetailColumn,@MasterColumn, @Percentage, @TypeColumn, @TypeValue;
	WHILE @@FETCH_STATUS=0
	BEGIN
		SET @AttributesCount += 1;
		IF @IsColumn = 1
		BEGIN
			SET @AlterTableSql = 'ALTER TABLE #tempTable ADD ' + @ColumnName + ' NVARCHAR(MAX)';
			EXEC sp_executesql @AlterTableSql;
			SET @InsertIntoTempTable += @ColumnName + ',';
			SET @SelectNeededData += @ColumnName + ',';
			SET @MainSelect += 'CASE WHEN (' + @ColumnName + ' IS NULL) OR (' + @ColumnName + ' = '''')';
			SET @MainSelect += ' OR (ISNUMERIC(' + @ColumnName + ') = 1 AND ' + @ColumnName + ' NOT LIKE ''%[^0-9]%''';
			SET @MainSelect += ' AND CONVERT(float, ' + @ColumnName + ') = 0)';
			SET @MainSelect += ' THEN 0 ELSE ' + @Percentage + ' END + ';
		END
		ELSE
		BEGIN
			SET @DetailSelectColumnName = @DetailName;
			IF (@TypeColumn IS NOT NULL) AND (@TypeValue IS NOT NULL)
			BEGIN
				SET @DetailSelectColumnName = @DetailName + '_' + CONVERT(varchar(5), @AttributesCount);
			END
			SET @AlterTableSql = 'ALTER TABLE #tempTable ADD ' + @DetailSelectColumnName + ' NVARCHAR(MAX)';
			EXEC sp_executesql @AlterTableSql;
			SET @InsertIntoTempTable += @DetailSelectColumnName + ',';
			SET @SelectNeededData += '(SELECT COUNT(*) FROM ' + @DetailName + ' WHERE ' + @DetailColumn +' = a.'+@MasterColumn
			IF (@TypeColumn IS NOT NULL) AND (@TypeValue IS NOT NULL)
			BEGIN
				SET @SelectNeededData += ' AND ';
				SET @SelectNeededData += @TypeColumn;
				SET @SelectNeededData += ' IN (';
				SET @SelectNeededData += '''' + CONVERT(varchar(38), @TypeValue) + '''';
				SET @SelectNeededData += ')';
			END;
			SET @SelectNeededData += ')';
			SET @SelectNeededData += ' AS ' + @DetailSelectColumnName;
			SET @SelectNeededData += ',';
			SET @MainSelect += 'CASE WHEN ' + @DetailSelectColumnName + ' = 0 THEN 0 ELSE ' + @Percentage + ' END + ';
		END
		FETCH NEXT FROM Attributes
		INTO @Id, @ColumnName, @DetailName, @IsColumn, @IsDetail,
		@DetailColumn,@MasterColumn, @Percentage, @TypeColumn, @TypeValue;
	END;
	CLOSE Attributes;
	DEALLOCATE Attributes;
	
	IF @AttributesCount = 0
	BEGIN
		SET @MainSelect = 'SELECT Id, [' + @SchemaResultColumn + '], (0+';
		SET @InsertIntoTempTable = 'INSERT INTO #tempTable (Id, Completeness+';
		SET @SelectNeededData = 'SELECT Id, Completeness+';
	END;
	SET @MainSelect = LEFT(@MainSelect, LEN(@MainSelect) - 1);
	SET @MainSelect += ' ) AS NewCompleteness';
	SET @MainSelect += ' FROM #tempTable WITH (NOLOCK)';
	IF (@SchemaTypeColumn <> '') AND (@SchemaTypeValue IS NOT NULL)
	BEGIN
		SET @MainSelect += ' WHERE [' + @SchemaTypeColumn + '] = ''' + CONVERT(varchar(38), @SchemaTypeValue) + '''';
	END
	SET @MainSelect += ' GROUP BY Id, Completeness';
	SET @MainSelect += ';';
	
	SET @InsertIntoTempTable = LEFT(@InsertIntoTempTable, LEN(@InsertIntoTempTable) - 1);
	SET @SelectNeededData = LEFT(@SelectNeededData, LEN(@SelectNeededData) - 1);
	
	SET @InsertIntoTempTable += ') ' + @SelectNeededData + ' FROM ['+ @SchemaTableName + '] a WITH (NOLOCK)';
	
	EXEC sp_executesql @InsertIntoTempTable;
	
	SET @vsql = 'SET @cursor = CURSOR FORWARD_ONLY STATIC FOR ' + @MainSelect + ' OPEN @cursor;'
	
	Create table #NewCompletenessRecords(
		Id uniqueidentifier,
	Completeness int);
	
	EXEC sys.sp_executesql
	@vsql
	,N'@cursor cursor output'
	,@records output
	
	FETCH NEXT FROM @records INTO @id, @OldCompleteness, @NewCompleteness
	WHILE (@@fetch_status = 0)
	BEGIN
		IF @OldCompleteness <> @NewCompleteness
		BEGIN
			INSERT INTO #NewCompletenessRecords(Id, Completeness) VALUES (@id, @NewCompleteness);
		END;
		FETCH NEXT FROM @records INTO @id, @OldCompleteness, @NewCompleteness;
	END;
	
	CLOSE @records
	DEALLOCATE @records
	SET @UpdateSql = 'UPDATE [' + @SchemaTableName + '] SET [' + @SchemaResultColumn + '] = b.Completeness';
	SET @UpdateSql += ' FROM #NewCompletenessRecords as b';
	SET @UpdateSql += ' WHERE [' + @SchemaTableName + '].Id = b.Id';
	EXEC sp_executesql @UpdateSql;
	
	DROP TABLE #NewCompletenessRecords;
DROP TABLE #tempTable;
END

PostgreSQL

CREATE OR REPLACE FUNCTION public."tsp_CompletenessRenew"(completenessid uuid, batchlimit integer DEFAULT 500)
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
schemaName VARCHAR(500);
resultColumnName VARCHAR(500);
columnValues TEXT;
allRecordsCount BIGINT;
currentStep BIGINT = 0;
sqlCmd TEXT;
BEGIN
	IF NOT EXISTS(SELECT NULL FROM "Completeness" WHERE "Id" = completenessId) THEN
		RETURN;
	END IF;
	
	SELECT "ResultColumnName", "EntitySchemaName"
	INTO resultColumnName, schemaName
	FROM "Completeness"
	WHERE "Id" = completenessId;
	
	IF NOT EXISTS(SELECT NULL FROM "CompletenessParameter" WHERE "CompletenessId" = completenessId) THEN
		sqlCmd = FORMAT(N'UPDATE %I SET %I = 0', schemaName, resultColumnName);
		EXECUTE sqlCmd;
		RETURN;
	END IF;
	
	DROP TABLE IF EXISTS tempTable;
	CREATE TEMP TABLE tempTable(
		"Id" UUID,
		"NewCompleteness" INTEGER
	);
	
	columnValues = (
		SELECT STRING_AGG(tmp.command, '+')
		FROM (
			SELECT FORMAT(N'CASE WHEN "fn_IsNumeric"(%1$I::TEXT) THEN CASE WHEN %1$I::TEXT::DOUBLE PRECISION = 0 THEN 0 ELSE 1 END ELSE LENGTH(LEFT(CONCAT(%1$I), 1)) END * %2$s',
				"ColumnName",
				"Percentage"
			) AS command
			FROM "CompletenessParameter"
			WHERE "CompletenessId" = completenessId
			AND "IsColumn" = TRUE
			AND "ColumnName" IS NOT NULL
			UNION ALL
			SELECT FORMAT(N'(SELECT COUNT("Id") FROM %1$I %2$s %3$s) * %4$s',
				"DetailEntityName",
				CASE WHEN (param."MasterColumn" IS NOT NULL) AND (param."DetailColumn" IS NOT NULL) THEN
					FORMAT(N'WHERE %1$I = a.%2$I', param."DetailColumn", param."MasterColumn")
				ELSE '' END,
				CASE WHEN (param."TypeColumn" IS NOT NULL) AND (param."TypeValue" IS NOT NULL) THEN
					FORMAT(N' AND %1$I IN (%2$L::UUID)', param."TypeColumn", param."TypeValue")
				ELSE '' END,
			"Percentage" ) AS command
			FROM "CompletenessParameter" param
			WHERE "CompletenessId" = completenessId
			AND "IsDetail" = TRUE
		AND "DetailEntityName" IS NOT NULL) AS tmp):: TEXT;
		
		sqlCmd = FORMAT(N'INSERT INTO tempTable("Id","NewCompleteness")
			SELECT "Id", SUM (%s) AS NewCompleteness
			FROM %I AS a
		GROUP BY "Id"', columnValues, schemaName);
		
		EXECUTE sqlCmd;
		
		SELECT COUNT("Id") INTO allRecordsCount FROM tempTable;
		LOOP
			EXIT WHEN currentStep > allRecordsCount;
			sqlCmd = FORMAT('UPDATE %1$I
				SET %2$I = t."NewCompleteness"
				FROM (SELECT "Id", "NewCompleteness" FROM temptable ORDER BY "Id" LIMIT %3$s OFFSET %4$s) t
			WHERE t."Id" = %1$I."Id" AND t."NewCompleteness" <> %1$I.%2$I', schemaName, resultColumnName, batchLimit, currentStep);
			EXECUTE sqlCmd;
			currentStep = currentStep + batchLimit;
		END LOOP;
END;
$function$

Пример 5 (хранимые процедуры)

Пример. Хранимая процедура, использующая транзакции, обработку ошибок и кастомные запросы.

Microsoft SQL Server

IF EXISTS(SELECT * FROM sys.procedures WHERE object_id =
OBJECT_ID(N'[dbo].[tsp_RemoveUnusedReferences]'))
DROP PROCEDURE [dbo].[tsp_RemoveUnusedReferences]
GO
CREATE PROCEDURE [dbo].[tsp_RemoveUnusedReferences]
AS
BEGIN
	IF OBJECT_ID('tempdb..#TranslationParsedKeys', 'U') IS NOT NULL
	BEGIN
		DROP TABLE #TranslationParsedKeys;
	END
	
	-- prepare config scripts
	
	DECLARE @ResourceKeysByType TABLE (
		[Id] UNIQUEIDENTIFIER PRIMARY KEY DEFAULT (NEWID()) NOT NULL,
		[ResourceType] NVARCHAR(50) NOT NULL,
		[ResourceKey] NVARCHAR(500) NOT NULL,
		[TranslationId] UNIQUEIDENTIFIER NOT NULL
	);
	
	DECLARE @ResourceKeysBySchemaName TABLE (
		[SchemaName] NVARCHAR(250) NULL,
		[KeyValue] NVARCHAR(500) NOT NULL,
		[ResourceKeyId] UNIQUEIDENTIFIER NOT NULL
	);
	
	DECLARE @UsedSchemas TABLE (
		[Id] UNIQUEIDENTIFIER NOT NULL,
		[Name] NVARCHAR(250) NOT NULL
	);
	
	DECLARE @TableNames TABLE (
		[TableName] NVARCHAR(250) NOT NULL
	);
	
	CREATE TABLE #TranslationParsedKeys (
		[TranslationId] UNIQUEIDENTIFIER NOT NULL,
		[TableName] NVARCHAR(250) NOT NULL,
		[RecordId] NVARCHAR(50) NOT NULL
	);
	
	-- prepare data for analisys
	
	SET NOCOUNT ON;
	
	INSERT INTO @ResourceKeysByType ([ResourceType], [ResourceKey], [TranslationId])
	SELECT
	SUBSTRING(st.[Key], 0, CHARINDEX(':', st.[Key])) AS [ResourceType],
	SUBSTRING(st.[Key], CHARINDEX(':', st.[Key]) + 1, LEN(st.[Key]) - CHARINDEX(':', st.[Key])) AS [ResourceKey],
	st.[Id] AS [TranslationId]
	FROM [SysTranslation] AS st;
	
	INSERT INTO @ResourceKeysBySchemaName ([SchemaName], [KeyValue], [ResourceKeyId])
	SELECT
	CASE rkbt.ResourceType
		WHEN 'Data' THEN PARSENAME(REPLACE(rkbt.[ResourceKey], ':', '.'), 3)
		WHEN 'Configuration' THEN SUBSTRING(rkbt.[ResourceKey], 0, CHARINDEX(':', rkbt.[ResourceKey]))
			ELSE
			NULL
		END,
		SUBSTRING(rkbt.[ResourceKey], CHARINDEX(':', rkbt.[ResourceKey]) + 1, LEN(rkbt.[ResourceKey]) - CHARINDEX(':', rkbt.[ResourceKey])),
		[Id]
		FROM @ResourceKeysByType rkbt;
		
		INSERT INTO @UsedSchemas
		SELECT
		ss.[Id],
		ss.[Name]
		FROM [SysSchema] ss
		JOIN (
			SELECT DISTINCT rkbsn.[SchemaName]
			FROM @ResourceKeysBySchemaName rkbsn
		) AS r1 ON r1.[SchemaName] = ss.[Name]
		ORDER BY ss.[Name] ASC;
		
		BEGIN TRANSACTION;
			BEGIN TRY
				DELETE st
				FROM [SysTranslation] st
				WHERE EXISTS (
					SELECT 1
					FROM @ResourceKeysByType rkbt1
					JOIN @ResourceKeysBySchemaName rksn1 ON rksn1.[ResourceKeyId] = rkbt1.[Id]
					WHERE NOT EXISTS (
						SELECT 1
						FROM [SysLocalizableValue] slv
						JOIN @UsedSchemas ss ON ss.[Id] = slv.[SysSchemaId]
						WHERE ss.[Name] = rksn1.[SchemaName]
						AND slv.[Key] = rksn1.[KeyValue]
					)
					AND rkbt1.[ResourceType] = 'Configuration'
					AND rkbt1.[TranslationId] = st.[Id]
				);
				
				COMMIT TRANSACTION;
			END TRY
			BEGIN CATCH
				ROLLBACK TRANSACTION;
			END CATCH
			
			-- prepare data scripts
			
			DECLARE @sqlText NVARCHAR(MAX) = N'',
			@queryTpl NVARCHAR(MAX) = N'BEGIN TRANSACTION;
			BEGIN TRY
			IF (EXISTS (
			SELECT *
			FROM INFORMATION_SCHEMA.TABLES
			WHERE TABLE_SCHEMA = ''dbo''
			AND TABLE_NAME = ''{TableName}''))
			BEGIN
			DELETE st FROM [dbo].[SysTranslation] st
			JOIN #TranslationParsedKeys tpk WITH (NOLOCK) ON tpk.[TranslationId] = st.[Id]
			AND tpk.[TableName] = ''{TableName}''
			LEFT OUTER JOIN [{TableName}] t1 WITH (NOLOCK) ON t1.[Id] = tpk.[RecordId]
			WHERE t1.[Id] IS NULL;
			END
			ELSE
			BEGIN
			DELETE st FROM [dbo].[SysTranslation] st
			WHERE st.[Key] LIKE ''%{TableName}%''
			END
			
			COMMIT TRANSACTION;
			END TRY
			BEGIN CATCH
			ROLLBACK TRANSACTION;
			END CATCH;' + CHAR(13);
			
			INSERT INTO #TranslationParsedKeys ([TranslationId], [TableName], [RecordId])
			SELECT
			st.[Id],
			PARSENAME(REPLACE(st.[Key], ':', '.'), 3),
			PARSENAME(REPLACE(st.[Key], ':', '.'), 1)
			FROM SysTranslation AS st
			WHERE st.[Key] LIKE N'Data:%';
			
			INSERT INTO @TableNames
			SELECT DISTINCT tpk.[TableName]
			FROM #TranslationParsedKeys tpk
			ORDER BY tpk.[TableName] ASC;
			
			SELECT
			@sqlText += REPLACE(@queryTpl, '{TableName}', tn.[TableName])
			FROM @TableNames tn;
			
			EXEC sp_executesql @sqlText;
			
			SET NOCOUNT OFF;
			
		DROP TABLE #TranslationParsedKeys;
		END

PostgreSQL

CREATE OR REPLACE FUNCTION public."tsp_RemoveUnusedReferences"()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
queryTpl TEXT;
tableName VARCHAR(250);
BEGIN
	PERFORM "tsp_DropAndCreateTempTables"();
	
	INSERT INTO "TranslationParsedKeys"
	SELECT
	st."Id",
	"Regexp_Substr"(REPLACE(st."Key", ':', '.'), '[^.]+', 1, 2),
	"Regexp_Substr"(REPLACE(st."Key", ':', '.'), '[^.]+', 1, 4)::UUID
	FROM "SysTranslation" st
	WHERE st."Key" LIKE 'Data:%';
	
	INSERT INTO "TableNames"
	SELECT DISTINCT tpk."TableName"
	FROM "TranslationParsedKeys" tpk
	ORDER BY tpk."TableName" ASC;
	
	queryTpl = E'DO $queryTpl$
	BEGIN
	IF (SELECT EXISTS (
	SELECT 1
	FROM pg_catalog.pg_class c
	JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
	WHERE n.nspname = ''public''
	AND c.relname = ''{TABLE_NAME}''
	AND c.relkind = ''r''
	)
	) THEN
	DELETE FROM "SysTranslation" st
	WHERE st."Id" IN (
	SELECT tpk."TranslationId"
	FROM "TranslationParsedKeys" tpk
	LEFT OUTER JOIN "{TABLE_NAME}" t1 ON t1."Id" = tpk."RecordId"
	WHERE t1."Id" IS NULL AND tpk."TableName" = ''{TABLE_NAME}''
	);
	ELSE
	DELETE FROM "SysTranslation" st
	WHERE st."Key" LIKE ''%{TABLE_NAME}%'';
	END IF;
	EXCEPTION WHEN OTHERS THEN
	ROLLBACK;
	END;
	$queryTpl$ LANGUAGE PLPGSQL;';
	
	FOR tableName IN (SELECT tn."TableName" FROM "TableNames" tn)
	LOOP
		EXECUTE REPLACE(queryTpl, '{TABLE_NAME}', tableName);
	END LOOP;
	
	INSERT INTO "ResourceKeysByType"
	SELECT
	UUID_GENERATE_V4(),
	SUBSTRING(st."Key", 0, STRPOS(st."Key", ':')),
	SUBSTRING(st."Key", STRPOS(st."Key", ':') + 1, LENGTH(st."Key") - STRPOS(st."Key", ':')),
	st."Id"
	FROM "SysTranslation" st;
	INSERT INTO "ResourceKeysBySchemaName"
	SELECT
	CASE rkbt."ResourceType"
		WHEN 'Data' THEN SUBSTRING(rkbt."ResourceKey", 0, STRPOS(rkbt."ResourceKey", '.'))
		WHEN 'Configuration' THEN SUBSTRING(rkbt."ResourceKey", 0, STRPOS(rkbt."ResourceKey", ':'))
		END,
		SUBSTRING(rkbt."ResourceKey", STRPOS(rkbt."ResourceKey", ':') + 1, LENGTH(rkbt."ResourceKey") - STRPOS(':', rkbt."ResourceKey")),
		rkbt."Id"
		FROM "ResourceKeysByType" rkbt;
		
		INSERT INTO "UsedSchemas"
		SELECT
		ss."Id",
		ss."Name"
		FROM "SysSchema" ss
		JOIN (
			SELECT DISTINCT rkbsn."SchemaName"
			FROM "ResourceKeysBySchemaName" rkbsn
		) r1 ON r1."SchemaName" = ss."Name"
		ORDER BY ss."Name" ASC;
		
		DELETE FROM "SysTranslation" st
		WHERE EXISTS (
			SELECT 1
			FROM "ResourceKeysByType" rkbt1
			JOIN "ResourceKeysBySchemaName" rkbsn1 ON rkbsn1."ResourceKeyId" = rkbt1."Id"
			WHERE NOT EXISTS (
				SELECT 1
				FROM "SysLocalizableValue" slv
				JOIN "UsedSchemas" ss ON ss."Id" = slv."SysSchemaId"
				WHERE ss."Name" = rkbsn1."SchemaName"
				AND slv."Key" = rkbsn1."KeyValue"
			)
			AND rkbt1."ResourceType" = 'Configuration'
			AND rkbt1."TranslationId" = st."Id"
		);
		EXCEPTION WHEN OTHERS THEN
		ROLLBACK;
END;
$function$

Пример 6 (функции)

Пример. Создание функции с помощью SQL-скрипта.

Microsoft SQL Server

DROP FUNCTION IF EXISTS [dbo].[fn_GetIsPhoneCommunicationType]
GO
CREATE FUNCTION [dbo].[fn_GetIsPhoneCommunicationType]
(
	@communicationTypeId UNIQUEIDENTIFIER
)
RETURNS BIT
AS
BEGIN
	DECLARE @isPhone BIT = 0;
	IF (EXISTS (
		SELECT 1 FROM [ComTypebyCommunication] [comType]
		JOIN [Communication] [com] on [com].[Id] = [comType].[CommunicationId]
		WHERE [com].[Code] = 'Phone' and [comType].[CommunicationTypeId] = @communicationTypeId
	)
	)
	SET @isPhone = 1;
RETURN @isPhone;
END

PostgreSQL

CREATE OR REPLACE FUNCTION public."fn_GetIsPhoneCommunicationType"(communicationtypeid uuid)
RETURNS boolean
LANGUAGE plpgsql
AS $function$
BEGIN
	RETURN EXISTS(
		SELECT
		1
		FROM "ComTypebyCommunication" comType
		INNER JOIN "Communication" com
		ON com."Id" = comType."CommunicationId"
		WHERE
		com."Code" = 'Phone' and
		comType."CommunicationTypeId" = communicationTypeId
	);
END;
$function$

Полезные ссылки

Документация PostgreSQL
Документация PostgreSQL (приведение типов)
Документация PostgreSQL (quote_ident)
Документация PostgreSQL (quote_literal)
Документация PostgreSQL (format)
Курс от PostgresPro. Разработка серверной части приложений PostgreSQL. Базовый курс

Материал был полезен для вас?

Спасибо за оценку!

AI и ML инструменты

Наши контакты

Наши медиа

BPMSoft признана лидером рынка CRM-систем 2026

Работа с базой данных

Общие рекомендации для работы в PostgreSQL

Соответствие типов данных

Примеры скриптов для Microsoft SQL Server и PostgreSQL

Пример 1 (представления)

Microsoft SQL Server

PostgreSQL

Пример 2 (представления)

Microsoft SQL Server

PostgreSQL

Пример 3 (хранимые процедуры)

Microsoft SQL Server

PostgreSQL

Пример 4 (хранимые процедуры)

Microsoft SQL Server

PostgreSQL

Пример 5 (хранимые процедуры)

Microsoft SQL Server

PostgreSQL

Пример 6 (функции)

Microsoft SQL Server

PostgreSQL

Рекомендуем изучить

Полезные ссылки