Thread: Aggregate function to build 2-d array

Aggregate function to build 2-d array

From

Steven Murdoch

Date:

15 November 2006, 17:01:29

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
notexist
 

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/

Re: Aggregate function to build 2-d array

From

chrisj

Date:

15 November 2006, 20:33:01

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.