0

I'm trying to take a list of tag rows and add them to a column as a CSV. I thought a subquery would be appropriate for this using string_agg. Below eventstags is a many-to-many table associating tags with their event.

(SELECT string_agg(name, ',') FROM tags WHERE tags.id IN (SELECT eventstags.tags_id WHERE eventstags.events_id = events.id)) AS tags

This appears to only return a single tag, as if it's ignoring all the other tag rows. Any idea how I can get the IN query to be aware of the other rows so that it concatenates the tag names as a CSV column?

I'm using psycopg2 and Postgres 9.5

DigitalDisaster
  • 467
  • 3
  • 10
  • 25

1 Answers1

0

create agregate:

CREATE AGGREGATE str_sum(text) (
  SFUNC=strcat,
  STYPE=text,
  INITCOND=''
);

and function:

CREATE OR REPLACE FUNCTION strcat(text, text)
  RETURNS text AS
$BODY$
  begin
    if $1 = '' then
      return $2;
    else
      return ( $1 || ', ' ) || $2;
    end if;
  end;
$BODY$
  LANGUAGE plpgsql STABLE

and use str_sum in subquery:

SELECT str_sum(name) FROM tags
Piotr Rogowski
  • 3,642
  • 19
  • 24