Seamless replacement to MySQL's GROUP_CONCAT function...

Lists: pgsql-general
From: "immersive(dot)excel(at)gmail(dot)com" <immersive(dot)excel(at)gmail(dot)com>
To: x3v0-pgsql(at)yahoo(dot)com
Cc: pgsql-general(at)postgresql(dot)org, David Fetter <david(at)fetter(dot)org>
Subject: Seamless replacement to MySQL's GROUP_CONCAT function...
Date: 2013-08-03 21:52:32
Message-ID: [email protected]
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-general

<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>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURNS TEXT AS $$<br>
BEGIN<br>
IF field1 IS NULL THEN<br>
&nbsp;&nbsp; RETURN field2;<br>
ELSIF field2 IS NULL THEN<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN field1;<br>
ELSE<br>
&nbsp;&nbsp;&nbsp;&nbsp; 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>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURNS TEXT AS $$<br>
BEGIN<br>
IF field1 IS NULL THEN<br>
&nbsp;&nbsp; IF field2 IS NULL THEN<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN NULL;<br>
&nbsp;&nbsp; ELSE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN CAST(field2 AS TEXT);<br>
&nbsp;&nbsp; END IF;<br>
ELSIF field2 IS NULL THEN<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN field1;<br>
ELSE<br>
&nbsp;&nbsp;&nbsp;&nbsp; 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>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURNS TEXT AS $$<br>
BEGIN<br>
IF field1 IS NULL THEN<br>
&nbsp;&nbsp; RETURN field2;<br>
ELSIF field2 IS NULL THEN<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IF field1 IS NULL THEN<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN NULL;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ELSE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN CAST(field1 AS TEXT);<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END IF;<br>
ELSE<br>
&nbsp;&nbsp;&nbsp;&nbsp; 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>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURNS TEXT AS $$<br>
BEGIN<br>
IF field1 IS NULL THEN<br>
&nbsp;&nbsp; IF field2 IS NULL THEN<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN NULL;<br>
&nbsp;&nbsp; ELSE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN CAST(field2 AS TEXT);<br>
&nbsp;&nbsp; END IF;<br>
ELSIF field2 IS NULL THEN<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IF field1 IS NULL THEN<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN NULL;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ELSE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN CAST(field1 AS TEXT);<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END IF;<br>
ELSE<br>
&nbsp;&nbsp;&nbsp;&nbsp; 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>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURNS TEXT AS $$<br>
BEGIN<br>
IF field1 IS NULL THEN<br>
&nbsp;&nbsp; IF field2 IS NULL THEN<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN NULL;<br>
&nbsp;&nbsp; ELSE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN CAST(field2 AS TEXT);<br>
&nbsp;&nbsp; END IF;<br>
ELSIF field2 IS NULL THEN<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN field1;<br>
ELSE<br>
&nbsp;&nbsp;&nbsp;&nbsp; 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>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURNS TEXT AS $$<br>
BEGIN<br>
IF field1 IS NULL THEN<br>
&nbsp;&nbsp; IF field2 IS NULL THEN<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN NULL;<br>
&nbsp;&nbsp; ELSE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN CAST(field2 AS TEXT);<br>
&nbsp;&nbsp; END IF;<br>
ELSIF field2 IS NULL THEN<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IF field1 IS NULL THEN<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN NULL;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ELSE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN CAST(field1 AS TEXT);<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END IF;<br>
ELSE<br>
&nbsp;&nbsp;&nbsp;&nbsp; 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>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURNS TEXT AS $$<br>
BEGIN<br>
IF field1 IS NULL THEN<br>
&nbsp;&nbsp; RETURN field2;<br>
ELSIF field2 IS NULL THEN<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IF field1 IS NULL THEN<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN NULL;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ELSE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN CAST(field1 AS TEXT);<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END IF;<br>
ELSE<br>
&nbsp;&nbsp;&nbsp;&nbsp; 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>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURNS TEXT AS $$<br>
BEGIN<br>
IF field1 IS NULL THEN<br>
&nbsp;&nbsp; IF field2 IS NULL THEN<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN NULL;<br>
&nbsp;&nbsp; ELSE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN CAST(field2 AS TEXT);<br>
&nbsp;&nbsp; END IF;<br>
ELSIF field2 IS NULL THEN<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN field1;<br>
ELSE<br>
&nbsp;&nbsp;&nbsp;&nbsp; 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>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (SFUNC=GROUP_CONCAT_ATOM,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; STYPE=TEXT<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; );<br>
<br>
DROP AGGREGATE IF EXISTS GROUP_CONCAT(INT8, TEXT); -- field,
delimiter<br>
CREATE AGGREGATE GROUP_CONCAT(INT8, TEXT) -- field, delimiter<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (SFUNC=GROUP_CONCAT_ATOM,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; STYPE=TEXT<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; );<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>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (SFUNC=GROUP_CONCAT_ATOM,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; STYPE=TEXT<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; );<br>
<br>
DROP AGGREGATE IF EXISTS GROUP_CONCAT(INT8); -- field,
delimiter=','<br>
CREATE AGGREGATE GROUP_CONCAT(INT8) -- field<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (SFUNC=GROUP_CONCAT_ATOM,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; STYPE=TEXT<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; );<br>
</tt></big></big><br>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 10.4 KB
From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: "immersive(dot)excel(at)gmail(dot)com" <immersive(dot)excel(at)gmail(dot)com>
Cc: x3v0-pgsql(at)yahoo(dot)com, 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-05 17:04:43
Message-ID: [email protected]
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-general

