Do AGGREGATES consistently use sort order?

From: "Webb Sprague" <webb(dot)sprague(at)gmail(dot)com>
To:
Cc: pgsql-general(at)postgresql(dot)org
Subject: Do AGGREGATES consistently use sort order?
Date: 2007-09-06 17:08:05
Message-ID: b11ea23c0709061008w531d5d82g21d2e231f2c24bac@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have the following query:

select array_accum(name) from (select name from placenames where
desig='crater' order by name desc) a;

with array_accum defined as:

CREATE AGGREGATE array_accum (
BASETYPE = anyelement,
SFUNC = array_append,
STYPE = anyarray,
INITCOND = '{}'
);

Can I count on this aggregate to take each new item in sorted order
when it adds it to the state vector? So that if I have the following:

oregon_2007_08_20=# select * from (select name from placenames where
desig='crater' order by name desc) a;

name
--------------------
Yapoah Crater
West Crater
Twin Craters
Timber Crater
Red Crater
Newberry Crater
Nash Crater
Mount Mazama
Millican Crater
Little Nash Crater
Le Conte Crater
Jordan Craters
Diamond Craters
Coffeepot Crater
Cayuse Crater
Black Crater
Big Hole
Belknap Crater
(18 rows)

I can always count on (note the order name):

\a
oregon_2007_08_20=# select array_accum(name) from (select name from
placenames where desig='crater' order by name desc) a;
array_accum
{"Yapoah Crater","West Crater","Twin Craters","Timber Crater","Red
Crater","Newberry Crater","Nash Crater","Mount Mazama","Millican
Crater","Little Nash Crater","Le Conte Crater","Jordan
Craters","Diamond Craters","Coffeepot Crater","Cayuse Crater","Black
Crater","Big Hole","Belknap Crater"}
(1 row)

I am interested in stitching a line out of points in postgis, but the
order/aggregate thing is a general question.

Thx
W

Responses

Browse pgsql-general by date

  From Date Subject
Next Message George Pavlov 2007-09-06 17:18:52 Re: Alias "all fields"?
Previous Message Max Zorloff 2007-09-06 16:08:21 Connection pooling