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