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

Answer 1

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 |