Replace ad-hoc insertions into pg_opclass and friends with CREATE

OPERATOR CLASS commands.  Further tweaking of documentation for same.

1 files changed, 41 insertions, 302 deletions

diff --git a/contrib/rtree_gist/rtree_gist.sql.in b/contrib/rtree_gist/rtree_gist.sql.in
index 0d1770381f4..df254404e18 100644
--- a/contrib/rtree_gist/rtree_gist.sql.in
+++ b/contrib/rtree_gist/rtree_gist.sql.in
@@ -1,4 +1,8 @@
 begin transaction;
+
+-- Adjust this setting to control where the objects get created.
+SET search_path = public;
+
 --
 --
 --
@@ -21,157 +25,25 @@ create function gbox_union(bytea, opaque) returns box as 'MODULE_PATHNAME' langu
 
 create function gbox_same(box, box, opaque) returns opaque as 'MODULE_PATHNAME' language 'C';
 
--- add a new opclass
-INSERT INTO pg_opclass (opcamid, opcname, opcnamespace, opcowner, opcintype, opcdefault, opckeytype)
-    VALUES (
-        (SELECT oid FROM pg_am WHERE amname = 'gist'),
-        'gist_box_ops',
-        (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog'),
-        1,	-- UID of superuser is hardwired to 1 as of PG 7.3
-        (SELECT oid FROM pg_type WHERE typname = 'box'),
-        true,
-        0);
-
--- get the comparators for boxes and store them in a tmp table
-SELECT o.oid AS opoid, o.oprname
-INTO TEMP TABLE rt_ops_tmp
-FROM pg_operator o, pg_type t
-WHERE o.oprleft = t.oid 
-   and t.typname = 'box';
-
--- using the tmp table, generate the amop entries 
--- box_left
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) 
-   SELECT opcl.oid, 1, false, c.opoid
-   FROM pg_opclass opcl, rt_ops_tmp c
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_box_ops' 
-      and c.oprname = '<<';
-
--- box_overleft
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) 
-   SELECT opcl.oid, 2, false, c.opoid
-   FROM pg_opclass opcl, rt_ops_tmp c
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_box_ops' 
-      and c.oprname = '&<';
-
--- box_overlap
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) 
-   SELECT opcl.oid, 3, false, c.opoid
-   FROM pg_opclass opcl, rt_ops_tmp c
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_box_ops' 
-      and c.oprname = '&&';
-
--- box_overright
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) 
-   SELECT opcl.oid, 4, false, c.opoid
-   FROM pg_opclass opcl, rt_ops_tmp c
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_box_ops' 
-      and c.oprname = '&>';
-
--- box_right
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
-   SELECT opcl.oid, 5, false, c.opoid
-   FROM pg_opclass opcl, rt_ops_tmp c
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_box_ops' 
-      and c.oprname = '>>';
-
--- box_same
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) 
-   SELECT opcl.oid, 6, false, c.opoid
-   FROM pg_opclass opcl, rt_ops_tmp c
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_box_ops' 
-      and c.oprname = '~=';
-
--- box_contains
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
-   SELECT opcl.oid, 7, false, c.opoid
-   FROM pg_opclass opcl, rt_ops_tmp c
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_box_ops' 
-      and c.oprname = '~';
-
--- box_contained
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
-   SELECT opcl.oid, 8, false, c.opoid
-   FROM pg_opclass opcl, rt_ops_tmp c
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_box_ops' 
-      and c.oprname = '@';
-
-DROP table rt_ops_tmp;
-
--- add the entries to amproc for the support methods
--- note the amprocnum numbers associated with each are specific!
-
-INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
-   SELECT opcl.oid, 1, pro.oid
-   FROM pg_opclass opcl, pg_proc pro
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_box_ops'
-      and proname = 'gbox_consistent';
-
-INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
-   SELECT opcl.oid, 2, pro.oid
-   FROM pg_opclass opcl, pg_proc pro
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_box_ops'
-      and proname = 'gbox_union';
-
-INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
-   SELECT opcl.oid, 3, pro.oid
-   FROM pg_opclass opcl, pg_proc pro
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_box_ops'
-      and proname = 'gbox_compress';
-
-INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
-   SELECT opcl.oid, 4, pro.oid
-   FROM pg_opclass opcl, pg_proc pro
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_box_ops'
-      and proname = 'rtree_decompress';
-
-INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
-   SELECT opcl.oid, 5, pro.oid
-   FROM pg_opclass opcl, pg_proc pro
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_box_ops'
-      and proname = 'gbox_penalty';
-
-INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
-   SELECT opcl.oid, 6, pro.oid
-   FROM pg_opclass opcl, pg_proc pro
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_box_ops'
-      and proname = 'gbox_picksplit';
+-- create the operator class
+CREATE OPERATOR CLASS gist_box_ops
+    DEFAULT FOR TYPE box USING gist AS
+	OPERATOR	1	<< ,
+	OPERATOR	2	&< ,
+	OPERATOR	3	&& ,
+	OPERATOR	4	&> ,
+	OPERATOR	5	>> ,
+	OPERATOR	6	~= ,
+	OPERATOR	7	~ ,
+	OPERATOR	8	@ ,
+	FUNCTION	1	gbox_consistent (opaque, box, int4),
+	FUNCTION	2	gbox_union (bytea, opaque),
+	FUNCTION	3	gbox_compress (opaque),
+	FUNCTION	4	rtree_decompress (opaque),
+	FUNCTION	5	gbox_penalty (opaque, opaque, opaque),
+	FUNCTION	6	gbox_picksplit (opaque, opaque),
+	FUNCTION	7	gbox_same (box, box, opaque);
 
-INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
-   SELECT opcl.oid, 7, pro.oid
-   FROM pg_opclass opcl, pg_proc pro
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_box_ops'
-      and proname = 'gbox_same';
 
 --
 --
@@ -185,158 +57,25 @@ create function gpoly_consistent(opaque,polygon,int4) returns bool as 'MODULE_PA
 
 create function gpoly_compress(opaque) returns opaque as 'MODULE_PATHNAME' language 'C';
 
--- add a new opclass
-INSERT INTO pg_opclass (opcamid, opcname, opcnamespace, opcowner, opcintype, opcdefault, opckeytype)
-    VALUES (
-        (SELECT oid FROM pg_am WHERE amname = 'gist'),
-        'gist_poly_ops',
-        (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog'),
-        1,	-- UID of superuser is hardwired to 1 as of PG 7.3
-        (SELECT oid FROM pg_type WHERE typname = 'polygon'),
-        true,
-        (SELECT oid FROM pg_type WHERE typname = 'box'));
+-- create the operator class
+CREATE OPERATOR CLASS gist_poly_ops
+    DEFAULT FOR TYPE polygon USING gist AS
+	OPERATOR	1	<<	RECHECK ,
+	OPERATOR	2	&<	RECHECK ,
+	OPERATOR	3	&&	RECHECK ,
+	OPERATOR	4	&>	RECHECK ,
+	OPERATOR	5	>>	RECHECK ,
+	OPERATOR	6	~=	RECHECK ,
+	OPERATOR	7	~	RECHECK ,
+	OPERATOR	8	@	RECHECK ,
+	FUNCTION	1	gpoly_consistent (opaque, polygon, int4),
+	FUNCTION	2	gbox_union (bytea, opaque),
+	FUNCTION	3	gpoly_compress (opaque),
+	FUNCTION	4	rtree_decompress (opaque),
+	FUNCTION	5	gbox_penalty (opaque, opaque, opaque),
+	FUNCTION	6	gbox_picksplit (opaque, opaque),
+	FUNCTION	7	gbox_same (box, box, opaque),
+	STORAGE		box;
 
--- get the comparators for polygons and store them in a tmp table
--- hack for 757 (poly_contain_pt) Teodor
-SELECT o.oid AS opoid, o.oprname
-INTO TEMP TABLE rt_ops_tmp
-FROM pg_operator o, pg_type t
-WHERE o.oprleft = t.oid and o.oid <> 757
-   and t.typname = 'polygon';
-
--- using the tmp table, generate the amop entries 
--- poly_left
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) 
-   SELECT opcl.oid, 1, true, c.opoid
-   FROM pg_opclass opcl, rt_ops_tmp c
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_poly_ops' 
-      and c.oprname = '<<';
-
--- poly_overleft
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) 
-   SELECT opcl.oid, 2, true, c.opoid
-   FROM pg_opclass opcl, rt_ops_tmp c
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_poly_ops' 
-      and c.oprname = '&<';
-
--- poly_overlap
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) 
-   SELECT opcl.oid, 3, true, c.opoid
-   FROM pg_opclass opcl, rt_ops_tmp c
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_poly_ops' 
-      and c.oprname = '&&';
-
--- poly_overright
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) 
-   SELECT opcl.oid, 4, true, c.opoid
-   FROM pg_opclass opcl, rt_ops_tmp c
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_poly_ops' 
-      and c.oprname = '&>';
-
--- poly_right
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
-   SELECT opcl.oid, 5, true, c.opoid
-   FROM pg_opclass opcl, rt_ops_tmp c
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_poly_ops' 
-      and c.oprname = '>>';
-
--- poly_same
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) 
-   SELECT opcl.oid, 6, true, c.opoid
-   FROM pg_opclass opcl, rt_ops_tmp c
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_poly_ops' 
-      and c.oprname = '~=';
-
--- poly_contains
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
-   SELECT opcl.oid, 7, true, c.opoid
-   FROM pg_opclass opcl, rt_ops_tmp c
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_poly_ops' 
-      and c.oprname = '~';
-
--- poly_contained
-INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
-   SELECT opcl.oid, 8, true, c.opoid
-   FROM pg_opclass opcl, rt_ops_tmp c
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_poly_ops' 
-      and c.oprname = '@';
-
-DROP table rt_ops_tmp;
-
--- add the entries to amproc for the support methods
--- note the amprocnum numbers associated with each are specific!
-
-INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
-   SELECT opcl.oid, 1, pro.oid
-   FROM pg_opclass opcl, pg_proc pro
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_poly_ops'
-      and proname = 'gpoly_consistent';
-
-INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
-   SELECT opcl.oid, 2, pro.oid
-   FROM pg_opclass opcl, pg_proc pro
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_poly_ops'
-      and proname = 'gbox_union';
-
-INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
-   SELECT opcl.oid, 3, pro.oid
-   FROM pg_opclass opcl, pg_proc pro
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_poly_ops'
-      and proname = 'gpoly_compress';
-
-INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
-   SELECT opcl.oid, 4, pro.oid
-   FROM pg_opclass opcl, pg_proc pro
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_poly_ops'
-      and proname = 'rtree_decompress';
-
-INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
-   SELECT opcl.oid, 5, pro.oid
-   FROM pg_opclass opcl, pg_proc pro
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_poly_ops'
-      and proname = 'gbox_penalty';
-
-INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
-   SELECT opcl.oid, 6, pro.oid
-   FROM pg_opclass opcl, pg_proc pro
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_poly_ops'
-      and proname = 'gbox_picksplit';
-
-INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
-   SELECT opcl.oid, 7, pro.oid
-   FROM pg_opclass opcl, pg_proc pro
-   WHERE
-      opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
-      and opcname = 'gist_poly_ops'
-      and proname = 'gbox_same';
 
 end transaction;
-