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-06 05:17:48Z

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

Slava Rozhnev · Accepted Answer · 2021-04-05 12:47:27Z

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

Collectives™ on Stack Overflow

Concatenate rows in function PostgreSQL

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related