PostgreSQL Custom Aggregates

PostgreSQL Custom Aggregates is a SQL function in the PostgreSQL Specific category. PostgreSQL: create custom aggregate functions using state transition functions. Extremely powerful. The syntax is CREATE AGGREGATE agg_name (types) (SFUNC=func, STYPE=type);. It accepts types. It returns custom aggregate result. A typical example: CREATE OR REPLACE FUNCTION array_accum_sfunc(acc INT[], val INT) RETURNS INT[] IMMUTABLE LANGUAGE SQL AS $ SELECT array_append(acc, val) $; CREATE AGGREGATE array_accum(INT) ( SFUNC = array_accum_sfunc, STYPE = INT[], INITCOND = '{}' ); SELECT department, array_accum(employee_id ORDER BY name) FROM employees GROUP BY department; -- Returns: IT -> {1,3,7}, HR -> {2,5} -- Built-in equivalents: -- array_agg() for arrays -- string_agg() for strings -- json_agg() for JSON A close relative is SERIAL, which postgreSQL auto-increment type. Creates an INTEGER column with a sequence. Equivalent to AUTO_INCREMENT in MySQL. A close relative is RETURNING, which postgreSQL extension that returns values from INSERT/UPDATE/DELETE statements (like OUTPUT in SQL Server).