Date: Wed, 15 Nov 2006 20:42:49 +0000
From: Steven Murdoch <psql+Steven.Murdoch@cl.cam.ac.uk>
To: pgsql-sql@postgresql.org
Subject: Aggregate function to build 2-d array
Message-ID: <20061115204249.GD17915@cl.cam.ac.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.9i

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/