\c postgres
DROP DATABASE test_view_opclass_deps;

CREATE DATABASE test_view_opclass_deps;

\c test_view_opclass_deps

CREATE TYPE comp AS (
	a int,
	b int
);

CREATE FUNCTION cmp(comp, comp) RETURNS int AS $$
SELECT CASE
	WHEN $1.a < $2.a THEN -1
	WHEN $1.a > $2.a THEN 1
	WHEN $1.a = $2.a THEN CASE
		WHEN $1.b < $2.b THEN -1
		WHEN $1.b > $2.b THEN 1
		WHEN $1.b = $2.b THEN 0 END
	END;
$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION eq(comp, comp) RETURNS bool AS $$
SELECT cmp($1, $2) = 0;
$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION gt(comp, comp) RETURNS bool AS $$
SELECT cmp($1, $2) > 0;
$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION lt(comp, comp) RETURNS bool AS $$
SELECT cmp($1, $2) < 0;
$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION ge(comp, comp) RETURNS bool AS $$
SELECT cmp($1, $2) >= 0;
$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION le(comp, comp) RETURNS bool AS $$
SELECT cmp($1, $2) <= 0;
$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION ne(comp, comp) RETURNS bool AS $$
SELECT cmp($1, $2) <> 0;
$$ LANGUAGE SQL IMMUTABLE;

CREATE OPERATOR = (
	leftarg = comp, rightarg = comp, procedure = eq,
	negator = <>, commutator = =,
	restrict = eqsel, join = eqjoinsel
);

CREATE OPERATOR > (
	leftarg = comp, rightarg = comp, procedure = gt,
	negator = <=, commutator = <,
	restrict = scalargtsel, join = scalargtjoinsel
);

CREATE OPERATOR < (
	leftarg = comp, rightarg = comp, procedure = lt,
	negator = >=, commutator = >,
	restrict = scalarltsel, join = scalarltjoinsel
);

CREATE OPERATOR >= (
	leftarg = comp, rightarg = comp, procedure = ge,
	negator = <, commutator = <=,
	restrict = scalargtsel, join = scalargtjoinsel
);

CREATE OPERATOR <= (
	leftarg = comp, rightarg = comp, procedure = le,
	negator = >, commutator = >=,
	restrict = scalarltsel, join = scalarltjoinsel
);

CREATE OPERATOR <> (
	leftarg = comp, rightarg = comp, procedure = ne,
	negator = =, commutator = <>,
	restrict = neqsel, join = neqjoinsel
);

CREATE OPERATOR CLASS comp_ops
DEFAULT FOR TYPE comp USING btree AS
	OPERATOR 1 <=,
	OPERATOR 2 <,
	OPERATOR 3 =,
	OPERATOR 4 >,
	OPERATOR 5 >=,
	FUNCTION 1 cmp(comp, comp);

CREATE TABLE stuff (
	item comp NOT NULL
);

INSERT INTO stuff (item)
SELECT (i, j)::comp
FROM generate_series(-2, 2) i CROSS JOIN generate_series(2, -2, -1) j;

CREATE VIEW group_stuff AS
SELECT item, count(*)
FROM stuff
GROUP BY item;
