Thread: Turning column into *sorted* array?

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

> SELECT array_accum(x) FROM (SELECT * FROM some_view ORDER BY x) AS tmp;
If you're using integers, you could use the int_array_accum or something  from the intarray module which is a lot
faster.Ibelieve intarray also has a function for sorting integer arrays...
 

>
> 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.
>

At Wed, 18 May 2005 19:54:08 +0200,
PFC wrote:
> > SELECT array_accum(x) FROM (SELECT * FROM some_view ORDER BY x) AS tmp;
> 
>     If you're using integers, you could use the int_array_accum or
> something from the intarray module which is a lot faster.  I believe
> intarray also has a function for sorting integer arrays...

No, unfortunately I'm using strings in "real-life" (my example is
perhaps a bit over-simplified).

Let me make my original question a bit more precise: How do I best
transform a column of varchars into a *sorted* array?

-- 
Felix E. Klee