--
-- Create access method tests
--
-- Make gist2 over gisthandler. In fact, it would be a synonym to gist.
CREATE ACCESS METHOD gist2 TYPE INDEX HANDLER gisthandler;
-- Try to create gist2 index on fast_emp4000: fail because opclass doesn't exist
CREATE INDEX grect2ind2 ON fast_emp4000 USING gist2 (home_base);
ERROR:  data type box has no default operator class for access method "gist2"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.
-- Make operator class for boxes using gist2
CREATE OPERATOR CLASS box_ops DEFAULT
	FOR TYPE box USING gist2 AS
	OPERATOR 1	<<,
	OPERATOR 2	&<,
	OPERATOR 3	&&,
	OPERATOR 4	&>,
	OPERATOR 5	>>,
	OPERATOR 6	~=,
	OPERATOR 7	@>,
	OPERATOR 8	<@,
	OPERATOR 9	&<|,
	OPERATOR 10	<<|,
	OPERATOR 11	|>>,
	OPERATOR 12	|&>,
	OPERATOR 13	~,
	OPERATOR 14	@,
	FUNCTION 1	gist_box_consistent(internal, box, smallint, oid, internal),
	FUNCTION 2	gist_box_union(internal, internal),
	-- don't need compress, decompress, or fetch functions
	FUNCTION 5	gist_box_penalty(internal, internal, internal),
	FUNCTION 6	gist_box_picksplit(internal, internal),
	FUNCTION 7	gist_box_same(box, box, internal);
-- Create gist2 index on fast_emp4000
CREATE INDEX grect2ind2 ON fast_emp4000 USING gist2 (home_base);
-- Now check the results from plain indexscan; temporarily drop existing
-- index grect2ind to ensure it doesn't capture the plan
BEGIN;
DROP INDEX grect2ind;
SET enable_seqscan = OFF;
SET enable_indexscan = ON;
SET enable_bitmapscan = OFF;
EXPLAIN (COSTS OFF)
SELECT * FROM fast_emp4000
    WHERE home_base @ '(200,200),(2000,1000)'::box
    ORDER BY (home_base[0])[0];
                           QUERY PLAN                           
----------------------------------------------------------------
 Sort
   Sort Key: ((home_base[0])[0])
   ->  Index Only Scan using grect2ind2 on fast_emp4000
         Index Cond: (home_base @ '(2000,1000),(200,200)'::box)
(4 rows)

SELECT * FROM fast_emp4000
    WHERE home_base @ '(200,200),(2000,1000)'::box
    ORDER BY (home_base[0])[0];
       home_base       
-----------------------
 (337,455),(240,359)
 (1444,403),(1346,344)
(2 rows)

EXPLAIN (COSTS OFF)
SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
                         QUERY PLAN                          
-------------------------------------------------------------
 Aggregate
   ->  Index Only Scan using grect2ind2 on fast_emp4000
         Index Cond: (home_base && '(1000,1000),(0,0)'::box)
(3 rows)

SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
 count 
-------
     2
(1 row)

EXPLAIN (COSTS OFF)
SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
                       QUERY PLAN                       
--------------------------------------------------------
 Aggregate
   ->  Index Only Scan using grect2ind2 on fast_emp4000
         Index Cond: (home_base IS NULL)
(3 rows)

SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
 count 
-------
   278
(1 row)

ROLLBACK;
-- Try to drop access method: fail because of dependent objects
DROP ACCESS METHOD gist2;
ERROR:  cannot drop access method gist2 because other objects depend on it
DETAIL:  index grect2ind2 depends on operator class box_ops for access method gist2
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
-- Drop access method cascade
DROP ACCESS METHOD gist2 CASCADE;
NOTICE:  drop cascades to index grect2ind2