Turning column into *sorted* array?

From: "Felix E(dot) Klee" <felix(dot)klee(at)inka(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: Turning column into *sorted* array?
Date: 2005-05-18 16:12:37
Message-ID: 87zmus8qdm.wl%felix.klee@inka.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Peter Bense 2005-05-18 17:40:50 Help with views/rules...
Previous Message Tom Lane 2005-05-18 16:00:03 Re: Meaning of ERROR: tuple concurrently updated