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@xxxxxxxxx <immersive.excel@xxxxxxxxx>: >> 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 >> ); >> -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
