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

Hello

2013/8/3 immersive(dot)excel(at)gmail(dot)com <immersive(dot)excel(at)gmail(dot)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
> );
>