'stuff' and 'for xml path('')' from SQL Server in Postgresql

Question

I'm migrating some SQL Server 2008R2 queries to Postgresql 9.0 and I have some trouble with it. Here's the SQL Server query:

stuff((select ', '+p.[NAME] as 'data()' 
from BPROVIDERS_PROVIDER p, BORDER_ARTICLEORDERPROVIDER aop 
where p.OID = aop.PROVIDER for xml path('')),1,1,'')) as pNAMES

Reading SQL Server documentation I understand that this creates a comma separated list. I think that I can change stuff function to overlay function in Postresql'. Am I correct?

The second problem comes with SQL Server's for xml path with ('') as a parameter. It returns the values assigned to an attribute called pNAMES instead of create row elements. Is that correct?

Does Postgresql Query_to_xml() function with attribute tableforest = 'true' do the same?

Thank you.

Mikael Eriksson · Answer 1 · 2013-01-02T10:03:07.760

26

You can use string_agg instead.

SQL Fiddle

PostgreSQL 9.1.6 Schema Setup:

create table T
(
  Name varchar(10)
);

insert into T values('Kalle');
insert into T values('Pelle');
insert into T values('Urban');

Query 1:

select string_agg(Name, ',') as Names
from T

Results:

|             NAMES |
---------------------
| Kalle,Pelle,Urban |

edited Jan 02 '13 at 10:03

answered Jan 02 '13 at 09:56

Mikael Eriksson

136,425
22
210
281

Thanks for your answer, what about 'for xml path' and 'query_to_xml'? – user1891262 Jan 02 '13 at 13:04
3

@user1891262 - You don't need that for the query you have. `string_agg` in PostgreSQL does what `for xml path` did for you in SQL Server. – Mikael Eriksson Jan 02 '13 at 13:10

Syed Kashif · Answer 2 · 2021-11-16T03:49:21.563

STUFF((SELECT DISTINCT ', ' + CONVERT(VARCHAR,L.ROLE_SUB_CAT_ID) FROM [PHS].[dbo].PHS_ADMIN_USER_ACCESS_DTL K, [PHS].[dbo].[PHS_ADMIN_USER_ROLE_SUB_CAT_MST] L WHERE L.ROLE_SUB_CAT_ID = K.ROLE_SUB_CAT_ID AND K.UMC_ID = A.UMC_ID AND K.CCR_ID = A.CCR_ID FOR XML PATH('')), 1, 1, '') AS ROLE_SUB_CAT_ID

Convert it to postgresql like this:

string_agg((SELECT distinct ', ' || cast(L.ROLE_SUB_CAT_ID as VARCHAR) FROM PHS.dbo.PHS_ADMIN_USER_ACCESS_DTL K, PHS.dbo.PHS_ADMIN_USER_ROLE_SUB_CAT_MST L WHERE L.ROLE_SUB_CAT_ID = K.ROLE_SUB_CAT_ID AND K.UMC_ID = A.UMC_ID AND K.CCR_ID=A.CCR_ID ), 1, 1, '') AS ROLE_SUB_CAT_ID

score -1 · Answer 3 · edited Jan 21 '21 at 07:21

STUFF() with XML PATH

same teble to record STUFF

SELECT distinct C.country, X.ProductList FROM     
tbl_demo as C     
CROSS APPLY    
(    
SELECT STUFF    
      (    
          (    
              SELECT 
                    distinct 
                        ',' + P.product 
                    FROM 
                        tbl_demo AS P    
                    JOIN 
                        tbl_demo AS CP 
                    ON 
                        P.country = CP.country     
                    WHERE 
                        CP.sub_id = C.sub_id    
                    FOR XML PATH('')    
          )    
              
      ,1,1,'') as ProductList    
) as X

'stuff' and 'for xml path('')' from SQL Server in Postgresql

3 Answers3

Linked