<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">
I needed a GROUP_CONCAT to port some queries to postgres.<br>
<br>
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!<br>
<br>
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?<br>
<br>
And now the formulation of GROUP_CONCAT for postgres that accepts
either integer or string columns, and the optional delimiter:<br>
<br>
<big><big><tt>-- permutation of GROUP_CONCAT parameter types with
delimiter parameter furnished:<br>
CREATE OR REPLACE<br>
FUNCTION GROUP_CONCAT_ATOM(field1 TEXT, field2 TEXT, delimiter
TEXT)<br>
RETURNS TEXT AS $$<br>
BEGIN<br>
IF field1 IS NULL THEN<br>
RETURN field2;<br>
ELSIF field2 IS NULL THEN<br>
RETURN field1;<br>
ELSE<br>
RETURN field1||delimiter||field2;<br>
END IF;<br>
END;<br>
$$ LANGUAGE plpgsql;<br>
<br>
CREATE OR REPLACE<br>
FUNCTION GROUP_CONCAT_ATOM(field1 TEXT, field2 INT8, delimiter
TEXT)<br>
RETURNS TEXT AS $$<br>
BEGIN<br>
IF field1 IS NULL THEN<br>
IF field2 IS NULL THEN<br>
RETURN NULL;<br>
ELSE<br>
RETURN CAST(field2 AS TEXT);<br>
END IF;<br>
ELSIF field2 IS NULL THEN<br>
RETURN field1;<br>
ELSE<br>
RETURN field1||delimiter||CAST(field2 AS TEXT);<br>
END IF;<br>
END;<br>
$$ LANGUAGE plpgsql;<br>
<br>
CREATE OR REPLACE<br>
FUNCTION GROUP_CONCAT_ATOM(field1 INT8, field2 TEXT, delimiter
TEXT)<br>
RETURNS TEXT AS $$<br>
BEGIN<br>
IF field1 IS NULL THEN<br>
RETURN field2;<br>
ELSIF field2 IS NULL THEN<br>
IF field1 IS NULL THEN<br>
RETURN NULL;<br>
ELSE<br>
RETURN CAST(field1 AS TEXT);<br>
END IF;<br>
ELSE<br>
RETURN CAST(field1 AS TEXT)||delimiter||field2;<br>
END IF;<br>
END;<br>
$$ LANGUAGE plpgsql;<br>
<br>
CREATE OR REPLACE<br>
FUNCTION GROUP_CONCAT_ATOM(field1 INT8, field2 INT8, delimiter
TEXT)<br>
RETURNS TEXT AS $$<br>
BEGIN<br>
IF field1 IS NULL THEN<br>
IF field2 IS NULL THEN<br>
RETURN NULL;<br>
ELSE<br>
RETURN CAST(field2 AS TEXT);<br>
END IF;<br>
ELSIF field2 IS NULL THEN<br>
IF field1 IS NULL THEN<br>
RETURN NULL;<br>
ELSE<br>
RETURN CAST(field1 AS TEXT);<br>
END IF;<br>
ELSE<br>
RETURN CAST(field1 AS TEXT)||delimiter||CAST(field2 AS
TEXT);<br>
END IF;<br>
END;<br>
$$ LANGUAGE plpgsql;<br>
<br>
-- permutation of function arguments without delimiter
furnished:<br>
CREATE OR REPLACE<br>
FUNCTION GROUP_CONCAT_ATOM(field1 TEXT, field2 TEXT) --
delimiter=','<br>
RETURNS TEXT AS $$<br>
BEGIN<br>
IF field1 IS NULL THEN<br>
IF field2 IS NULL THEN<br>
RETURN NULL;<br>
ELSE<br>
RETURN CAST(field2 AS TEXT);<br>
END IF;<br>
ELSIF field2 IS NULL THEN<br>
RETURN field1;<br>
ELSE<br>
RETURN field1||','||field2;<br>
END IF;<br>
END;<br>
$$ LANGUAGE plpgsql;<br>
<br>
CREATE OR REPLACE<br>
FUNCTION GROUP_CONCAT_ATOM(field1 INT8, field2 INT8) --
delimiter=','<br>
RETURNS TEXT AS $$<br>
BEGIN<br>
IF field1 IS NULL THEN<br>
IF field2 IS NULL THEN<br>
RETURN NULL;<br>
ELSE<br>
RETURN CAST(field2 AS TEXT);<br>
END IF;<br>
ELSIF field2 IS NULL THEN<br>
IF field1 IS NULL THEN<br>
RETURN NULL;<br>
ELSE<br>
RETURN CAST(field1 AS TEXT);<br>
END IF;<br>
ELSE<br>
RETURN CAST(field1 AS TEXT)||','||CAST(field2 AS TEXT);<br>
END IF;<br>
END;<br>
$$ LANGUAGE plpgsql;<br>
<br>
CREATE OR REPLACE<br>
FUNCTION GROUP_CONCAT_ATOM(field1 INT8, field2 TEXT)<br>
RETURNS TEXT AS $$<br>
BEGIN<br>
IF field1 IS NULL THEN<br>
RETURN field2;<br>
ELSIF field2 IS NULL THEN<br>
IF field1 IS NULL THEN<br>
RETURN NULL;<br>
ELSE<br>
RETURN CAST(field1 AS TEXT);<br>
END IF;<br>
ELSE<br>
RETURN CAST(field1 AS TEXT)||','||field2;<br>
END IF;<br>
END;<br>
$$ LANGUAGE plpgsql;<br>
<br>
CREATE OR REPLACE<br>
FUNCTION GROUP_CONCAT_ATOM(field1 TEXT, field2 INT8) --
delimiter=','<br>
RETURNS TEXT AS $$<br>
BEGIN<br>
IF field1 IS NULL THEN<br>
IF field2 IS NULL THEN<br>
RETURN NULL;<br>
ELSE<br>
RETURN CAST(field2 AS TEXT);<br>
END IF;<br>
ELSIF field2 IS NULL THEN<br>
RETURN field1;<br>
ELSE<br>
RETURN field1||','||CAST(field2 AS TEXT);<br>
END IF;<br>
END;<br>
$$ LANGUAGE plpgsql;<br>
<br>
-- aggregates for all parameter types with delimiter:<br>
DROP AGGREGATE IF EXISTS GROUP_CONCAT(TEXT, TEXT); -- field,
delimiter<br>
CREATE AGGREGATE GROUP_CONCAT(TEXT, TEXT) -- field, delimiter<br>
(SFUNC=GROUP_CONCAT_ATOM,<br>
STYPE=TEXT<br>
);<br>
<br>
DROP AGGREGATE IF EXISTS GROUP_CONCAT(INT8, TEXT); -- field,
delimiter<br>
CREATE AGGREGATE GROUP_CONCAT(INT8, TEXT) -- field, delimiter<br>
(SFUNC=GROUP_CONCAT_ATOM,<br>
STYPE=TEXT<br>
);<br>
<br>
-- aggregates for all parameter types without the optional
delimiter:<br>
DROP AGGREGATE IF EXISTS GROUP_CONCAT(TEXT); -- field,
delimiter=','<br>
CREATE AGGREGATE GROUP_CONCAT(TEXT) -- field<br>
(SFUNC=GROUP_CONCAT_ATOM,<br>
STYPE=TEXT<br>
);<br>
<br>
DROP AGGREGATE IF EXISTS GROUP_CONCAT(INT8); -- field,
delimiter=','<br>
CREATE AGGREGATE GROUP_CONCAT(INT8) -- field<br>
(SFUNC=GROUP_CONCAT_ATOM,<br>
STYPE=TEXT<br>
);<br>
</tt></big></big><br>
</body>
</html>