Обсуждение: Seamless replacement to MySQL's GROUP_CONCAT function...
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
);
immersive.excel@gmail.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
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.http://okbob.blogspot.com/2009/08/mysql-functions-for-postgresql.html
On Mon, Aug 5, 2013 at 10:04 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
immersive.excel@gmail.com escribió:> of queries?<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
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Regards,
Richard Broersma Jr.
Ahh! I was looking for that in the documentation!! I believe that is not mentioned on the function documentation page. Ihad tried ANY that works for other SQLs, but it had barfed on it. Let me test it and post the more efficient code, if itworks... ======================== On 08/05/2013 01:04 PM, Alvaro Herrera wrote: > immersive.excel@gmail.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. > >
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
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.
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...)
But while I am testing use of the ANYELEMENT, I will see if VARIADIC
can simplify things a tad more...
========================
On 08/05/2013 01:15 PM, Richard Broersma wrote:
<blockquote
cite="mid:CABvLTWEZyie9+zZZ9+xJNgaPsNLOL=OfOn2KROSrbryyQY2QEg@mail.gmail.com"
type="cite">
<meta http-equiv="Context-Type" content="text/html;
charset=ISO-8859-1">
For posterity that finalized function could be posted here:
<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
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 <<a
moz-do-not-send="true"
href="mailto:alvherre@2ndquadrant.com">alvherre@2ndquadrant.com>
wrote:
<a moz-do-not-send="true"
href="mailto:immersive.excel@gmail.com">immersive.excel@gmail.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 <a moz-do-not-send="true"
href="http://www.2ndQuadrant.com/">http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training &
Services
--
Sent via pgsql-general mailing list (<a
moz-do-not-send="true"
href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org)
To make changes to your subscription:
<a moz-do-not-send="true"
href="http://www.postgresql.org/mailpref/pgsql-general">http://www.postgresql.org/mailpref/pgsql-general
--
Regards,
Richard Broersma Jr.
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...
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
);
Hello
2013/8/3 immersive.excel@gmail.com <immersive.excel@gmail.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
> );
>
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
2013/8/6 Alvaro Herrera <alvherre@2ndquadrant.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
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.excel@gmail.com <immersive.excel@gmail.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 >> ); >>
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 .. >
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
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
);