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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
