pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAggregate function to build 2-d array
2+ messages / 2 participants
[nested] [flat]
* Aggregate function to build 2-d array
@ 2006-11-15 20:42 Steven Murdoch <psql+Steven.Murdoch@cl.cam.ac.uk>
0 siblings, 1 reply; 2+ messages in thread
From: Steven Murdoch @ 2006-11-15 20:42 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Aggregate function to build 2-d array
@ 2006-11-16 00:32 chrisj <chrisj.wood@sympatico.ca>
parent: Steven Murdoch <psql+Steven.Murdoch@cl.cam.ac.uk>
0 siblings, 0 replies; 2+ messages in thread
From: chrisj @ 2006-11-16 00:32 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-11-16 00:32 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-15 20:42 Aggregate function to build 2-d array Steven Murdoch <psql+Steven.Murdoch@cl.cam.ac.uk>
2006-11-16 00:32 ` chrisj <chrisj.wood@sympatico.ca>
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