Aggregate Range

From PostgreSQL wiki

Revision as of 11:15, 25 April 2013 by Ppkrauss (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

...?? ... Same as

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

Contents

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

Personal tools