From: | "immersive(dot)excel(at)gmail(dot)com" <immersive(dot)excel(at)gmail(dot)com> |
---|---|
To: | Richard Broersma <richard(dot)broersma(at)gmail(dot)com> |
Cc: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, x3v0-pgsql(at)yahoo(dot)com, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>, David Fetter <david(at)fetter(dot)org> |
Subject: | Re: Seamless replacement to MySQL's GROUP_CONCAT function... |
Date: | 2013-08-06 00:24:49 |
Message-ID: | 52004251.3090903@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
No luck on posting at that blog; comments are limited to X characters.
Here is the final version with the minor update to the variable names
and comments:
-- 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
IF column1 IS NULL THEN
RETURN column2;
ELSIF column2 IS NULL THEN
RETURN column1;
ELSE
RETURN column1||delimiter||column2;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE
FUNCTION GROUP_CONCAT_ATOM(column1 TEXT, column2 INT8, delimiter TEXT)
RETURNS TEXT AS $$
BEGIN
IF column1 IS NULL THEN
IF column2 IS NULL THEN
RETURN NULL;
ELSE
RETURN CAST(column2 AS TEXT);
END IF;
ELSIF column2 IS NULL THEN
RETURN column1;
ELSE
RETURN column1||delimiter||CAST(column2 AS TEXT);
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE
FUNCTION GROUP_CONCAT_ATOM(column1 INT8, column2 TEXT, delimiter TEXT)
RETURNS TEXT AS $$
BEGIN
IF column1 IS NULL THEN
RETURN column2;
ELSIF column2 IS NULL THEN
IF column1 IS NULL THEN
RETURN NULL;
ELSE
RETURN CAST(column1 AS TEXT);
END IF;
ELSE
RETURN CAST(column1 AS TEXT)||delimiter||column2;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE
FUNCTION GROUP_CONCAT_ATOM(column1 INT8, column2 INT8, delimiter TEXT)
RETURNS TEXT AS $$
BEGIN
IF column1 IS NULL THEN
IF column2 IS NULL THEN
RETURN NULL;
ELSE
RETURN CAST(column2 AS TEXT);
END IF;
ELSIF column2 IS NULL THEN
IF column1 IS NULL THEN
RETURN NULL;
ELSE
RETURN CAST(column1 AS TEXT);
END IF;
ELSE
RETURN CAST(column1 AS TEXT)||delimiter||CAST(column2 AS TEXT);
END IF;
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
IF column1 IS NULL THEN
IF column2 IS NULL THEN
RETURN NULL;
ELSE
RETURN CAST(column2 AS TEXT);
END IF;
ELSIF column2 IS NULL THEN
RETURN column1;
ELSE
RETURN column1||','||column2;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE
FUNCTION GROUP_CONCAT_ATOM(column1 INT8, column2 INT8) -- delimiter=','
RETURNS TEXT AS $$
BEGIN
IF column1 IS NULL THEN
IF column2 IS NULL THEN
RETURN NULL;
ELSE
RETURN CAST(column2 AS TEXT);
END IF;
ELSIF column2 IS NULL THEN
IF column1 IS NULL THEN
RETURN NULL;
ELSE
RETURN CAST(column1 AS TEXT);
END IF;
ELSE
RETURN CAST(column1 AS TEXT)||','||CAST(column2 AS TEXT);
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE
FUNCTION GROUP_CONCAT_ATOM(column1 INT8, column2 TEXT) -- delimiter=','
RETURNS TEXT AS $$
BEGIN
IF column1 IS NULL THEN
RETURN column2;
ELSIF column2 IS NULL THEN
IF column1 IS NULL THEN
RETURN NULL;
ELSE
RETURN CAST(column1 AS TEXT);
END IF;
ELSE
RETURN CAST(column1 AS TEXT)||','||column2;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE
FUNCTION GROUP_CONCAT_ATOM(column1 TEXT, column2 INT8) -- delimiter=','
RETURNS TEXT AS $$
BEGIN
IF column1 IS NULL THEN
IF column2 IS NULL THEN
RETURN NULL;
ELSE
RETURN CAST(column2 AS TEXT);
END IF;
ELSIF column2 IS NULL THEN
RETURN column1;
ELSE
RETURN column1||','||CAST(column2 AS TEXT);
END IF;
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
);
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2013-08-06 00:30:21 | Re: dblink / Insert several records into remote table |
Previous Message | immersive.excel@gmail.com | 2013-08-05 23:56:48 | Re: Seamless replacement to MySQL's GROUP_CONCAT function... |