-- http://www.postgresql.org/docs/current/interactive/sql-createaggregate.html
-- http://www.postgresql.org/docs/current/interactive/xaggr.html
-- http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks

/*
 * Array sortieren
 */
CREATE OR REPLACE 
  FUNCTION ARRAY_SORT_ASC (ANYARRAY) RETURNS ANYARRAY
LANGUAGE SQL IMMUTABLE
AS
$_SQL_$
SELECT
ARRAY
(
  SELECT $1[s.i] AS elem
  FROM
    GENERATE_SERIES(ARRAY_LOWER($1,1), ARRAY_UPPER($1,1)) AS s(i)
  ORDER BY elem ASC
)
$_SQL_$
;

CREATE OR REPLACE 
  FUNCTION ARRAY_SORT_DESC (ANYARRAY) RETURNS ANYARRAY 
LANGUAGE SQL IMMUTABLE
AS 
$_SQL_$
SELECT 
ARRAY
(
  SELECT $1[s.i] AS elem
  FROM
    GENERATE_SERIES(ARRAY_LOWER($1,1), ARRAY_UPPER($1,1)) AS s(i)
  ORDER BY elem DESC
)
$_SQL_$
;

/*
 * Array in String (TEXT) umwandeln und gegebenfalls vorher sortieren
 * TEXT in PostgreSQL = CLOB in Standard-SQL
 */
CREATE OR REPLACE
  FUNCTION ARRAY_TO_TEXT_COMMA(a ANYARRAY) RETURNS TEXT 
LANGUAGE PLPGSQL IMMUTABLE
AS 
$_SQL_$
BEGIN 
  RETURN ARRAY_TO_STRING(a, ', ');
END
$_SQL_$
;

CREATE OR REPLACE
  FUNCTION ARRAY_SORT_ASC_TO_TEXT_COMMA (a ANYARRAY) RETURNS TEXT
LANGUAGE PLPGSQL IMMUTABLE
AS
$_SQL_$
BEGIN 
  RETURN ARRAY_TO_STRING(ARRAY_SORT_ASC(a), ', ');
END
$_SQL_$
;

CREATE OR REPLACE
  FUNCTION ARRAY_SORT_DESC_TO_TEXT_COMMA (a ANYARRAY) RETURNS TEXT
LANGUAGE PLPGSQL IMMUTABLE
AS
$_SQL_$
BEGIN
  RETURN ARRAY_TO_STRING(ARRAY_SORT_DESC(a), ', ');
END
$_SQL_$
;



/*
 * Aggregation: Aufsammeln von Werten in einem Array
 *
 * Definition einer neuen Aggregations-Operation: ARRAY_AGGREGATE
 */

CREATE OR REPLACE
  FUNCTION ARRAY_APPEND_NO_NULLS(ANYARRAY, ANYELEMENT) 
  RETURNS ANYARRAY
LANGUAGE SQL IMMUTABLE
AS
$_SQL_$
SELECT
CASE
   WHEN $1 IS NULL AND $2 IS NULL
       THEN '{}'
   WHEN $1 IS NULL
       THEN ARRAY[$2]
   WHEN $2 IS NULL
       THEN $1
   ELSE
       ARRAY_APPEND($1,$2)
END;
$_SQL_$
;

CREATE OR REPLACE 
  FUNCTION ARRAY_APPEND_NO_NULLS_DISTINCT(ANYARRAY, ANYELEMENT) RETURNS ANYARRAY
LANGUAGE SQL IMMUTABLE
AS 
$_SQL_$
SELECT
CASE
   WHEN $1 IS NULL AND $2 IS NULL
       THEN '{}'
   WHEN $1 IS NULL
       THEN ARRAY[$2]
   WHEN $2 IS NULL
       THEN $1
   WHEN $2 = ANY($1) -- Duplikat-Elimination, kostet Zeit!
       THEN $1
   ELSE
       ARRAY_APPEND($1,$2) 
END;
$_SQL_$
;

DROP AGGREGATE IF EXISTS ARRAY_AGGREGATE (ANYELEMENT) ; 
CREATE AGGREGATE ARRAY_AGGREGATE (ANYELEMENT)
(
   SFUNC = ARRAY_APPEND_NO_NULLS,
   STYPE = ANYARRAY,
   INITCOND = '{}'
)
; 

DROP AGGREGATE IF EXISTS ARRAY_AGGREGATE_ASC (ANYELEMENT) ; 
CREATE AGGREGATE ARRAY_AGGREGATE_ASC (ANYELEMENT)
(
   SFUNC = ARRAY_APPEND_NO_NULLS,
   STYPE = ANYARRAY,
   INITCOND = '{}',
   FINALFUNC = ARRAY_SORT_ASC
)
;

DROP AGGREGATE IF EXISTS ARRAY_AGGREGATE_DESC (ANYELEMENT) ; 
CREATE AGGREGATE ARRAY_AGGREGATE_DESC (ANYELEMENT)
(
   SFUNC = ARRAY_APPEND_NO_NULLS,
   STYPE = ANYARRAY,
   INITCOND = '{}',
   FINALFUNC = ARRAY_SORT_DESC
)
;

DROP AGGREGATE IF EXISTS ARRAY_AGGREGATE_DISTINCT (ANYELEMENT);
CREATE AGGREGATE ARRAY_AGGREGATE_DISTINCT (ANYELEMENT)
(
   SFUNC = ARRAY_APPEND_NO_NULLS_DISTINCT,
   STYPE = ANYARRAY,
   INITCOND = '{}'
)
; 

