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
