From: chrisj <chrisj.wood@sympatico.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Aggregate function to build 2-d array
Date: Wed, 15 Nov 2006 16:32:56 -0800 (PST)
Message-ID: <7369425.post@talk.nabble.com> (raw)
In-Reply-To: <20061115204249.GD17915@cl.cam.ac.uk>
References: <20061115204249.GD17915@cl.cam.ac.uk>
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.
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: chrisj.wood@sympatico.ca
Subject: Re: Aggregate function to build 2-d array
In-Reply-To: <7369425.post@talk.nabble.com>
* 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