DROP AGGREGATE IF EXISTS ARRAY_AGGREGATE_DISTINCT_ASC (ANYELEMENT);
CREATE AGGREGATE ARRAY_AGGREGATE_DISTINCT_ASC (ANYELEMENT)
(
   SFUNC = ARRAY_APPEND_NO_NULLS_DISTINCT,
   STYPE = ANYARRAY,
   INITCOND = '{}',
   FINALFUNC = ARRAY_SORT_ASC
)
;

DROP AGGREGATE IF EXISTS ARRAY_AGGREGATE_DISTINCT_DESC (ANYELEMENT);
CREATE AGGREGATE ARRAY_AGGREGATE_DISTINCT_DESC (ANYELEMENT)
(
   SFUNC = ARRAY_APPEND_NO_NULLS_DISTINCT,
   STYPE = ANYARRAY,
   INITCOND = '{}',
   FINALFUNC = ARRAY_SORT_DESC
)
;


/*
 * Aggregation: Aufsammeln von Werten in einem String (TEXT)
 *              TEXT ist die Postgres-Version von CLOB.
 *
 * Definition einer neuen Aggregations-Operation: TEXT_AGGREGATE
 */

DROP AGGREGATE IF EXISTS TEXT_AGGREGATE (ANYELEMENT);
CREATE AGGREGATE TEXT_AGGREGATE (ANYELEMENT)
(
  SFUNC     = ARRAY_APPEND,
  STYPE     = ANYARRAY,
  FINALFUNC = ARRAY_TO_TEXT_COMMA
)
; 

DROP AGGREGATE IF EXISTS TEXT_AGGREGATE_ASC (ANYELEMENT);
CREATE AGGREGATE TEXT_AGGREGATE_ASC (ANYELEMENT)
(
  SFUNC     = ARRAY_APPEND,
  STYPE     = ANYARRAY,
  FINALFUNC = ARRAY_SORT_ASC_TO_TEXT_COMMA
)
;

DROP AGGREGATE IF EXISTS TEXT_AGGREGATE_DESC (ANYELEMENT);
CREATE AGGREGATE TEXT_AGGREGATE_DESC (ANYELEMENT)
(
  SFUNC     = ARRAY_APPEND,
  STYPE     = ANYARRAY,
  FINALFUNC = ARRAY_SORT_DESC_TO_TEXT_COMMA
)
;



DROP AGGREGATE IF EXISTS TEXT_AGGREGATE_DISTINCT (ANYELEMENT);
CREATE AGGREGATE TEXT_AGGREGATE_DISTINCT (ANYELEMENT)
(
  SFUNC     = ARRAY_APPEND_NO_NULLS_DISTINCT,
  STYPE     = ANYARRAY,
  FINALFUNC = ARRAY_TO_TEXT_COMMA
)
; 

DROP AGGREGATE IF EXISTS TEXT_AGGREGATE_DISTINCT_ASC (ANYELEMENT);
CREATE AGGREGATE TEXT_AGGREGATE_DISTINCT_ASC (ANYELEMENT)
(
  SFUNC     = ARRAY_APPEND_NO_NULLS_DISTINCT,
  STYPE     = ANYARRAY,
  FINALFUNC = ARRAY_SORT_ASC_TO_TEXT_COMMA
)
;

DROP AGGREGATE IF EXISTS TEXT_AGGREGATE_DISTINCT_DESC (ANYELEMENT);
CREATE AGGREGATE TEXT_AGGREGATE_DISTINCT_DESC (ANYELEMENT)
(
  SFUNC     = ARRAY_APPEND_NO_NULLS_DISTINCT,
  STYPE     = ANYARRAY,
  FINALFUNC = ARRAY_SORT_DESC_TO_TEXT_COMMA
)
;


-- Letztes Element einer Menge von Elementen.
-- Damit kann man das MySQL-Verhalten simulieren:
--   MySQL:       SELECT COUNT(*), a, x        FROM ... GROUP BY a  (x hat irgendeinen Wert aus der Gruppe)
--   PostgreSQL:  SELECT COUNT(*), a, FIRST(x) FROM ... GROUP BY a

CREATE OR REPLACE
  FUNCTION FIRST_ELEMENT(ANYELEMENT, ANYELEMENT) 
  RETURNS ANYELEMENT
LANGUAGE SQL IMMUTABLE
AS
$_SQL_$
SELECT $1;
$_SQL_$
;

DROP AGGREGATE IF EXISTS FIRST (ANYELEMENT) ; 
CREATE AGGREGATE FIRST(ANYELEMENT)
(
   SFUNC = FIRST_ELEMENT,
   STYPE = ANYELEMENT
)
; 

CREATE OR REPLACE
  FUNCTION SECOND_ELEMENT(ANYELEMENT, ANYELEMENT) 
  RETURNS ANYELEMENT
LANGUAGE SQL IMMUTABLE
AS
$_SQL_$
SELECT $2
$_SQL_$
;

DROP AGGREGATE IF EXISTS LAST (ANYELEMENT) ; 
CREATE AGGREGATE LAST (ANYELEMENT)
(
   SFUNC = SECOND_ELEMENT,
   STYPE = ANYELEMENT
)
; 