Thread: Seamless replacement to MySQL's GROUP_CONCAT function...

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
                           );

immersive.excel@gmail.com escribió:

>     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?<br>

I don't think you need all the variations; you should be able to make
them all work with a single set of functions, taking ANYELEMENT instead
of text/int8/int4 etc.


--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Ahh! I was looking for that in the documentation!! I believe that is not mentioned on the function documentation page.
Ihad tried ANY that works for other SQLs, but it had barfed on it. Let me test it and post the more efficient code, if
itworks... 

========================
On 08/05/2013 01:04 PM, Alvaro Herrera wrote:
> immersive.excel@gmail.com escribió:
>
>>     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?<br>
> I don't think you need all the variations; you should be able to make
> them all work with a single set of functions, taking ANYELEMENT instead
> of text/int8/int4 etc.
>
>

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


    I believe his VARIADIC might cover the optional delimiter use, but
    he suggests using an array aggregation function instead -which does
    not return a string list, so you would still have to rework the
    original queries.

    Now you could write a GROUP_CONCAT aggregate that uses a final
    function to convert the array to a string list -but correct me if
    I'm wrong, I believe the way I am concatenating as I go uses less
    resources (an array of, say, 5000 elements versus a string...)

    But while I am testing use of the ANYELEMENT, I will see if VARIADIC
    can simplify things a tad more...

    ========================
    On 08/05/2013 01:15 PM, Richard Broersma wrote:
    <blockquote
cite="mid:CABvLTWEZyie9+zZZ9+xJNgaPsNLOL=OfOn2KROSrbryyQY2QEg@mail.gmail.com"
      type="cite">
      <meta http-equiv="Context-Type" content="text/html;
        charset=ISO-8859-1">

        For posterity that finalized function could be posted here:

          <a moz-do-not-send="true"

href="http://okbob.blogspot.com/2009/08/mysql-functions-for-postgresql.html">http://okbob.blogspot.com/2009/08/mysql-functions-for-postgresql.html 



        There's already a GROUP_CONCAT, listed there, but I guess this
        one was lacking in some way.



        On Mon, Aug 5, 2013 at 10:04 AM, Alvaro Herrera <<a
              moz-do-not-send="true"
              href="mailto:alvherre@2ndquadrant.com">alvherre@2ndquadrant.com>
          wrote:
          <a moz-do-not-send="true"
              href="mailto:immersive.excel@gmail.com">immersive.excel@gmail.com
            escribió:

              >     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?<br>

            I don't think you need all the variations; you should be
            able to make
            them all work with a single set of functions, taking
            ANYELEMENT instead
            of text/int8/int4 etc.


              --
              Álvaro Herrera                <a moz-do-not-send="true"
                href="http://www.2ndQuadrant.com/">http://www.2ndQuadrant.com/
              PostgreSQL Development, 24x7 Support, Training &
              Services


              --
              Sent via pgsql-general mailing list (<a
                moz-do-not-send="true"
                href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org)
              To make changes to your subscription:
              <a moz-do-not-send="true"

href="http://www.postgresql.org/mailpref/pgsql-general">http://www.postgresql.org/mailpref/pgsql-general





        --
        Regards,
        Richard Broersma Jr.

Well after testing, this is what I found:

When you try to use ANYELEMENT parameters, and even just a VARIADIC
TEXT[] parameter to support the optional delimiter argument:

    FUNCTION GROUP_CONCAT_ATOM(ANYELEMENT, ANYELEMENT, VARIADIC
delimiters TEXT[])

when you go to create the aggregates, postgres runs through a
permutation of the argument types, just to be sure that all functions
are accounted for. And at that point postgres complains:

    function group_concat_atom(text, anyelement, text) does not exist

You would _think_ that the function definition above would cover this
case, but it for whatever reason it does not.

In the process of trying variations, I see that I used the deficient
nomenclature of "field" instead of "column" in the previous code. I will
fix that and post at the other site listed above; but other than that,
the code works and I welcome anyone to come up with a more compact
version that allows it to remain as flexible as it is now...

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
                 );

Hello

2013/8/3 immersive.excel@gmail.com <immersive.excel@gmail.com>:
> 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;

your code will be significantly faster when you don't use a classic C
programming style and use a COALESCE function. PL/pgSQL is a
interpreted language and is necessary to minimize number of
instruction.

you code can be translated to

CREATE OR REPLACE
FUNCTION GROUP_CONCAT_ATOM(field1 TEXT, field2 TEXT, delimiter TEXT)
         RETURNS TEXT AS $$
BEGIN
  RETURN COALESCE(field1||delimiter||field2, field2, field1);
END;
$$ LANGUAGE plpgsql;

Regards

Pavel

p.s. speed is in this use case important, because you execute this
function for every row


>
> 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
>                  );
>

Pavel Stehule escribió:

> you code can be translated to
>
> CREATE OR REPLACE
> FUNCTION GROUP_CONCAT_ATOM(field1 TEXT, field2 TEXT, delimiter TEXT)
>          RETURNS TEXT AS $$
> BEGIN
>   RETURN COALESCE(field1||delimiter||field2, field2, field1);
> END;
> $$ LANGUAGE plpgsql;

Actually you don't even need plpgsql for this, do you ..

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

2013/8/6 Alvaro Herrera <alvherre@2ndquadrant.com>:
> Pavel Stehule escribió:
>
>> you code can be translated to
>>
>> CREATE OR REPLACE
>> FUNCTION GROUP_CONCAT_ATOM(field1 TEXT, field2 TEXT, delimiter TEXT)
>>          RETURNS TEXT AS $$
>> BEGIN
>>   RETURN COALESCE(field1||delimiter||field2, field2, field1);
>> END;
>> $$ LANGUAGE plpgsql;
>
> Actually you don't even need plpgsql for this, do you ..

There is better optimized plpgsql - not sure, why - probably, so there
is not possible inlining .. and start of SQL engine is little bit
slower than start of plpgsql.

postgres=# create table f(a text, b int);
CREATE TABLE
postgres=# insert into f select 'Ahoj', random()*100 from
generate_series(1,10000);

select group_concat(a,',') from f group by b;

PL/pgSQL .. 36 ms
SQL          .. 49 ms

Regards

Pavel Stehule

>
> --
> Álvaro Herrera                http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services

That is good and I will effect that change here; but as far as I can see
you will still need to permute the types of the arguments in that set of
functions in order for the aggregates to accept an int argument and an
optional text argument, and zap out a TEXT...

========================
On 08/06/2013 12:49 AM, Pavel Stehule wrote:
> Hello
>
> 2013/8/3 immersive.excel@gmail.com <immersive.excel@gmail.com>:
>> 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;
> your code will be significantly faster when you don't use a classic C
> programming style and use a COALESCE function. PL/pgSQL is a
> interpreted language and is necessary to minimize number of
> instruction.
>
> you code can be translated to
>
> CREATE OR REPLACE
> FUNCTION GROUP_CONCAT_ATOM(field1 TEXT, field2 TEXT, delimiter TEXT)
>          RETURNS TEXT AS $$
> BEGIN
>   RETURN COALESCE(field1||delimiter||field2, field2, field1);
> END;
> $$ LANGUAGE plpgsql;
>
> Regards
>
> Pavel
>
> p.s. speed is in this use case important, because you execute this
> function for every row
>
>
>> 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
>>                  );
>>

You're saying as oppose to straight SQL? I don't think so; but I had
defined it as such just in case there was some functional benefit that I
might be unaware of...

========================
On 08/06/2013 01:26 AM, Alvaro Herrera wrote:
> Pavel Stehule escribió:
>
>> you code can be translated to
>>
>> CREATE OR REPLACE
>> FUNCTION GROUP_CONCAT_ATOM(field1 TEXT, field2 TEXT, delimiter TEXT)
>>          RETURNS TEXT AS $$
>> BEGIN
>>   RETURN COALESCE(field1||delimiter||field2, field2, field1);
>> END;
>> $$ LANGUAGE plpgsql;
> Actually you don't even need plpgsql for this, do you ..
>

<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
                       );