order function in aggregate

From: Michael Toews <mwtoews(at)sfu(dot)ca>
To: pgsql-sql(at)postgresql(dot)org
Subject: order function in aggregate
Date: 2008-07-30 15:31:57
Message-ID: 4890896D.2090009@sfu.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi,

I'm relatively new to object oriented databases and postgres (~ 4
months), so I'm not sure what is wrong with my custom aggregate
function. I'm using Postgres 8.3.3 (results are same on Windows and
Ubuntu versions).

Here is my data required to explain my problem:
------------------------------------------
CREATE TABLE aggr_test
(
id serial NOT NULL,
sub text,
grp text,
CONSTRAINT aggr_test_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

INSERT INTO aggr_test (id, sub, grp) VALUES (1, 'one', 'pom pom');
INSERT INTO aggr_test (id, sub, grp) VALUES (2, 'two', 'pom pom');
INSERT INTO aggr_test (id, sub, grp) VALUES (3, 'three', 'pom pom');
INSERT INTO aggr_test (id, sub, grp) VALUES (4, 'two', 'la la');
INSERT INTO aggr_test (id, sub, grp) VALUES (5, 'three', 'la la');
INSERT INTO aggr_test (id, sub, grp) VALUES (6, 'one', 'la la');

CREATE OR REPLACE FUNCTION concat(text, text)
RETURNS text AS
$BODY$DECLARE
t text;
BEGIN
IF $1 ISNULL OR $2 ISNULL THEN
t = COALESCE($1,$2);
ELSIF character_length($1) > 0 THEN
t = $1 ||', '|| $2;
ELSE
t = $2;
END IF;
RETURN t;
END;$BODY$
LANGUAGE 'plpgsql' IMMUTABLE
COST 100;

CREATE AGGREGATE commacat("text") (
SFUNC=concat,
STYPE=text,
SORTOP="<"
);
------------------------------------------
Now, I would like to group a summary of the strings in "sub", but they
must be ordered alphabetically. Here I attempt the SQL:

select commacat(sub), grp from aggr_test group by grp;

However, on my system it will output the "commacat" column as:

"one, two, three";"pom pom"
"two, three, one";"la la"

(notice that the items in the first column are ordered differently,
since the input values were entered in non-alphabetically)

This is where I get confused, since in the aggregate function, I have
specified `SORTOP="<"`, which according to the documentation
(http://www.postgresql.org/docs/8.3/interactive/sql-createaggregate.html)
"must be equivalent to":

SELECT sub FROM aggr_test ORDER BY sub USING <; -- LIMIT 1;

or

SELECT sub FROM aggr_test ORDER BY sub USING >; -- LIMIT 1;

(I've modified the example to show the desired effect of ASC and DESC
sorting, respectively)

So my question is: why is `SORTOP="<"` in my "commacat" aggregate
function not working? Any suggestions? Is this a bug?

Note: I am aware that I can achieve my goal using a subquery:

SELECT commacat(sub), grp FROM
(SELECT * FROM aggr_test ORDER BY grp, sub) AS foo
GROUP BY grp;

however, I'm looking to see if this ordering can done naturally within
the aggregate function.

Thanks in advance!
+mt

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Emi Lu 2008-07-30 15:32:08 Re: Get day name(Mon, Tue... Sun) and day number (1, 2...7) from a date
Previous Message Pavel Stehule 2008-07-30 15:23:41 Re: Get day name(Mon, Tue... Sun) and day number (1, 2...7) from a date