我很难将查询从SQL转换为postgresql [英] I have difficulties translating a query from SQL to postgresql

问题描述

大家好。我很难将查询从sql转换为postgresql。 sql中的查询是：

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'')

我研究并发现postgresql等价物sqlstuff是

I researched and find that postgresql equivalent of sql "stuff" is

overlay

所以我的postgre sql查询是：

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)

但是当我在pgadmin中执行此查询时，我收到以下错误消息：

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)

你能帮我翻译我的sql查询到postgre吗？



我尝试过：



我试图找到如何翻译此代码块

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

从sql到postgre sql但没有成功。

" from sql to postgre sql but without success.

推荐答案

一个简单的谷歌搜索产生了这个：

A simple google search resulted in this:

SELECT table_to_xml('users', true, false, '');

--Or

SELECT query_to_xml('SELECT * FROM users', true, false, '');

如果这还不足以让你开始，我会把你留给疯狂的google技巧。

I leave you to your mad google skillz if this isn't sufficient to get you started.

这篇关于我很难将查询从SQL转换为postgresql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！