Hi, everyone. I have difficulties translating a query from sql to postgresql. The query in sql is:
SELECT Stuff((SELECT N', ' + reference_number + '_' + report_name
FROM tbl_report_definition
WHERE report_definition_id in
(SELECT report_definition_id
FROM tbl_report_definition_columns
WHERE report_header_id = rh.id)
FOR XML PATH('') ,TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N'')
I researched and find that postgresql equivalent of sql "stuff" is
overlay
so my postgre sql query is:
SELECT overlay((SELECT N', ' + reference_number + '_' + report_name
FROM tbl_report_definition
WHERE report_definition_id in
(SELECT report_definition_id
FROM tbl_report_definition_columns
WHERE report_header_id = rh.id)
FOR XML PATH('') ,TYPE)
.value('text()[1]','nvarchar(max)') placing N'' from 1 for 2)
But when I executed this query in pgadmin I received the following error message:
syntax error at or near "XML" LINE 15: FOR XML PATH('') ,TYPE)
Can you help me to translate my sql query to postgre right?
What I have tried:
I tried to find how to translate this code block "
FOR XML PATH('') ,TYPE
" from sql to postgre sql but without success.