Concatenate rows in function PostgreSQL

Question

Assume there's a table projects containing project name, location, team id, start and end years. How can I concatenate rows so that the same names would combine the other information into one string?

name        location       team_id      start       end 
Library     Atlanta        2389         2015        2017
Library     Georgetown     9920         2003        2007
Museum      Auckland       3092         2005        2007

Expected output would look like this:

name                 Records 
Library     Atlanta, 2389, 2015-2017
            Georgetown, 9920, 2003-2007
Museum      Auckland, 3092, 2005-2007

Each line should contain end-of-line / new line character.

I have a function for this, but I don't think it would work with just using CONCAT. What are other ways this can be done? What I tried:

CREATE OR REPLACE TYPE projects (name TEXT, records TEXT); 

CREATE OR REPLACE FUNCTION records (INT) 
RETURNS SETOF projects AS
$$ 
     RETURN QUERY
     SELECT p.name
            CONCAT(p.location, ', ', p.team_id, ', ', p.start, '-', p.end, CHAR(10))
     FROM projects($1) p; 
$$ 
LANGUAGE PLpgSQL;

I tried using CHAR(10) for new line, but its giving a syntax error (not sure why?).

The above sample concatenate the string but expectedly leaving out duplicated names.

Jim Jones · Accepted Answer · 2021-04-06T05:17:48.210

You do not need PL/pgSQL for that.

First eliminate duplicate names using DISTINCT and then in a subquery you can concat the columns into a single string. After that use array_agg to create an array out of it. It will then "merge" multiple arrays, in case the subquery returns more than one row. Finally, get rid of the commas and curly braces using array_to_string. Instead of using the char value of a newline, you can simply use E'\n' (E stands for escape):

WITH j (name,location,team_id,start,end_) AS (
  VALUES ('Library','Atlanta',2389,2015,2017),
         ('Library','Georgetown',9920,2003,2007),
         ('Museum','Auckland',3092,2005,2007)
)
SELECT 
  DISTINCT q1.name,
  array_to_string(
  (SELECT array_agg(concat(location,', ',team_id,', ',start,'-', end_, E'\n'))
   FROM j WHERE name = q1.name),'') AS records
FROM j q1;

  name   |           records           
---------+----------------------------
 Library | Atlanta, 2389, 2015-2017   
         | Georgetown, 9920, 2003-2007
         | 
 Museum  | Auckland, 3092, 2005-2007

Note: try to not use reserved strings (e.g. end,name,start, etc.) to name your columns. Although PostgreSQL allows you to use them, it is considered a bad practice.

Demo: db<>fiddle

score 1 · Answer 2 · answered Apr 05 '21 at 12:47

1

A bit simple query:

select 
  name, 
  string_agg( concat(location, ', ', team_id, ', ', start, '-', "end"), E'\n') AS records 
FROM  t 
group by name;

PostgreSQL fiddle

answered Apr 05 '21 at 12:47

Slava Rozhnev

9,510
6
23
39

Concatenate rows in function PostgreSQL

2 Answers2