In the hope that it will be useful, I would like to place this code in the public domain:
I find the following code, which is very Postgresql-specific, defines some insanely useful aggregate functions:
Code:
-- Function form of || operator
CREATE OR REPLACE FUNCTION catenate(text,text) RETURNS text AS '
SELECT COALESCE($1 || $2,$1,$2,NULL)
' LANGUAGE SQL;
-- How to concatenate strings in an aggregate
CREATE AGGREGATE concatenate (
sfunc = catenate,
basetype = text,
stype = text,
initcond = ''
);
-- || function but with a two-element array
CREATE OR REPLACE FUNCTION merger(text,anyarray) RETURNS text AS '
SELECT COALESCE($1 || $2[1] || $2[2],$1,$2[2],NULL)
' LANGUAGE SQL;
-- join(val,cols) aggregate
CREATE AGGREGATE merge (
sfunc = merger,
basetype = anyarray,
stype = text
);
The first bit is basically what you expect SUM() to do if you pass it text instead of numbers. (You can even call it SUM if you like by changing the name from
concatenate).
After running this snippet on a Postgresql database (with functions enabled and assuming that the user has appropriate CREATE privalages), you can then do things like this:
Code:
CREATE TEMPORARY TABLE article(words text);
INSERT INTO article VALUES ('hello');
INSERT INTO article VALUES ('to');
INSERT INTO article VALUES ('you');
INSERT INTO article VALUES (NULL);
SELECT concatenate(words || ' ') FROM article;
The final SELECT will return the string 'hello to you '
I find this useful in the case that you have two tables with a 1:N relationship (say, newspapers and articles) and you want to make up the text in a view of the newspaper from the articles.
Now what we really want is something to do a Perl-style join, so that one could run:
Code:
SELECT merge(ARRAY[' ', words]) FROM article;
and get 'hello to you' without the space at the end. That's what the second aggregate does, although it should be noted that it's less efficient because you need to create an array for every element, just to pass the join text, which should really be stored in the initial condition somehow . But Postgresql doesn't support that as far as I can tell; someone please correct me if I'm wrong.
