Query column with range types

== Rationale ==
The main use case for range types is to store ranges in PostgreSQL tables, and then find rows whose range includes a certain literal. These can already be indexed using GIN and GiST index types.
 db=# EXPLAIN ANALYZE SELECT * FROM rng WHERE rng @> 10.0;
 Bitmap Heap Scan on rng (cost=48.40..2709.45 rows=1000 width=32) (actual time=0.103..0.103 rows=10 loops=1)
 Recheck Cond: (rng @> 10.0)
 -> Bitmap Index Scan on rng_rng_idx (cost=0.00..48.15 rows=1000 width=0) (actual time=0.096..0.096 rows=10 loops=1)
 Index Cond: (rng @> 10.0)
 Total runtime: 0.136 ms
However, you cannot index inverse queries - if you want to find single values in a certain range:
 db=# EXPLAIN ANALYZE SELECT * FROM num WHERE i <@ '[0, 10)';
 Seq Scan on num (cost=0.00..16925.00 rows=1000 width=6) (actual time=0.014..188.761 rows=9 loops=1)
 Filter: (i <@ '[0,10]'::numrange)
 Rows Removed by Filter: 999991
 Total runtime: 188.820 ms
== Example ==
Of course the above query is equivalent to the indexable query WHERE num >= 0 AND num < 10. But what if you want to use range types? Well, you can create a custom operator with a function that automatically gets inlined:
 db=# EXPLAIN ANALYZE SELECT * FROM num WHERE i <@ '[0, 10)';
 Index Only Scan using num_i_idx on num (cost=0.00..4.56 rows=10 width=6) (actual time=0.014..0.016 rows=9 loops=1)
 Index Cond: ((i >= 0::numeric) AND (i < 10::numeric))
 Heap Fetches: 0
 Total runtime: 0.039 ms
Infinite terms get optimized out:
 db=# EXPLAIN ANALYZE SELECT * FROM num WHERE i <@ '[,10]';
 Index Only Scan using num_i_idx on num (cost=0.00..4.53 rows=10 width=6) (actual time=0.007..0.009 rows=10 loops=1)
 Index Cond: (i <= 10::numeric)
 Heap Fetches: 0
 Total runtime: 0.032 ms
=== Beware! ===
These custom operators are deliberately defined to be non-commative. If you create these operators, do not write range queries in the form 10.0 <@ range_col because it will blow up:
 db=# EXPLAIN ANALYZE SELECT * FROM rng WHERE 10.0 <@ rng;
 Seq Scan on rng (cost=0.00..46358.16 rows=444424 width=32) (actual time=0.013..240.054 rows=10 loops=1)
 Filter: ((lower_inf(rng) OR CASE WHEN lower_inc(rng) THEN (10.0 >= lower(rng)) ELSE (10.0 > lower(rng)) END)
 AND (upper_inf(rng) OR CASE WHEN upper_inc(rng) THEN (10.0 <= upper(rng)) ELSE (10.0 < upper(rng)) END))
 Rows Removed by Filter: 999990
 Total runtime: 240.125 ms
== Code ==
You can adapt this code to any range type you need, just by changing the types of the function and the operator.
-- int4range (integer)
CREATE OR REPLACE FUNCTION range_contains(el integer, rng int4range) RETURNS bool IMMUTABLE LANGUAGE sql AS $$
SELECT (lower_inf(rng) OR (CASE WHEN lower_inc(rng) THEN el >= lower(rng) ELSE el > lower(rng) END))
 AND (upper_inf(rng) OR (CASE WHEN upper_inc(rng) THEN el <= upper(rng) ELSE el < upper(rng) END))
$$;
CREATE OPERATOR <@ (procedure=range_contains, leftarg=integer, rightarg=int4range);
-- int8range (bigint)
CREATE OR REPLACE FUNCTION range_contains(el bigint, rng int8range) RETURNS bool IMMUTABLE LANGUAGE sql AS $$
SELECT (lower_inf(rng) OR (CASE WHEN lower_inc(rng) THEN el >= lower(rng) ELSE el > lower(rng) END))
 AND (upper_inf(rng) OR (CASE WHEN upper_inc(rng) THEN el <= upper(rng) ELSE el < upper(rng) END))
$$;
CREATE OPERATOR <@ (procedure=range_contains, leftarg=bigint, rightarg=int8range);
-- numrange (numeric)
CREATE OR REPLACE FUNCTION range_contains(el numeric, rng numrange) RETURNS bool IMMUTABLE LANGUAGE sql AS $$
SELECT (lower_inf(rng) OR (CASE WHEN lower_inc(rng) THEN el >= lower(rng) ELSE el > lower(rng) END))
 AND (upper_inf(rng) OR (CASE WHEN upper_inc(rng) THEN el <= upper(rng) ELSE el < upper(rng) END))
$$;
CREATE OPERATOR <@ (procedure=range_contains, leftarg=numeric, rightarg=numrange);
-- tsrange (timestamp without time zone)
CREATE OR REPLACE FUNCTION range_contains(el timestamp, rng tsrange) RETURNS bool IMMUTABLE LANGUAGE sql AS $$
SELECT (lower_inf(rng) OR (CASE WHEN lower_inc(rng) THEN el >= lower(rng) ELSE el > lower(rng) END))
 AND (upper_inf(rng) OR (CASE WHEN upper_inc(rng) THEN el <= upper(rng) ELSE el < upper(rng) END))
$$;
CREATE OPERATOR <@ (procedure=range_contains, leftarg=timestamp, rightarg=tsrange);
-- tstzrange (timestamp with time zone)
CREATE OR REPLACE FUNCTION range_contains(el timestamptz, rng tstzrange) RETURNS bool IMMUTABLE LANGUAGE sql AS $$
SELECT (lower_inf(rng) OR (CASE WHEN lower_inc(rng) THEN el >= lower(rng) ELSE el > lower(rng) END))
 AND (upper_inf(rng) OR (CASE WHEN upper_inc(rng) THEN el <= upper(rng) ELSE el < upper(rng) END))
$$;
CREATE OPERATOR <@ (procedure=range_contains, leftarg=timestamptz, rightarg=tstzrange);
-- daterange (date)
CREATE OR REPLACE FUNCTION range_contains(el date, rng daterange) RETURNS bool IMMUTABLE LANGUAGE sql AS $$
SELECT (lower_inf(rng) OR (CASE WHEN lower_inc(rng) THEN el >= lower(rng) ELSE el > lower(rng) END))
 AND (upper_inf(rng) OR (CASE WHEN upper_inc(rng) THEN el <= upper(rng) ELSE el < upper(rng) END))
$$;
CREATE OPERATOR <@ (procedure=range_contains, leftarg=date, rightarg=daterange);
Idea from Andres Freund, implemented by Marti Raudsepp.