immersive(dot)excel(at)gmail(dot)com escribió:

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

I don't think you need all the variations; you should be able to make
them all work with a single set of functions, taking ANYELEMENT instead
of text/int8/int4 etc.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

From: Richard Broersma <richard(dot)broersma(at)gmail(dot)com>
To: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
Cc: "immersive(dot)excel(at)gmail(dot)com" <immersive(dot)excel(at)gmail(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-05 17:15:08
Message-ID: CABvLTWEZyie9+zZZ9+xJNgaPsNLOL=OfOn2KROSrbryyQY2QEg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-general

For posterity that finalized function could be posted here:

http://okbob.blogspot.com/2009/08/mysql-functions-for-postgresql.html

There's already a GROUP_CONCAT, listed there, but I guess this one was
lacking in some way.

On Mon, Aug 5, 2013 at 10:04 AM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>wrote:

> immersive(dot)excel(at)gmail(dot)com escribió:
>
> > 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>
>
> I don't think you need all the variations; you should be able to make
> them all work with a single set of functions, taking ANYELEMENT instead
> of text/int8/int4 etc.
>
>
> --
> Álvaro Herrera http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Regards,
Richard Broersma Jr.

From: "immersive(dot)excel(at)gmail(dot)com" <immersive(dot)excel(at)gmail(dot)com>
To: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
Cc: x3v0-pgsql(at)yahoo(dot)com, 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-05 17:55:59
Message-ID: [email protected]
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-general

Ahh! I was looking for that in the documentation!! I believe that is not mentioned on the function documentation page. I had tried ANY that works for other SQLs, but it had barfed on it. Let me test it and post the more efficient code, if it works...

========================
On 08/05/2013 01:04 PM, Alvaro Herrera wrote:
> immersive(dot)excel(at)gmail(dot)com escribió:
>
>> 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>
> I don't think you need all the variations; you should be able to make
> them all work with a single set of functions, taking ANYELEMENT instead
> of text/int8/int4 etc.
>
>

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-05 18:06:12
Message-ID: [email protected]
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-general

<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
I believe his VARIADIC might cover the optional delimiter use, but
he suggests using an array aggregation function instead -which does
not return a string list, so you would still have to rework the
original queries.<br>
<br>
Now you could write a GROUP_CONCAT aggregate that uses a final
function to convert the array to a string list -but correct me if
I'm wrong, I believe the way I am concatenating as I go uses less
resources (an array of, say, 5000 elements versus a string...)<br>
<br>
But while I am testing use of the ANYELEMENT, I will see if VARIADIC
can simplify things a tad more...<br>
<br>
========================<br>
On 08/05/2013 01:15 PM, Richard Broersma wrote:
<blockquote
cite="mid:CABvLTWEZyie9+zZZ9+xJNgaPsNLOL=OfOn2KROSrbryyQY2QEg(at)mail(dot)gmail(dot)com"
type="cite">
<meta http-equiv="Context-Type" content="text/html;
charset=ISO-8859-1">
<div>
<div>For posterity that finalized function could be posted here:<br>
<br>
<a moz-do-not-send="true"
href="http://okbob.blogspot.com/2009/08/mysql-functions-for-postgresql.html">http://okbob.blogspot.com/2009/08/mysql-functions-for-postgresql.html</a>&nbsp;
<br>
<br>
</div>
There's already a GROUP_CONCAT, listed there, but I guess this
one was lacking in some way.<br>
</div>
<div><br>
<br>
<div>On Mon, Aug 5, 2013 at 10:04 AM, Alvaro Herrera <span>&lt;<a
moz-do-not-send="true"
href="mailto:alvherre(at)2ndquadrant(dot)com">alvherre(at)2ndquadrant(dot)com</a>&gt;</span>
wrote:<br>
<blockquote><a moz-do-not-send="true"
href="mailto:immersive(dot)excel(at)gmail(dot)com">immersive(dot)excel(at)gmail(dot)com</a>
escribi&oacute;:<br>
<div><br>
&gt; &nbsp; &nbsp; Note: I found some close-but-no cigar aggregates
shared online, but<br>
&gt; &nbsp; &nbsp; they would not accept integer arguments, nor
would they handle the<br>
&gt; &nbsp; &nbsp; optionally furnished delimiter. People would
suggesting casting the<br>
&gt; &nbsp; &nbsp; argument to the pseudo-GROUP_CONCAT. Again:
Rewrite perhaps hundreds<br>
</div>
&gt; &nbsp; &nbsp; of queries?&lt;br&gt;<br>
<br>
I don't think you need all the variations; you should be
able to make<br>
them all work with a single set of functions, taking
ANYELEMENT instead<br>
of text/int8/int4 etc.<br>
<span> <br>
<br>
--<br>
&Aacute;lvaro Herrera &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<a moz-do-not-send="true"
href="http://www.2ndQuadrant.com/">http://www.2ndQuadrant.com/</a><br>
PostgreSQL Development, 24x7 Support, Training &amp;
Services<br>
<br>
<br>
--<br>
Sent via pgsql-general mailing list (<a
moz-do-not-send="true"
href="mailto:pgsql-general(at)postgresql(dot)org">pgsql-general(at)postgresql(dot)org</a>)<br>
To make changes to your subscription:<br>
<a moz-do-not-send="true"
href="http://www.postgresql.org/mailpref/pgsql-general">http://www.postgresql.org/mailpref/pgsql-general</a><br>
</span></blockquote>
</div>
<br>
<br>
<br>
-- <br>
Regards,<br>
Richard Broersma Jr.
</div>
</blockquote>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 3.8 KB
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-05 23:56:48
Message-ID: [email protected]
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-general

Well after testing, this is what I found:

When you try to use ANYELEMENT parameters, and even just a VARIADIC
TEXT[] parameter to support the optional delimiter argument:

FUNCTION GROUP_CONCAT_ATOM(ANYELEMENT, ANYELEMENT, VARIADIC
delimiters TEXT[])

when you go to create the aggregates, postgres runs through a
permutation of the argument types, just to be sure that all functions
are accounted for. And at that point postgres complains:

function group_concat_atom(text, anyelement, text) does not exist

You would _think_ that the function definition above would cover this
case, but it for whatever reason it does not.

In the process of trying variations, I see that I used the deficient
nomenclature of "field" instead of "column" in the previous code. I will
fix that and post at the other site listed above; but other than that,
the code works and I welcome anyone to come up with a more compact
version that allows it to remain as flexible as it is now...

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: [email protected]
Views: Whole Thread | Raw Message | Download mbox | Resend email
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: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: "immersive(dot)excel(at)gmail(dot)com" <immersive(dot)excel(at)gmail(dot)com>
Cc: x3v0-pgsql(at)yahoo(dot)com, 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 04:49:48
Message-ID: CAFj8pRA1TNbnsBZ_sDyie-CdPpyL=ehLaiZnK3ZOmqZUwGGG9g@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-general

Hello

2013/8/3 immersive(dot)excel(at)gmail(dot)com <immersive(dot)excel(at)gmail(dot)com>:
> 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;

your code will be significantly faster when you don't use a classic C
programming style and use a COALESCE function. PL/pgSQL is a
interpreted language and is necessary to minimize number of
instruction.

you code can be translated to

CREATE OR REPLACE
FUNCTION GROUP_CONCAT_ATOM(field1 TEXT, field2 TEXT, delimiter TEXT)
RETURNS TEXT AS $$
BEGIN
RETURN COALESCE(field1||delimiter||field2, field2, field1);
END;
$$ LANGUAGE plpgsql;

Regards

Pavel

p.s. speed is in this use case important, because you execute this
function for every row

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

From: "immersive(dot)excel(at)gmail(dot)com" <immersive(dot)excel(at)gmail(dot)com>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: x3v0-pgsql(at)yahoo(dot)com, 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 05:10:35
Message-ID: [email protected]
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-general

That is good and I will effect that change here; but as far as I can see
you will still need to permute the types of the arguments in that set of
functions in order for the aggregates to accept an int argument and an
optional text argument, and zap out a TEXT...

========================
On 08/06/2013 12:49 AM, Pavel Stehule wrote:
> Hello
>
> 2013/8/3 immersive(dot)excel(at)gmail(dot)com <immersive(dot)excel(at)gmail(dot)com>:
>> 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;
> your code will be significantly faster when you don't use a classic C
> programming style and use a COALESCE function. PL/pgSQL is a
> interpreted language and is necessary to minimize number of
> instruction.
>
> you code can be translated to
>
> CREATE OR REPLACE
> FUNCTION GROUP_CONCAT_ATOM(field1 TEXT, field2 TEXT, delimiter TEXT)
> RETURNS TEXT AS $$
> BEGIN
> RETURN COALESCE(field1||delimiter||field2, field2, field1);
> END;
> $$ LANGUAGE plpgsql;
>
> Regards
>
> Pavel
>
> p.s. speed is in this use case important, because you execute this
> function for every row
>
>
>> 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
>> );
>>

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: "immersive(dot)excel(at)gmail(dot)com" <immersive(dot)excel(at)gmail(dot)com>, x3v0-pgsql(at)yahoo(dot)com, 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 05:26:12
Message-ID: [email protected]
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-general

