перепиши запрос с ms sql на postgreSql

declare @uniqueValueValidatorTypeID uniqueidentifier = '9cd89f16-8482-4382-943b-1db0469481ae'
declare @fieldValidatorTypeID uniqueidentifier = '9B24AB01-5F46-05D1-BDFA-615212761DD8'
declare @sectionValidatorTypeID uniqueidentifier = 'D57EF1A1-CFCD-0852-8A2C-CE527BC02AAC'
declare @taskOptionsValidators table (TaskTypeID uniqueidentifier, OptionID uniqueidentifier, SectionID uniqueidentifier, ColumnID uniqueidentifier, ValidatorTypeID uniqueidentifier)
declare @settingsCardID uniqueidentifier = (select top 1 ID from Instances (nolock) where TypeID = 'bc127923-1a2f-41fd-a295-a75689853109')

if (@settingsCardID is null)
begin
SELECT N'Сначала создайте карточку настроек тестирования процессов'
return
end

declare @defaultTypesValues table(TypeName nvarchar(100), TypeNameJson nvarchar(100), NeedQuotes bit, DefaultValue nvarchar(1000))
insert into @defaultTypesValues(TypeName, TypeNameJson, NeedQuotes, DefaultValue)
values
('String', '', 1, N'тестовый текст'),
('Int', '::int', 0, '1'),
('Double', '', 0, '1.5'),
('Decimal', '::dcm', 1, '1.5'),
('Boolean', '', 0, 'true'),
('Datetime', '::dtm', 1, FORMAT(GETUTCDATE()+365, 'yyyy-MM-ddTHH:mm:ssZ')),
('Date', '::dtm', 1, FORMAT(GETUTCDATE()+365, 'yyyy-MM-ddT00:00:00Z')),
('Guid', '::uid', 1, '00000000-0000-0000-0000-000000000000')

declare @defaultComplexColumnsValues table(ReferencedColumn uniqueidentifier, NeedQuotes bit, DefaultValue nvarchar(1000))
insert into @defaultComplexColumnsValues(ReferencedColumn, NeedQuotes, DefaultValue)
values
--Roles
('81f6010b-9641-01a5-4000-08e8603fbf4b', 1, '3DB19FA0-228A-497F-873A-0250BF0A4CCB'),
('616d6b2e-35d5-424d-846b-618eb25962d0', 1, 'Admin'),
--PersonalRoles
('6c977939-bbfc-016f-4000-01c2244e3cc3', 1, '3DB19FA0-228A-497F-873A-0250BF0A4CCB'),
('1782f76a-4743-4aa4-920c-7edaee860964', 1, 'Admin')

declare @taskMetadata nvarchar(max)
declare @taskTypeID uniqueidentifier
declare cur cursor local for select [ID], [Metadata] from [Types] (nolock) where InstanceTypeID = 2
open cur
fetch next from cur into @taskTypeID, @taskMetadata
while @@fetch_status = 0
begin
insert into @taskOptionsValidators (TaskTypeID, OptionID, SectionID, ColumnID, ValidatorTypeID)
select 
@taskTypeID, CompletionOptionID, SectionID, ColumnID, TypeID
from openjson(@taskMetadata, '$.Validators')
with (
   ValidatorSettings nvarchar(max) '$.ValidatorSettings' as JSON,
   TypeID uniqueidentifier '$."TypeID::uid"'
)
outer apply openjson(ValidatorSettings)
with (
	SectionID uniqueidentifier '$."SectionID::uid"',
	ColumnID uniqueidentifier '$."ColumnID::uid"'	
) as vs
cross apply openjson(@taskMetadata, '$.CompletionOptions')
with (
	CompletionOptionID uniqueidentifier '$."TypeID::uid"'
)
where TypeID <> @uniqueValueValidatorTypeID
UNION
select 
@taskTypeID, CompletionOptionID, SectionID, ColumnID, TypeID
from openjson(@taskMetadata, '$.CompletionOptions') 
with (
	CompletionOptionID uniqueidentifier '$."TypeID::uid"',
	Validators nvarchar(max) '$.Validators' as JSON
)
outer apply openjson(Validators)
with (
   ValidatorSettings nvarchar(max) '$.ValidatorSettings' as JSON,
   TypeID uniqueidentifier '$."TypeID::uid"'
)
outer apply openjson(ValidatorSettings)
with (
	SectionID uniqueidentifier '$."SectionID::uid"',
	ColumnID uniqueidentifier '$."ColumnID::uid"'	
) as vs
where TypeID <> @uniqueValueValidatorTypeID

fetch next from cur into @taskTypeID, @taskMetadata
end
close cur
deallocate cur

declare @sectionsJson table (OptionID uniqueidentifier, EntriesSections nvarchar(max), CollectionsSections nvarchar(max))

