Seamless replacement to MySQL's GROUP_CONCAT function...

["immersive.excel@xxxxxxxxx" <immersive.excel@xxxxxxxxx>]

I needed a GROUP_CONCAT to port some queries to postgres.

In discussions online, I found repeated advice for rewriting the queries, but no solid way to formulate the GROUP_CONCAT as a postgres function. Rewrite perhaps hundreds of queries that happen to be in the app you're porting? Puh-lease!

Note: I found some close-but-no cigar aggregates shared online, but they would not accept integer arguments, nor would they handle the optionally furnished delimiter. People would suggesting casting the argument to the pseudo-GROUP_CONCAT. Again: Rewrite perhaps hundreds of queries?

And now the formulation of GROUP_CONCAT for postgres that accepts either integer or string columns, and the optional delimiter:

-- permutation of GROUP_CONCAT parameter types with delimiter parameter furnished:
CREATE OR REPLACE
FUNCTION GROUP_CONCAT_ATOM(field1 TEXT, field2 TEXT, delimiter TEXT)
         RETURNS TEXT AS $$
BEGIN
IF field1 IS NULL THEN
   RETURN field2;
ELSIF field2 IS NULL THEN
      RETURN field1;
ELSE
     RETURN field1||delimiter||field2;
END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE
FUNCTION GROUP_CONCAT_ATOM(field1 TEXT, field2 INT8, delimiter TEXT)
         RETURNS TEXT AS $$
BEGIN
IF field1 IS NULL THEN
   IF field2 IS NULL THEN
      RETURN NULL;
   ELSE
        RETURN CAST(field2 AS TEXT);
   END IF;
ELSIF field2 IS NULL THEN
      RETURN field1;
ELSE
     RETURN field1||delimiter||CAST(field2 AS TEXT);
END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE
FUNCTION GROUP_CONCAT_ATOM(field1 INT8, field2 TEXT, delimiter TEXT)
         RETURNS TEXT AS $$
BEGIN
IF field1 IS NULL THEN
   RETURN field2;
ELSIF field2 IS NULL THEN
      IF field1 IS NULL THEN
         RETURN NULL;
      ELSE
           RETURN CAST(field1 AS TEXT);
      END IF;
ELSE
     RETURN CAST(field1 AS TEXT)||delimiter||field2;
END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE
FUNCTION GROUP_CONCAT_ATOM(field1 INT8, field2 INT8, delimiter TEXT)
         RETURNS TEXT AS $$
BEGIN
IF field1 IS NULL THEN
   IF field2 IS NULL THEN
      RETURN NULL;
   ELSE
        RETURN CAST(field2 AS TEXT);
   END IF;
ELSIF field2 IS NULL THEN
      IF field1 IS NULL THEN
         RETURN NULL;
      ELSE
           RETURN CAST(field1 AS TEXT);
      END IF;
ELSE
     RETURN CAST(field1 AS TEXT)||delimiter||CAST(field2 AS TEXT);
END IF;
END;
$$ LANGUAGE plpgsql;

-- permutation of function arguments without delimiter furnished:
CREATE OR REPLACE
FUNCTION GROUP_CONCAT_ATOM(field1 TEXT, field2 TEXT) -- delimiter=','
         RETURNS TEXT AS $$
BEGIN
IF field1 IS NULL THEN
   IF field2 IS NULL THEN
      RETURN NULL;
   ELSE
        RETURN CAST(field2 AS TEXT);
   END IF;
ELSIF field2 IS NULL THEN
      RETURN field1;
ELSE
     RETURN field1||','||field2;
END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE
FUNCTION GROUP_CONCAT_ATOM(field1 INT8, field2 INT8) -- delimiter=','
         RETURNS TEXT AS $$
BEGIN
IF field1 IS NULL THEN
   IF field2 IS NULL THEN
      RETURN NULL;
   ELSE
        RETURN CAST(field2 AS TEXT);
   END IF;
ELSIF field2 IS NULL THEN
      IF field1 IS NULL THEN
         RETURN NULL;
      ELSE
           RETURN CAST(field1 AS TEXT);
      END IF;
ELSE
     RETURN CAST(field1 AS TEXT)||','||CAST(field2 AS TEXT);
END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE
FUNCTION GROUP_CONCAT_ATOM(field1 INT8, field2 TEXT)
         RETURNS TEXT AS $$
BEGIN
IF field1 IS NULL THEN
   RETURN field2;
ELSIF field2 IS NULL THEN
      IF field1 IS NULL THEN
         RETURN NULL;
      ELSE
           RETURN CAST(field1 AS TEXT);
      END IF;
ELSE
     RETURN CAST(field1 AS TEXT)||','||field2;
END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE
FUNCTION GROUP_CONCAT_ATOM(field1 TEXT, field2 INT8) -- delimiter=','
         RETURNS TEXT AS $$
BEGIN
IF field1 IS NULL THEN
   IF field2 IS NULL THEN
      RETURN NULL;
   ELSE
        RETURN CAST(field2 AS TEXT);
   END IF;
ELSIF field2 IS NULL THEN
      RETURN field1;
ELSE
     RETURN field1||','||CAST(field2 AS TEXT);
END IF;
END;
$$ LANGUAGE plpgsql;

-- aggregates for all parameter types with delimiter:
DROP AGGREGATE IF EXISTS GROUP_CONCAT(TEXT, TEXT); -- field, delimiter
CREATE AGGREGATE GROUP_CONCAT(TEXT, TEXT) -- field, delimiter
                 (SFUNC=GROUP_CONCAT_ATOM,
                  STYPE=TEXT
                 );

DROP AGGREGATE IF EXISTS GROUP_CONCAT(INT8, TEXT); -- field, delimiter
CREATE AGGREGATE GROUP_CONCAT(INT8, TEXT) -- field, delimiter
                 (SFUNC=GROUP_CONCAT_ATOM,
                  STYPE=TEXT
                 );

-- aggregates for all parameter types without the optional delimiter:
DROP AGGREGATE IF EXISTS GROUP_CONCAT(TEXT); -- field, delimiter=','
CREATE AGGREGATE GROUP_CONCAT(TEXT) -- field
                 (SFUNC=GROUP_CONCAT_ATOM,
                  STYPE=TEXT
                 );

DROP AGGREGATE IF EXISTS GROUP_CONCAT(INT8); -- field, delimiter=','
CREATE AGGREGATE GROUP_CONCAT(INT8) -- field
                 (SFUNC=GROUP_CONCAT_ATOM,
                  STYPE=TEXT
                 );