Pavel Stehule escribió:

> you code can be translated to
>
> CREATE OR REPLACE
> FUNCTION GROUP_CONCAT_ATOM(field1 TEXT, field2 TEXT, delimiter TEXT)
> RETURNS TEXT AS $$
> BEGIN
> RETURN COALESCE(field1||delimiter||field2, field2, field1);
> END;
> $$ LANGUAGE plpgsql;

Actually you don't even need plpgsql for this, do you ..

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

From: "immersive(dot)excel(at)gmail(dot)com" <immersive(dot)excel(at)gmail(dot)com>
To: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
Cc: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, x3v0-pgsql(at)yahoo(dot)com, 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 05:41:06
Message-ID: [email protected]
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-general

You're saying as oppose to straight SQL? I don't think so; but I had
defined it as such just in case there was some functional benefit that I
might be unaware of...

========================
On 08/06/2013 01:26 AM, Alvaro Herrera wrote:
> Pavel Stehule escribió:
>
>> you code can be translated to
>>
>> CREATE OR REPLACE
>> FUNCTION GROUP_CONCAT_ATOM(field1 TEXT, field2 TEXT, delimiter TEXT)
>> RETURNS TEXT AS $$
>> BEGIN
>> RETURN COALESCE(field1||delimiter||field2, field2, field1);
>> END;
>> $$ LANGUAGE plpgsql;
> Actually you don't even need plpgsql for this, do you ..
>

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
Cc: "immersive(dot)excel(at)gmail(dot)com" <immersive(dot)excel(at)gmail(dot)com>, x3v0-pgsql(at)yahoo(dot)com, 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 05:47:47
Message-ID: CAFj8pRD0-J-_c_bL7AAazuo+W4QhcRfJjOFkOpKpPnXRUjNUXw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-general