declare @DistinctValidatorsColumns table(
OptionID uniqueidentifier,
SectionID uniqueidentifier,
SectionName nvarchar(1000),
ColumnID uniqueidentifier,
ColumnName nvarchar(1000),
ColumnValue nvarchar(4000),
ValidatorTypeID uniqueidentifier
)

insert into @DistinctValidatorsColumns (OptionID,SectionID,SectionName,ColumnID,ColumnName,ColumnValue,ValidatorTypeID)
select
vdt.OptionID,
vdt.SectionID,
ts.Name as SectionName,
vdt.ColumnID,
(columnInfo.PhysicalColumnName + dtv.TypeNameJson) as ColumnName,
(case when dtv.NeedQuotes = 1 then '"'+ dtv.defaultValue+'"' else dtv.defaultValue end) as ColumnValue,
vdt.ValidatorTypeID
from @taskOptionsValidators vdt
inner join [Tables] (NOLOCK) ts
on ts.ID = SectionID
outer apply (
select top 1
cast(physColumn.value('@Name', 'nvarchar(max)') as nvarchar(max)) as PhysicalColumnName,
cast(physColumn.value('@Type', 'nvarchar(max)') as nvarchar(max)) as PhysicalColumnType
from (
select [Definition]
from Tables t2 with(nolock)
where t2.ID = vdt.SectionID -- ID секции
) as xmldata (x)
cross apply x.nodes('(/SchemeTable/SchemePhysicalColumn)') xmlPhysicalColumn(physColumn)
where physColumn.value('@ID', 'nvarchar(36)') = vdt.ColumnID -- ID колонки SchemePhysicalColumn
) as columnInfo
outer apply (
select top 1 *
from @defaultTypesValues
where columnInfo.PhysicalColumnType like N''+ TypeName+'%'
) as dtv
where vdt.ValidatorTypeID = @fieldValidatorTypeID
and columnInfo.PhysicalColumnName is not null

UNION
select
vdt.OptionID,
vdt.SectionID,
ts.Name as SectionName,
vdt.ColumnID,
(columnInfo.PhysicalColumnName + dtypes.TypeNameJson) as ColumnName,
(case when dvalues.NeedQuotes = 1 then '"'+ dvalues.DefaultValue+'"' else dvalues.DefaultValue end),
vdt.ValidatorTypeID
from @taskOptionsValidators vdt
inner join [Tables] (NOLOCK) ts
on ts.ID = SectionID
outer apply (
select
cast(complexColumn.value('@Name', 'nvarchar(max)') as nvarchar(max)) as ComplexColumnName,
cast(complexColumn.value('@ReferencedTable', 'nvarchar(36)') as uniqueidentifier) as ComplexColumnReferencedTableID,
cast(refColumn.value('@Name', 'nvarchar(max)') as nvarchar(max)) as PhysicalColumnName,
cast(refColumn.value('@Type', 'nvarchar(max)') as nvarchar(max)) as PhysicalColumnType,
cast(refColumn.value('@ReferencedColumn', 'nvarchar(36)') as uniqueidentifier) as PhysicalColumnReferencedColumn
from (
select [Definition]
from Tables t with(nolock)
where t.ID = vdt.SectionID -- ID секции
) as xmldata(x)
cross apply x.nodes('(/SchemeTable/SchemeComplexColumn)') as xmlComplexColumn(complexColumn)
cross apply complexColumn.nodes('(SchemeReferencingColumn)') as xmlReferencingColumn(refColumn)
where complexColumn.value('@ID', 'nvarchar(36)') = vdt.ColumnID -- ID колонки SchemeComplexColumn
) as columnInfo
outer apply (
select top 1 *
from @defaultTypesValues
where columnInfo.PhysicalColumnType like N''+ TypeName+'%'
) as dtypes
outer apply (
select top 1 *
from @defaultComplexColumnsValues
where columnInfo.PhysicalColumnReferencedColumn = ReferencedColumn
) as dvalues
where vdt.ValidatorTypeID = @fieldValidatorTypeID
and columnInfo.PhysicalColumnName is not null

UNION
select
vdt.OptionID,
vdt.SectionID,
ts.Name as SectionName,
vdt.ColumnID,
(columnInfo.PhysicalColumnName + dtv.TypeNameJson) as ColumnName,
(case when dtv.NeedQuotes = 1 then '"'+ dtv.defaultValue+'"' else dtv.defaultValue end) as ColumnValue,
vdt.ValidatorTypeID
from @taskOptionsValidators vdt
inner join [Tables] (NOLOCK) ts
on ts.ID = SectionID
outer apply (
select top 1
cast(physColumn.value('@Name', 'nvarchar(max)') as nvarchar(max)) as PhysicalColumnName,
cast(physColumn.value('@Type', 'nvarchar(max)') as nvarchar(max)) as PhysicalColumnType
from (
select [Definition]
from Tables t2 with(nolock)
where t2.ID = vdt.SectionID -- ID секции
) as xmldata (x)
cross apply x.nodes('(/SchemeTable/SchemePhysicalColumn)') xmlPhysicalColumn(physColumn)
where isnull(cast(physColumn.value('@IsSealed', 'nvarchar(36)') as bit), 0) = 0 -- чтобы исключить всякие ID и RowID
) as columnInfo
outer apply (
select top 1 *
from @defaultTypesValues
where columnInfo.PhysicalColumnType like N''+ TypeName+'%'
) as dtv
where vdt.ValidatorTypeID = @sectionValidatorTypeID
and columnInfo.PhysicalColumnName is not null

