[GENERAL] 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
Re: [GENERAL] Seamless replacement to MySQL's GROUP_CONCAT function...
Ahh! I was looking for that in the documentation!! I believe that is not mentioned on the function documentation page. I had tried ANY that works for other SQLs, but it had barfed on it. Let me test it and post the more efficient code, if it works... On 08/05/2013 01:04 PM, Alvaro Herrera wrote: > immersive.ex...@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? > 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. > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Seamless replacement to MySQL's GROUP_CONCAT function...
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: For posterity that finalized function could be posted here: 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 <alvhe...@2ndquadrant.com> wrote: immersive.ex...@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? 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Regards, Richard Broersma Jr.
Re: [GENERAL] Seamless replacement to MySQL's GROUP_CONCAT function...
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... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Seamless replacement to MySQL's GROUP_CONCAT function...
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 ); -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Seamless replacement to MySQL's GROUP_CONCAT function...
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.ex...@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 &g
Re: [GENERAL] Seamless replacement to MySQL's GROUP_CONCAT function...
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 .. > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Seamless replacement to MySQL's GROUP_CONCAT function...
A (final?) version using COALESCE (It wasn't too long to post at the blog now; 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 );
