Re: Seamless replacement to MySQL's GROUP_CONCAT function...
От | immersive.excel@gmail.com |
---|---|
Тема | Re: Seamless replacement to MySQL's GROUP_CONCAT function... |
Дата | |
Msg-id | 52011F96.3010007@gmail.com обсуждение исходный текст |
Ответ на | Re: Seamless replacement to MySQL's GROUP_CONCAT function... (Pavel Stehule <pavel.stehule@gmail.com>) |
Список | 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 );
В списке pgsql-general по дате отправления:
Предыдущее
От: "immersive.excel@gmail.com"Дата:
Сообщение: Re: Seamless replacement to MySQL's GROUP_CONCAT function...