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

<meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">


    A (final?) version using COALESCE (<grin>It wasn't too long to
    post at the blog now</grin>; I am also posting here for belt
    and suspenders reasons...):

    -- 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
      RETURN COALESCE(column1||delimiter||column2, column2,
        column1);
      END;
      $$ LANGUAGE plpgsql;

      CREATE OR REPLACE
      FUNCTION GROUP_CONCAT_ATOM(column1 TEXT, column2 INT8,
        delimiter TEXT)
               RETURNS TEXT AS $$
      BEGIN
      RETURN COALESCE(column1||delimiter||CAST(column2 AS
        TEXT), CAST(column2 AS TEXT), column1);
      END;
      $$ LANGUAGE plpgsql;

      CREATE OR REPLACE
      FUNCTION GROUP_CONCAT_ATOM(column1 INT8, column2 TEXT,
        delimiter TEXT)
               RETURNS TEXT AS $$
      BEGIN
      RETURN COALESCE(CAST(column1 AS
        TEXT)||delimiter||column2, column2, CAST(column1 AS TEXT));
      END;
      $$ LANGUAGE plpgsql;

      CREATE OR REPLACE
      FUNCTION GROUP_CONCAT_ATOM(column1 INT8, column2 INT8,
        delimiter TEXT)
               RETURNS TEXT AS $$
      BEGIN
      RETURN COALESCE(CAST(column1 AS
        TEXT)||delimiter||CAST(column2 AS TEXT), CAST(column2 AS TEXT),
        CAST(column1 AS TEXT));
      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
      RETURN COALESCE(column1||','||column2, column2, column1);
      END;
      $$ LANGUAGE plpgsql;

      CREATE OR REPLACE
      FUNCTION GROUP_CONCAT_ATOM(column1 INT8, column2 INT8) --
        delimiter=','
               RETURNS TEXT AS $$
      BEGIN
      RETURN COALESCE(CAST(column1 AS TEXT)||','||CAST(column2
        AS TEXT), CAST(column2 AS TEXT), CAST(column1 AS TEXT));
      END;
      $$ LANGUAGE plpgsql;

      CREATE OR REPLACE
      FUNCTION GROUP_CONCAT_ATOM(column1 INT8, column2 TEXT) --
        delimiter=','
               RETURNS TEXT AS $$
      BEGIN
      RETURN COALESCE(CAST(column1 AS TEXT)||','||column2,
        column2, CAST(column1 AS TEXT));
      END;
      $$ LANGUAGE plpgsql;

      CREATE OR REPLACE
      FUNCTION GROUP_CONCAT_ATOM(column1 TEXT, column2 INT8) --
        delimiter=','
               RETURNS TEXT AS $$
      BEGIN
      RETURN COALESCE(column1||','||CAST(column2 AS TEXT),
        CAST(column2 AS TEXT), column1);
      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
                       );