<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
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...):<br>
<br>
<big><tt>-- group_concat.sql</tt><tt><br>
</tt><tt><br>
</tt><tt>-- permutation of GROUP_CONCAT parameter types with
delimiter parameter furnished:</tt><tt><br>
</tt><tt>CREATE OR REPLACE</tt><tt><br>
</tt><tt>FUNCTION GROUP_CONCAT_ATOM(column1 TEXT, column2 TEXT,
delimiter TEXT)</tt><tt><br>
</tt><tt> RETURNS TEXT AS $$</tt><tt><br>
</tt><tt>BEGIN</tt><tt><br>
</tt><tt>RETURN COALESCE(column1||delimiter||column2, column2,
column1);</tt><tt><br>
</tt><tt>END;</tt><tt><br>
</tt><tt>$$ LANGUAGE plpgsql;</tt><tt><br>
</tt><tt><br>
</tt><tt>CREATE OR REPLACE</tt><tt><br>
</tt><tt>FUNCTION GROUP_CONCAT_ATOM(column1 TEXT, column2 INT8,
delimiter TEXT)</tt><tt><br>
</tt><tt> RETURNS TEXT AS $$</tt><tt><br>
</tt><tt>BEGIN</tt><tt><br>
</tt><tt>RETURN COALESCE(column1||delimiter||CAST(column2 AS
TEXT), CAST(column2 AS TEXT), column1);</tt><tt><br>
</tt><tt>END;</tt><tt><br>
</tt><tt>$$ LANGUAGE plpgsql;</tt><tt><br>
</tt><tt><br>
</tt><tt>CREATE OR REPLACE</tt><tt><br>
</tt><tt>FUNCTION GROUP_CONCAT_ATOM(column1 INT8, column2 TEXT,
delimiter TEXT)</tt><tt><br>
</tt><tt> RETURNS TEXT AS $$</tt><tt><br>
</tt><tt>BEGIN</tt><tt><br>
</tt><tt>RETURN COALESCE(CAST(column1 AS
TEXT)||delimiter||column2, column2, CAST(column1 AS TEXT));</tt><tt><br>
</tt><tt>END;</tt><tt><br>
</tt><tt>$$ LANGUAGE plpgsql;</tt><tt><br>
</tt><tt><br>
</tt><tt>CREATE OR REPLACE</tt><tt><br>
</tt><tt>FUNCTION GROUP_CONCAT_ATOM(column1 INT8, column2 INT8,
delimiter TEXT)</tt><tt><br>
</tt><tt> RETURNS TEXT AS $$</tt><tt><br>
</tt><tt>BEGIN</tt><tt><br>
</tt><tt>RETURN COALESCE(CAST(column1 AS
TEXT)||delimiter||CAST(column2 AS TEXT), CAST(column2 AS TEXT),
CAST(column1 AS TEXT));</tt><tt><br>
</tt><tt>END;</tt><tt><br>
</tt><tt>$$ LANGUAGE plpgsql;</tt><tt><br>
</tt><tt><br>
</tt><tt>-- permutation of function arguments without delimiter
furnished:</tt><tt><br>
</tt><tt>CREATE OR REPLACE</tt><tt><br>
</tt><tt>FUNCTION GROUP_CONCAT_ATOM(column1 TEXT, column2 TEXT) --
delimiter=','</tt><tt><br>
</tt><tt> RETURNS TEXT AS $$</tt><tt><br>
</tt><tt>BEGIN</tt><tt><br>
</tt><tt>RETURN COALESCE(column1||','||column2, column2, column1);</tt><tt><br>
</tt><tt>END;</tt><tt><br>
</tt><tt>$$ LANGUAGE plpgsql;</tt><tt><br>
</tt><tt><br>
</tt><tt>CREATE OR REPLACE</tt><tt><br>
</tt><tt>FUNCTION GROUP_CONCAT_ATOM(column1 INT8, column2 INT8) --
delimiter=','</tt><tt><br>
</tt><tt> RETURNS TEXT AS $$</tt><tt><br>
</tt><tt>BEGIN</tt><tt><br>
</tt><tt>RETURN COALESCE(CAST(column1 AS TEXT)||','||CAST(column2
AS TEXT), CAST(column2 AS TEXT), CAST(column1 AS TEXT));</tt><tt><br>
</tt><tt>END;</tt><tt><br>
</tt><tt>$$ LANGUAGE plpgsql;</tt><tt><br>
</tt><tt><br>
</tt><tt>CREATE OR REPLACE</tt><tt><br>
</tt><tt>FUNCTION GROUP_CONCAT_ATOM(column1 INT8, column2 TEXT) --
delimiter=','</tt><tt><br>
</tt><tt> RETURNS TEXT AS $$</tt><tt><br>
</tt><tt>BEGIN</tt><tt><br>
</tt><tt>RETURN COALESCE(CAST(column1 AS TEXT)||','||column2,
column2, CAST(column1 AS TEXT));</tt><tt><br>
</tt><tt>END;</tt><tt><br>
</tt><tt>$$ LANGUAGE plpgsql;</tt><tt><br>
</tt><tt><br>
</tt><tt>CREATE OR REPLACE</tt><tt><br>
</tt><tt>FUNCTION GROUP_CONCAT_ATOM(column1 TEXT, column2 INT8) --
delimiter=','</tt><tt><br>
</tt><tt> RETURNS TEXT AS $$</tt><tt><br>
</tt><tt>BEGIN</tt><tt><br>
</tt><tt>RETURN COALESCE(column1||','||CAST(column2 AS TEXT),
CAST(column2 AS TEXT), column1);</tt><tt><br>
</tt><tt>END;</tt><tt><br>
</tt><tt>$$ LANGUAGE plpgsql;</tt><tt><br>
</tt><tt><br>
</tt><tt>-- aggregates for all parameter types with delimiter:</tt><tt><br>
</tt><tt>DROP AGGREGATE IF EXISTS GROUP_CONCAT(TEXT, TEXT); --
column, delimiter</tt><tt><br>
</tt><tt>CREATE AGGREGATE GROUP_CONCAT(TEXT, TEXT) -- column,
delimiter</tt><tt><br>
</tt><tt> (SFUNC=GROUP_CONCAT_ATOM,</tt><tt><br>
</tt><tt> STYPE=TEXT</tt><tt><br>
</tt><tt> );</tt><tt><br>
</tt><tt><br>
</tt><tt>DROP AGGREGATE IF EXISTS GROUP_CONCAT(INT8, TEXT); --
column, delimiter</tt><tt><br>
</tt><tt>CREATE AGGREGATE GROUP_CONCAT(INT8, TEXT) -- column</tt><tt><br>
</tt><tt> (SFUNC=GROUP_CONCAT_ATOM,</tt><tt><br>
</tt><tt> STYPE=TEXT</tt><tt><br>
</tt><tt> );</tt><tt><br>
</tt><tt><br>
</tt><tt>-- aggregates for all parameter types without the
optional delimiter:</tt><tt><br>
</tt><tt>DROP AGGREGATE IF EXISTS GROUP_CONCAT(TEXT); -- column,
delimiter=','</tt><tt><br>
</tt><tt>CREATE AGGREGATE GROUP_CONCAT(TEXT) -- column,
delimiter=','</tt><tt><br>
</tt><tt> (SFUNC=GROUP_CONCAT_ATOM,</tt><tt><br>
</tt><tt> STYPE=TEXT</tt><tt><br>
</tt><tt> );</tt><tt><br>
</tt><tt><br>
</tt><tt>DROP AGGREGATE IF EXISTS GROUP_CONCAT(INT8); -- column,
delimiter=','</tt><tt><br>
</tt><tt>CREATE AGGREGATE GROUP_CONCAT(INT8) -- column,
delimiter=','</tt><tt><br>
</tt><tt> (SFUNC=GROUP_CONCAT_ATOM,</tt><tt><br>
</tt><tt> STYPE=TEXT</tt><tt><br>
</tt><tt> );</tt><tt><br>
</tt></big><br>
</body>
</html>