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

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

  • failed to load resource file sqlcmd.rll
  • Using NHibernate mapping by code: Cannot insert explicit value for identity column in table 'DietUser' when IDENTITY_INSERT is set to OFF
  • Concatenate, how to get different values from a column into one row?
  • SQL Server Case Statement when IS NULL
  • Which is better: Distinct or Group By
  • SQL Server hierarchy query for primary/secondary variables
  • 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.

  • SQL First In First Out Loyalty Point
  • How can I setup my SQL Server LocalDB to be as close to a normal SQL Server?
  • Edit TOP Row in SQL Server Management Studio
  • Insertion without using cursor
  • SQL Server: using table or @table in stored procedure
  • Why is ConnectTimeout Ignored In This Case?
  • One Solution collect form web for “'stuff' and 'for xml path('')' from SQL Server in Postgresql”

    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


    |             NAMES |
    | Kalle,Pelle,Urban |
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.