-2

In MySQL I use GROUP_CONCAT() function, nevertheless now i need to convert that to Postgres. Can anyone help me out how to convert such query into Postgres? I think the only one which has to be replaced is the line with GROUP_CONCAT?

SET @sql = NULL;
SELECT CONCAT(
   'SELECT ',GROUP_CONCAT(c.TABLE_NAME,'.',c.COLUMN_NAME,' AS `',c.TABLE_NAME,'.',c.COLUMN_NAME,'`'),'
    from t1 AS thre
inner join t2 as ter on thre.datasource = ter.datasource 
inner join t3 as ston on thre.datasource = ston.datasource
inner join t4 as diron on thre.datasource = diron.datasource'
)
INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME IN ('t1','t2',
                       't3','t4');    
PREPARE sql_statement FROM @sql;
EXECUTE sql_statement;
Henry
  • 537
  • 1
  • 9
  • 22
  • And many more answers [here](https://stackoverflow.com/search?page=2&tab=Relevance&q=%5bpostgresql%5d%20group_concat) and [here](https://stackoverflow.com/questions/tagged/postgresql+string-aggregation) –  Oct 17 '19 at 07:02

2 Answers2

0

You may use STRING_AGG

select 'SELECT '||string_agg(c.TABLE_NAME||'.'||c.COLUMN_NAME||' AS '
                           ||c.TABLE_NAME||'.'||c.COLUMN_NAME ,',') ||
            'from t1 AS thre inner join t2 ..
            ..
            .. = diron.datasource'              
 from INFORMATION_SCHEMA.COLUMNS c

|| is the concatenation operator in Postgres

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
0

I think you are looking for the string_agg() function

https://www.postgresql.org/docs/12/functions-aggregate.html

richyen
  • 8,114
  • 4
  • 13
  • 28