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
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
);