From: Steven Murdoch <psql+Steven.Murdoch@cl.cam.ac.uk>
To: pgsql-sql@postgresql.org
Subject: Aggregate function to build 2-d array
Date: Wed, 15 Nov 2006 20:42:49 +0000
Message-ID: <20061115204249.GD17915@cl.cam.ac.uk> (raw)
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: psql+Steven.Murdoch@cl.cam.ac.uk
Subject: Re: Aggregate function to build 2-d array
In-Reply-To: <20061115204249.GD17915@cl.cam.ac.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox