Date: Wed, 18 May 2005 18:12:37 +0200
Message-ID: <87zmus8qdm.wl%felix.klee@inka.de>
From: "Felix E. Klee" <felix.klee@inka.de>
To: pgsql-sql@postgresql.org
Subject: Turning column into *sorted* array?
User-Agent: Wanderlust/2.14.0 (Africa) SEMI/1.14.6 (Maruoka) FLIM/1.14.7
	(=?ISO-8859-4?Q?Sanj=F2?=) APEL/10.6 Emacs/21.3
	(i486-slackware-linux-gnu) MULE/5.0 (SAKAKI)
MIME-Version: 1.0 (generated by SEMI 1.14.6 - "Maruoka")
Content-Type: text/plain; charset=US-ASCII

Suppose I have a table containing a column with integer values:

CREATE TABLE some_table (x int NOT NULL);
INSERT INTO some_table values(1);
INSERT INTO some_table values(5);
INSERT INTO some_table values(3);

Also, there is a view to that table (probably superfluous here, but it's
in the actual more complicated database design):

CREATE VIEW some_view AS SELECT x FROM some_table;

Now, the goal is to turn the column "x" of the view "some_view" into a
*sorted* array.  I tried the following code.  It works, but is it
reliable?  IOW: is it guaranteed that this gives me indeed a sorted
array?  If not, what's a good alternative?

CREATE AGGREGATE array_accum (
    sfunc = array_append,
    basetype = anyelement,
    stype = anyarray,
    initcond = '{}'
);

COMMENT ON AGGREGATE array_accum(anyelement) IS
'Found in section "33.9. User-Defined Aggregates" of the PostgreSQL 7.4.2
Documentation.';

SELECT array_accum(x) FROM (SELECT * FROM some_view ORDER BY x) AS tmp;

BTW, the best alternative (in terms of execution performance) that comes
into my mind is to create an aggregate that does the sorting right away
while the values "come in" from the rows.  But that'd probably take me
some time to get right.

-- 
Felix E. Klee