Re: Seamless replacement to MySQL's GROUP_CONCAT function... - Mailing list pgsql-general

No luck on posting at that blog; comments are limited to X characters.
Here is the final version with the minor update to the variable names
and comments:

-- group_concat.sql

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

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

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

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

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

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

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

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

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

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

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

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