2013/8/6 Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>:
> Pavel Stehule escribió:
>
>> you code can be translated to
>>
>> CREATE OR REPLACE
>> FUNCTION GROUP_CONCAT_ATOM(field1 TEXT, field2 TEXT, delimiter TEXT)
>> RETURNS TEXT AS $$
>> BEGIN
>> RETURN COALESCE(field1||delimiter||field2, field2, field1);
>> END;
>> $$ LANGUAGE plpgsql;
>
> Actually you don't even need plpgsql for this, do you ..

There is better optimized plpgsql - not sure, why - probably, so there
is not possible inlining .. and start of SQL engine is little bit
slower than start of plpgsql.

postgres=# create table f(a text, b int);
CREATE TABLE
postgres=# insert into f select 'Ahoj', random()*100 from
generate_series(1,10000);

select group_concat(a,',') from f group by b;

PL/pgSQL .. 36 ms
SQL .. 49 ms

Regards

Pavel Stehule

>
> --
> Álvaro Herrera http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services

From: "immersive(dot)excel(at)gmail(dot)com" <immersive(dot)excel(at)gmail(dot)com>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, x3v0-pgsql(at)yahoo(dot)com, 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 16:08:54
Message-ID: [email protected]
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-general

<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 (&lt;grin&gt;It wasn't too long to
post at the blog now&lt;/grin&gt;; 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>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 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>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 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>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 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>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 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>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 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>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 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>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 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>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 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>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (SFUNC=GROUP_CONCAT_ATOM,</tt><tt><br>
</tt><tt>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; STYPE=TEXT</tt><tt><br>
</tt><tt>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; );</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>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (SFUNC=GROUP_CONCAT_ATOM,</tt><tt><br>
</tt><tt>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; STYPE=TEXT</tt><tt><br>
</tt><tt>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; );</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>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (SFUNC=GROUP_CONCAT_ATOM,</tt><tt><br>
</tt><tt>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; STYPE=TEXT</tt><tt><br>
</tt><tt>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; );</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>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (SFUNC=GROUP_CONCAT_ATOM,</tt><tt><br>
</tt><tt>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; STYPE=TEXT</tt><tt><br>
</tt><tt>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; );</tt><tt><br>
</tt></big><br>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 7.3 KB