A (final?) version using COALESCE (It wasn't too long to
post at the blog now; I am also posting here for belt
and suspenders reasons...):
-- group_concat.sql
-- permutation of GROUP_CONCAT parameter types with
delimiter parameter furnished:
CR
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
>
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/2
2013/8/6 Alvaro Herrera :
> 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;
>>
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 do
Hello
2013/8/3 immersive.ex...@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 h
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
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, post
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
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
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 wrote:
> immersive.ex...
immersive.ex...@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
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
13 matches
Mail list logo
