Re: Aggregate function to build 2-d array - Mailing list pgsql-sql

Hi Steven,

I believe I saw something about a fix to array_append in the release notes
for V8.2.  Not sure if this helps.


Steven Murdoch-2 wrote:
> 
> I would like to aggregate several rows of a query, maintaining the
> relative order.  Is there an other way to achive the same result? I
> have an alternative construction, but I am not convinced it will work
> in all cases.
> 
> For example, with the setup below:
> 
> -- Concatenate elements of type t into array of type t[]
> CREATE AGGREGATE aconcat (
>     BASETYPE = anyelement,
>     SFUNC = array_append,
>     STYPE = anyarray,
>     INITCOND = '{}'
> );
> 
> -- Sample table
> CREATE TABLE a (
>   id INT PRIMARY KEY,
>   k TEXT NOT NULL,
>   v1 TEXT NOT NULL,
>   v2 TEXT NOT NULL);
> 
> -- Initialize data
> COPY a(id,k,v1,v2) FROM STDIN DELIMITER '|';
> 1|Alice|A|a
> 2|Bob|B|b
> 3|Charlie|C|c
> 4|Alice|A|a
> 5|Charlie|C|c
> \.
> 
> This query is what I would like to run:
>  SELECT aconcat(ARRAY[v1, v2]), k FROM a GROUP BY k;
> Which gives the result
>  "ERROR:  could not find array type for data type text[]"
> 
> I would have expected:
>   aconcat       |    k    
>  ---------------+---------
>   {{C,c},C,c}}  | Charlie
>   {{A,a},{A,a}  | Alice
>   {{B,b},{B,b}} | Bob
> 
> The problem I am hitting appears to be that the array_append()
> function does not accept 2-d arrays[1].
> 
>  SELECT array_append(ARRAY[ARRAY[1,2],ARRAY[2,3]], ARRAY[3,4]);
>  ERROR:  function array_append(integer[], integer[]) does not exist
> 
> The operator "||" does but I don't know how to use this to make a
> custom aggregate. Is there some way to do so, or achive the same
> result?
> 
>  SELECT ARRAY[ARRAY[1,2],ARRAY[2,3]] || ARRAY[3,4];
>        ?column?       
>  ---------------------
>   {{1,2},{2,3},{3,4}}
> 
> An alternative works in my test case:
>  SELECT aconcat(v1), aconcat(v2), k FROM a GROUP BY k;
>   aconcat | aconcat |    k    
>  ---------+---------+---------
>   {C,C}   | {c,c}   | Charlie
>   {A,A}   | {a,a}   | Alice
>   {B}     | {b}     | Bob
> 
> However I can't find any assurance that the order that each aggregate
> is formed will be the same in each column. Is this currently the case,
> and is it likely to remain so?
> 
> Thanks in advance,
> Steven.
> 
> [1] http://www.postgresql.org/docs/8.2/interactive/arrays.html
> 
> -- 
> w: http://www.cl.cam.ac.uk/users/sjm217/
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 
> 

-- 
View this message in context: http://www.nabble.com/Aggregate-function-to-build-2-d-array-tf2638930.html#a7369425
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.