| -- https://wiki.postgresql.org/wiki/Aggregate_Mode | -- https://wiki.postgresql.org/wiki/Aggregate_Mode |
| CREATE OR REPLACE FUNCTION _final_mode(anyarray) | CREATE OR REPLACE FUNCTION _final_mode(anyarray) |
| RETURNS anyelement AS | RETURNS anyelement AS |
| $BODY$ | $BODY$ |
| SELECT a | SELECT a |
| FROM unnest($1) a | FROM unnest($1) a |
| GROUP BY 1 | GROUP BY 1 |
| ORDER BY COUNT(1) DESC, 1 | ORDER BY COUNT(1) DESC, 1 |
| LIMIT 1; | LIMIT 1; |
| $BODY$ | $BODY$ |
| LANGUAGE 'sql' IMMUTABLE; | LANGUAGE 'sql' IMMUTABLE; |
| -- Tell Postgres how to use our aggregate | -- Tell Postgres how to use our aggregate |
| CREATE AGGREGATE mode(anyelement) ( | CREATE AGGREGATE text_mode(anyelement) ( |
| SFUNC=array_append, --Function to call for each row. Just builds the array | SFUNC=array_append, --Function to call for each row. Just builds the array |
| STYPE=anyarray, | STYPE=anyarray, |
| FINALFUNC=_final_mode, --Function to call after everything has been added to array | FINALFUNC=_final_mode, --Function to call after everything has been added to array |
| INITCOND='{}' --Initialize an empty array when starting | INITCOND='{}' --Initialize an empty array when starting |
| ); | ); |
| CREATE VIEW suppliers AS | |
| select distinct on ("supplierABN") "supplierABN", "supplierName", count("supplierName") from contractnotice | |
| where "supplierABN" is not null | |
| group by "supplierABN", "supplierName" | |
| order by "supplierABN" asc, count("supplierName") desc; |