Aggregate Range

From PostgreSQL wiki
Jump to navigationJump to search

...?? ... Same as

SELECT MAX(num_value)-MIN(num_value) AS range_val FROM t;

range()

Snippets

Aggregate Range

Works with PostgreSQL

Any version

Written in

SQL

Depends on

Nothing


CREATE OR REPLACE FUNCTION _final_range(numeric[])
   RETURNS numeric AS $$
   SELECT MAX(val) - MIN(val)
   FROM unnest($1) val;
$$ LANGUAGE 'sql' IMMUTABLE;

-- Add aggregate
CREATE AGGREGATE range(numeric) (
  SFUNC=array_append, --Function to call for each row. Just builds the array
  STYPE=numeric[],
  FINALFUNC=_final_range, --Function to call after everything has been added to array
  INITCOND='{}' --Initialize an empty array when starting
);

Usage

SELECT range(num_value) AS range_val FROM t;

Caution

If you are on PostgreSQL 8.3 or earlier, you will need to add the Array Unnest function.

See Also