UNION
select
vdt.OptionID,
vdt.SectionID,
ts.Name as SectionName,
vdt.ColumnID,
(columnInfo.PhysicalColumnName + dtypes.TypeNameJson) as ColumnName,
(case when dvalues.NeedQuotes = 1 then '"'+ dvalues.DefaultValue+'"' else dvalues.DefaultValue end),
vdt.ValidatorTypeID
from @taskOptionsValidators vdt
inner join [Tables] (NOLOCK) ts
on ts.ID = SectionID
outer apply (
select
cast(complexColumn.value('@Name', 'nvarchar(max)') as nvarchar(max)) as ComplexColumnName,
cast(complexColumn.value('@ReferencedTable', 'nvarchar(36)') as uniqueidentifier) as ComplexColumnReferencedTableID,
cast(refColumn.value('@Name', 'nvarchar(max)') as nvarchar(max)) as PhysicalColumnName,
cast(refColumn.value('@Type', 'nvarchar(max)') as nvarchar(max)) as PhysicalColumnType,
cast(refColumn.value('@ReferencedColumn', 'nvarchar(36)') as uniqueidentifier) as PhysicalColumnReferencedColumn
from (
select [Definition]
from Tables t with(nolock)
where t.ID = vdt.SectionID -- ID секции
) as xmldata(x)
cross apply x.nodes('(/SchemeTable/SchemeComplexColumn)') as xmlComplexColumn(complexColumn)
cross apply complexColumn.nodes('(SchemeReferencingColumn)') as xmlReferencingColumn(refColumn)
where isnull(cast(complexColumn.value('@IsSealed', 'nvarchar(36)') as bit), 0) = 0 -- чтобы исключить всякие ID и RowID
) as columnInfo
outer apply (
select top 1 *
from @defaultTypesValues
where columnInfo.PhysicalColumnType like N''+ TypeName+'%'
) as dtypes
outer apply (
select top 1 *
from @defaultComplexColumnsValues
where columnInfo.PhysicalColumnReferencedColumn = ReferencedColumn
) as dvalues
where vdt.ValidatorTypeID = @sectionValidatorTypeID
and columnInfo.PhysicalColumnName is not null

if exists (select top 1 * from @DistinctValidatorsColumns where ColumnValue is null)
begin
select N'Необходимо заполнить значения по умолчанию в следующих колонках'
select * from @DistinctValidatorsColumns where ColumnValue is null
return;
end

insert into @sectionsJson
select top 100 OptionID,
STUFF((
SELECT
', "' + SectionName + '": { "Fields": {' + STUFF((
SELECT
', "' + ColumnName + '": ' + ColumnValue
FROM
@DistinctValidatorsColumns AS innerTable
WHERE
innerTable.OptionID = outerTable.OptionID AND innerTable.SectionID = outerTable.SectionID
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + '} }'
FROM
@DistinctValidatorsColumns AS outerTable
WHERE
outerTable.OptionID = vdt.OptionID
and outerTable.ValidatorTypeID = @fieldValidatorTypeID
GROUP BY outerTable.OptionID, outerTable.SectionID, outerTable.SectionName
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
STUFF((
SELECT
', "' + SectionName + '": {
".table::int":1,
"Rows":[{' + STUFF((
SELECT
', "' + ColumnName + '": ' + ColumnValue
FROM
@DistinctValidatorsColumns AS innerTable
WHERE
innerTable.OptionID = outerTable.OptionID AND innerTable.SectionID = outerTable.SectionID
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + '}]}'
FROM
@DistinctValidatorsColumns AS outerTable
WHERE
outerTable.OptionID = vdt.OptionID
and outerTable.ValidatorTypeID = @sectionValidatorTypeID
GROUP BY outerTable.OptionID, outerTable.SectionID, outerTable.SectionName
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

from @DistinctValidatorsColumns vdt
group by OptionID

truncate table PatGlobalTaskCompletionSettings

insert into PatGlobalTaskCompletionSettings (ID, RowID, CompletionOptionID, CompletionOptionName, CompletionOptionCaption, TaskSectionsJson)
select @settingsCardID, newID(), OptionID, co.Name, co.Caption,
'{' + isnull(EntriesSections, '') + iif(EntriesSections is null or CollectionsSections is null, '', ',') + isnull(CollectionsSections, '') + '}'
from @sectionsJson
inner join CompletionOptions co on co.ID = optionID