order function in aggregate

Поиск
Список
Период
Сортировка
От Michael Toews
Тема order function in aggregate
Дата
Msg-id 4890896D.2090009@sfu.ca
обсуждение исходный текст
Ответы Re: order function in aggregate  (Richard Huxton <dev@archonet.com>)
Список 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$DECLAREt text;
BEGINIF $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' IMMUTABLECOST 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



В списке pgsql-sql по дате отправления:

Предыдущее
От: Bricklen Anderson
Дата:
Сообщение: Re: Get day name(Mon, Tue... Sun) and day number (1, 2...7) from a date
Следующее
От: Emi Lu
Дата:
Сообщение: Re: Get day name(Mon, Tue... Sun) and day number (1, 2...7) from a date