[postgis-tickets] r15290 - Fix for pg_upgrade of pg < 9.5 + 2.3+, pg_upgrade and doing a postgis micro update to latest stable.

Regina Obe lr at pcorp.us
Sat Jan 21 21:46:19 PST 2017


Author: robe
Date: 2017-01-21 21:46:18 -0800 (Sat, 21 Jan 2017)
New Revision: 15290

Added:
   trunk/postgis/geography_brin.sql.in
   trunk/postgis/postgis_brin.sql.in
Modified:
   trunk/postgis/geography.sql.in
   trunk/postgis/postgis.sql.in
Log:
Fix for pg_upgrade of pg < 9.5 + 2.3+, pg_upgrade and doing a postgis micro update to latest stable.
References #3683 for PostGIS 2.4 (trunk)

Modified: trunk/postgis/geography.sql.in
===================================================================
--- trunk/postgis/geography.sql.in	2017-01-18 21:07:16 UTC (rev 15289)
+++ trunk/postgis/geography.sql.in	2017-01-22 05:46:18 UTC (rev 15290)
@@ -286,94 +286,9 @@
 	FUNCTION        6        geography_gist_picksplit (internal, internal),
 	FUNCTION        7        geography_gist_same (box2d, box2d, internal);
 
-#if POSTGIS_PGSQL_VERSION > 94
---------------------------------------------------------------------
--- BRIN support for geographies                                   --
---------------------------------------------------------------------
+-- moved to separate file cause its invovled
+#include "geography_brin.sql.in"
 
---------------------------------
--- the needed cross-operators --
---------------------------------
-
--- Availability: 2.3.0
-CREATE OR REPLACE FUNCTION overlaps_geog(gidx, geography)
-RETURNS boolean
-AS 'MODULE_PATHNAME','gserialized_gidx_geog_overlaps'
-LANGUAGE 'c' IMMUTABLE STRICT;
-
--- Availability: 2.3.0
-CREATE OR REPLACE FUNCTION overlaps_geog(gidx, gidx)
-RETURNS boolean
-AS 'MODULE_PATHNAME','gserialized_gidx_gidx_overlaps'
-LANGUAGE 'c' IMMUTABLE STRICT;
-
--- Availability: 2.3.0
-CREATE OPERATOR && (
-  LEFTARG    = gidx,
-  RIGHTARG   = geography,
-  PROCEDURE  = overlaps_geog,
-  COMMUTATOR = &&
-);
-
--- Availability: 2.3.0
-CREATE OR REPLACE FUNCTION overlaps_geog(geography, gidx)
-RETURNS boolean
-AS $$
-  SELECT $2 OPERATOR(@extschema at .&&) $1;
-$$ LANGUAGE SQL IMMUTABLE STRICT;
-
--- Availability: 2.3.0
-CREATE OPERATOR && (
-  LEFTARG    = geography,
-  RIGHTARG   = gidx,
-  PROCEDURE  = overlaps_geog,
-  COMMUTATOR = &&
-);
-
--- Availability: 2.3.0
-CREATE OPERATOR && (
-  LEFTARG   = gidx,
-  RIGHTARG  = gidx,
-  PROCEDURE = overlaps_geog,
-  COMMUTATOR = &&
-);
-
---------------------------------
--- the OpFamily               --
---------------------------------
-
--- Availability: 2.3.0
-CREATE OPERATOR FAMILY brin_geography_inclusion_ops USING brin;
-
--- Availability: 2.3.0
-CREATE OR REPLACE FUNCTION geog_brin_inclusion_add_value(internal, internal, internal, internal) RETURNS boolean
-        AS 'MODULE_PATHNAME','geog_brin_inclusion_add_value'
-        LANGUAGE 'c';
-
--- Availability: 2.3.0
-CREATE OPERATOR CLASS brin_geography_inclusion_ops
-  DEFAULT FOR TYPE geography
-  USING brin
-  FAMILY brin_geography_inclusion_ops AS
-    OPERATOR      3        &&(geography, geography),
-    FUNCTION      1        brin_inclusion_opcinfo(internal) ,
-    FUNCTION      2        geog_brin_inclusion_add_value(internal, internal, internal, internal) ,
-    FUNCTION      3        brin_inclusion_consistent(internal, internal, internal) ,
-    FUNCTION      4        brin_inclusion_union(internal, internal, internal) ,
-  STORAGE gidx;
-
-ALTER OPERATOR FAMILY brin_geography_inclusion_ops USING brin ADD
-    OPERATOR      3         &&(gidx, geography),
-
-    OPERATOR      3         &&(geography, gidx),
-
-    OPERATOR      3         &&(gidx, gidx);
-
----------------------------------------------------------------
--- END
----------------------------------------------------------------
-#endif
-
 -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
 -- B-Tree Functions
 -- For sorting and grouping

Added: trunk/postgis/geography_brin.sql.in
===================================================================
--- trunk/postgis/geography_brin.sql.in	                        (rev 0)
+++ trunk/postgis/geography_brin.sql.in	2017-01-22 05:46:18 UTC (rev 15290)
@@ -0,0 +1,104 @@
+#if POSTGIS_PGSQL_VERSION > 94
+--------------------------------------------------------------------
+-- BRIN support for geographies                                   --
+--------------------------------------------------------------------
+
+--------------------------------
+-- the needed cross-operators --
+--------------------------------
+
+-- Availability: 2.3.0
+CREATE OR REPLACE FUNCTION overlaps_geog(gidx, geography)
+RETURNS boolean
+AS 'MODULE_PATHNAME','gserialized_gidx_geog_overlaps'
+LANGUAGE 'c' IMMUTABLE STRICT;
+
+-- Availability: 2.3.0
+CREATE OR REPLACE FUNCTION overlaps_geog(gidx, gidx)
+RETURNS boolean
+AS 'MODULE_PATHNAME','gserialized_gidx_gidx_overlaps'
+LANGUAGE 'c' IMMUTABLE STRICT;
+
+
+-- logic to create missing brin operators
+-- note we can't use CREATE IF NOT EXISTS since not currently supported for operators or families
+-- an earlier 2.3+ might miss this if doing upgrade from < 9.5 to 9.5+
+-- so we need to include this always for 9.5+
+-- Intentionally not indenting DDL cause our uninstall process can't pick it up
+-- also not dollar quoting functions inside DO since gets tripped up by perl upgrade script parser
+DO  language 'plpgsql' $$
+BEGIN
+	-- add geography brin ops if missing and are running a newer than 9.4 --
+	-- use wildcard since if schema postgis is installed is not in search path, will have schema name in front
+	IF NOT EXISTS(SELECT 1 from pg_operator WHERE oprname = '&&' AND 
+		oprleft::regtype::text LIKE '%gidx' AND oprright::regtype::text LIKE '%geography' ) THEN
+-- Availability: 2.3.0
+CREATE OPERATOR && (
+  LEFTARG    = gidx,
+  RIGHTARG   = geography,
+  PROCEDURE  = overlaps_geog,
+  COMMUTATOR = &&
+);
+
+-- Availability: 2.3.0
+CREATE OR REPLACE FUNCTION overlaps_geog(geography, gidx)
+RETURNS boolean
+AS 
+  'SELECT $2 OPERATOR(@extschema at .&&) $1;'
+ LANGUAGE SQL IMMUTABLE STRICT;
+
+-- Availability: 2.3.0
+CREATE OPERATOR && (
+  LEFTARG    = geography,
+  RIGHTARG   = gidx,
+  PROCEDURE  = overlaps_geog,
+  COMMUTATOR = &&
+);
+
+-- Availability: 2.3.0
+CREATE OPERATOR && (
+  LEFTARG   = gidx,
+  RIGHTARG  = gidx,
+  PROCEDURE = overlaps_geog,
+  COMMUTATOR = &&
+);
+
+--------------------------------
+-- the OpFamily               --
+--------------------------------
+
+-- Availability: 2.3.0
+CREATE OPERATOR FAMILY brin_geography_inclusion_ops USING brin;
+
+-- Availability: 2.3.0
+CREATE OR REPLACE FUNCTION geog_brin_inclusion_add_value(internal, internal, internal, internal) RETURNS boolean
+        AS 'MODULE_PATHNAME','geog_brin_inclusion_add_value'
+        LANGUAGE 'c';
+
+-- Availability: 2.3.0
+CREATE OPERATOR CLASS brin_geography_inclusion_ops
+  DEFAULT FOR TYPE geography
+  USING brin
+  FAMILY brin_geography_inclusion_ops AS
+    OPERATOR      3        &&(geography, geography),
+    FUNCTION      1        brin_inclusion_opcinfo(internal) ,
+    FUNCTION      2        geog_brin_inclusion_add_value(internal, internal, internal, internal) ,
+    FUNCTION      3        brin_inclusion_consistent(internal, internal, internal) ,
+    FUNCTION      4        brin_inclusion_union(internal, internal, internal) ,
+  STORAGE gidx;
+
+ALTER OPERATOR FAMILY brin_geography_inclusion_ops USING brin ADD
+    OPERATOR      3         &&(gidx, geography),
+
+    OPERATOR      3         &&(geography, gidx),
+
+    OPERATOR      3         &&(gidx, gidx);
+
+    END IF;
+  
+END;
+$$;
+---------------------------------------------------------------
+-- END
+---------------------------------------------------------------
+#endif
\ No newline at end of file

Modified: trunk/postgis/postgis.sql.in
===================================================================
--- trunk/postgis/postgis.sql.in	2017-01-18 21:07:16 UTC (rev 15289)
+++ trunk/postgis/postgis.sql.in	2017-01-22 05:46:18 UTC (rev 15290)
@@ -5569,302 +5569,11 @@
 	AS 'MODULE_PATHNAME', 'ST_InterpolatePoint'
 	LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL;
 
-#if POSTGIS_PGSQL_VERSION > 94
---------------------------------------------------------------------
--- BRIN support                                                   --
---------------------------------------------------------------------
 
----------------------------------
--- 2d operators                --
----------------------------------
+-- moved to separate file cause its invovled
+#include "postgis_brin.sql.in"
 
--- Availability: 2.3.0
-CREATE OR REPLACE FUNCTION contains_2d(box2df, geometry)
-RETURNS boolean
-AS 'MODULE_PATHNAME','gserialized_contains_box2df_geom_2d'
-LANGUAGE 'c' IMMUTABLE STRICT;
-
--- Availability: 2.3.0
-CREATE OR REPLACE FUNCTION is_contained_2d(box2df, geometry)
-RETURNS boolean
-AS 'MODULE_PATHNAME','gserialized_within_box2df_geom_2d'
-LANGUAGE 'c' IMMUTABLE STRICT;
-
--- Availability: 2.3.0
-CREATE OR REPLACE FUNCTION overlaps_2d(box2df, geometry)
-RETURNS boolean
-AS 'MODULE_PATHNAME','gserialized_overlaps_box2df_geom_2d'
-LANGUAGE 'c' IMMUTABLE STRICT;
-
--- Availability: 2.3.0
-CREATE OR REPLACE FUNCTION overlaps_2d(box2df, box2df)
-RETURNS boolean
-AS 'MODULE_PATHNAME','gserialized_contains_box2df_box2df_2d'
-LANGUAGE 'c' IMMUTABLE STRICT;
-
--- Availability: 2.3.0
-CREATE OR REPLACE FUNCTION contains_2d(box2df, box2df)
-RETURNS boolean
-AS 'MODULE_PATHNAME','gserialized_contains_box2df_box2df_2d'
-LANGUAGE 'c' IMMUTABLE STRICT;
-
--- Availability: 2.3.0
-CREATE OR REPLACE FUNCTION is_contained_2d(box2df, box2df)
-RETURNS boolean
-AS 'MODULE_PATHNAME','gserialized_contains_box2df_box2df_2d'
-LANGUAGE 'c' IMMUTABLE STRICT;
-
--- Availability: 2.3.0
-CREATE OPERATOR ~ (
-  LEFTARG    = box2df,
-  RIGHTARG   = geometry,
-  PROCEDURE  = contains_2d,
-  COMMUTATOR = @
-);
-
--- Availability: 2.3.0
-CREATE OPERATOR @ (
-  LEFTARG    = box2df,
-  RIGHTARG   = geometry,
-  PROCEDURE  = is_contained_2d,
-  COMMUTATOR = ~
-);
-
--- Availability: 2.3.0
-CREATE OPERATOR && (
-  LEFTARG    = box2df,
-  RIGHTARG   = geometry,
-  PROCEDURE  = overlaps_2d,
-  COMMUTATOR = &&
-);
-
--- Availability: 2.3.0
-CREATE OR REPLACE FUNCTION contains_2d(geometry, box2df)
-RETURNS boolean
-AS $$
-  SELECT $2 @ $1;
-$$ LANGUAGE SQL IMMUTABLE STRICT;
-
--- Availability: 2.3.0
-CREATE OR REPLACE FUNCTION is_contained_2d(geometry, box2df)
-RETURNS boolean
-AS $$
-  SELECT $2 ~ $1;
-$$ LANGUAGE SQL IMMUTABLE STRICT;
-
--- Availability: 2.3.0
-CREATE OR REPLACE FUNCTION overlaps_2d(geometry, box2df)
-RETURNS boolean
-AS $$
-  SELECT $2 OPERATOR(@extschema at .&&) $1;
-$$ LANGUAGE SQL IMMUTABLE STRICT;
-
--- Availability: 2.3.0
-CREATE OPERATOR ~ (
-  LEFTARG = geometry,
-  RIGHTARG = box2df,
-  COMMUTATOR = @,
-  PROCEDURE  = contains_2d
-);
-
--- Availability: 2.3.0
-CREATE OPERATOR @ (
-  LEFTARG = geometry,
-  RIGHTARG = box2df,
-  COMMUTATOR = ~,
-  PROCEDURE = is_contained_2d
-);
-
--- Availability: 2.3.0
-CREATE OPERATOR && (
-  LEFTARG    = geometry,
-  RIGHTARG   = box2df,
-  PROCEDURE  = overlaps_2d,
-  COMMUTATOR = &&
-);
-
--- Availability: 2.3.0
-CREATE OPERATOR && (
-  LEFTARG   = box2df,
-  RIGHTARG  = box2df,
-  PROCEDURE = overlaps_2d,
-  COMMUTATOR = &&
-);
-
--- Availability: 2.3.0
-CREATE OPERATOR @ (
-  LEFTARG   = box2df,
-  RIGHTARG  = box2df,
-  PROCEDURE = is_contained_2d,
-  COMMUTATOR = ~
-);
-
--- Availability: 2.3.0
-CREATE OPERATOR ~ (
-  LEFTARG   = box2df,
-  RIGHTARG  = box2df,
-  PROCEDURE = contains_2d,
-  COMMUTATOR = @
-);
-
-----------------------------
--- nd operators           --
-----------------------------
-
--- Availability: 2.3.0
-CREATE OR REPLACE FUNCTION overlaps_nd(gidx, geometry)
-RETURNS boolean
-AS 'MODULE_PATHNAME','gserialized_gidx_geom_overlaps'
-LANGUAGE 'c' IMMUTABLE STRICT;
-
--- Availability: 2.3.0
-CREATE OR REPLACE FUNCTION overlaps_nd(gidx, gidx)
-RETURNS boolean
-AS 'MODULE_PATHNAME','gserialized_gidx_gidx_overlaps'
-LANGUAGE 'c' IMMUTABLE STRICT;
-
--- Availability: 2.3.0
-CREATE OPERATOR &&& (
-  LEFTARG    = gidx,
-  RIGHTARG   = geometry,
-  PROCEDURE  = overlaps_nd,
-  COMMUTATOR = &&&
-);
-
--- Availability: 2.3.0
-CREATE OR REPLACE FUNCTION overlaps_nd(geometry, gidx)
-RETURNS boolean
-AS $$
-  SELECT $2 OPERATOR(@extschema at .&&&) $1;
-$$ LANGUAGE SQL IMMUTABLE STRICT;
-
--- Availability: 2.3.0
-CREATE OPERATOR &&& (
-  LEFTARG    = geometry,
-  RIGHTARG   = gidx,
-  PROCEDURE  = overlaps_nd,
-  COMMUTATOR = &&&
-);
-
--- Availability: 2.3.0
-CREATE OPERATOR &&& (
-  LEFTARG   = gidx,
-  RIGHTARG  = gidx,
-  PROCEDURE = overlaps_nd,
-  COMMUTATOR = &&&
-);
-
-------------------------------
--- Create operator families --
-------------------------------
-
--------------
--- 2D case --
--------------
-
--- Availability: 2.3.0
-CREATE OPERATOR FAMILY brin_geometry_inclusion_ops_2d USING brin;
-
--- Availability: 2.3.0
-CREATE OR REPLACE FUNCTION geom2d_brin_inclusion_add_value(internal, internal, internal, internal) RETURNS boolean
-	AS 'MODULE_PATHNAME','geom2d_brin_inclusion_add_value'
-	LANGUAGE 'c';
-
--- Availability: 2.3.0
-CREATE OR REPLACE FUNCTION geom3d_brin_inclusion_add_value(internal, internal, internal, internal) RETURNS boolean
-	AS 'MODULE_PATHNAME','geom3d_brin_inclusion_add_value'
-	LANGUAGE 'c';
-
--- Availability: 2.3.0
-CREATE OR REPLACE FUNCTION geom4d_brin_inclusion_add_value(internal, internal, internal, internal) RETURNS boolean
-	AS 'MODULE_PATHNAME','geom4d_brin_inclusion_add_value'
-	LANGUAGE 'c';
-
--- Availability: 2.3.0
-CREATE OPERATOR CLASS brin_geometry_inclusion_ops_2d
-  DEFAULT FOR TYPE geometry
-  USING brin
-  FAMILY brin_geometry_inclusion_ops_2d AS
-    OPERATOR      3        &&(geometry, geometry),
-    OPERATOR      7        ~(geometry, geometry),
-    OPERATOR      8        @(geometry, geometry),
-    FUNCTION      1        brin_inclusion_opcinfo(internal) ,
-    FUNCTION      2        geom2d_brin_inclusion_add_value(internal, internal, internal, internal) ,
-    FUNCTION      3        brin_inclusion_consistent(internal, internal, internal) ,
-    FUNCTION      4        brin_inclusion_union(internal, internal, internal) ,
-  STORAGE box2df;
-
-ALTER OPERATOR FAMILY brin_geometry_inclusion_ops_2d USING brin ADD
-    OPERATOR      3         &&(box2df, geometry),
-    OPERATOR      7         ~(box2df, geometry),
-    OPERATOR      8         @(box2df, geometry),
-
-    OPERATOR      3         &&(geometry, box2df),
-    OPERATOR      7         ~(geometry, box2df),
-    OPERATOR      8         @(geometry, box2df),
-
-    OPERATOR      3         &&(box2df, box2df),
-    OPERATOR      7         ~(box2df, box2df),
-    OPERATOR      8         @(box2df, box2df);
-
--------------
--- 3D case --
--------------
-
--- Availability: 2.3.0
-CREATE OPERATOR FAMILY brin_geometry_inclusion_ops_3d USING brin;
-
--- Availability: 2.3.0
-CREATE OPERATOR CLASS brin_geometry_inclusion_ops_3d
-  FOR TYPE geometry
-  USING brin
-  FAMILY brin_geometry_inclusion_ops_3d AS
-    OPERATOR      3        &&&(geometry, geometry),
-    FUNCTION      1        brin_inclusion_opcinfo(internal) ,
-    FUNCTION      2        geom3d_brin_inclusion_add_value(internal, internal, internal, internal) ,
-    FUNCTION      3        brin_inclusion_consistent(internal, internal, internal) ,
-    FUNCTION      4        brin_inclusion_union(internal, internal, internal) ,
-  STORAGE gidx;
-
-ALTER OPERATOR FAMILY brin_geometry_inclusion_ops_3d USING brin ADD
-    OPERATOR      3         &&&(gidx, geometry),
-
-    OPERATOR      3         &&&(geometry, gidx),
-
-    OPERATOR      3         &&&(gidx, gidx);
-
--------------
--- 4D case --
--------------
-
--- Availability: 2.3.0
-CREATE OPERATOR FAMILY brin_geometry_inclusion_ops_4d USING brin;
-
--- Availability: 2.3.0
-CREATE OPERATOR CLASS brin_geometry_inclusion_ops_4d
-  FOR TYPE geometry
-  USING brin
-  FAMILY brin_geometry_inclusion_ops_4d AS
-    OPERATOR      3        &&&(geometry, geometry),
-    FUNCTION      1        brin_inclusion_opcinfo(internal) ,
-    FUNCTION      2        geom4d_brin_inclusion_add_value(internal, internal, internal, internal) ,
-    FUNCTION      3        brin_inclusion_consistent(internal, internal, internal) ,
-    FUNCTION      4        brin_inclusion_union(internal, internal, internal) ,
-  STORAGE gidx;
-
-ALTER OPERATOR FAMILY brin_geometry_inclusion_ops_4d USING brin ADD
-    OPERATOR      3         &&&(gidx, geometry),
-
-    OPERATOR      3         &&&(geometry, gidx),
-
-    OPERATOR      3         &&&(gidx, gidx);
-
 ---------------------------------------------------------------
--- END
----------------------------------------------------------------
-#endif
-
----------------------------------------------------------------
 -- USER CONTRIBUTED
 ---------------------------------------------------------------
 

Added: trunk/postgis/postgis_brin.sql.in
===================================================================
--- trunk/postgis/postgis_brin.sql.in	                        (rev 0)
+++ trunk/postgis/postgis_brin.sql.in	2017-01-22 05:46:18 UTC (rev 15290)
@@ -0,0 +1,336 @@
+#include "sqldefines.h"
+#if POSTGIS_PGSQL_VERSION > 94
+--------------------------------------------------------------------
+-- BRIN support start                                                --
+--------------------------------------------------------------------
+
+---------------------------------
+-- 2d operators                --
+---------------------------------
+
+-- Availability: 2.3.0
+CREATE OR REPLACE FUNCTION contains_2d(box2df, geometry)
+RETURNS boolean
+AS 'MODULE_PATHNAME','gserialized_contains_box2df_geom_2d'
+LANGUAGE 'c' IMMUTABLE STRICT;
+
+-- Availability: 2.3.0
+CREATE OR REPLACE FUNCTION is_contained_2d(box2df, geometry)
+RETURNS boolean
+AS 'MODULE_PATHNAME','gserialized_within_box2df_geom_2d'
+LANGUAGE 'c' IMMUTABLE STRICT;
+
+-- Availability: 2.3.0
+CREATE OR REPLACE FUNCTION overlaps_2d(box2df, geometry)
+RETURNS boolean
+AS 'MODULE_PATHNAME','gserialized_overlaps_box2df_geom_2d'
+LANGUAGE 'c' IMMUTABLE STRICT;
+
+-- Availability: 2.3.0
+CREATE OR REPLACE FUNCTION overlaps_2d(box2df, box2df)
+RETURNS boolean
+AS 'MODULE_PATHNAME','gserialized_contains_box2df_box2df_2d'
+LANGUAGE 'c' IMMUTABLE STRICT;
+
+-- Availability: 2.3.0
+CREATE OR REPLACE FUNCTION contains_2d(box2df, box2df)
+RETURNS boolean
+AS 'MODULE_PATHNAME','gserialized_contains_box2df_box2df_2d'
+LANGUAGE 'c' IMMUTABLE STRICT;
+
+-- Availability: 2.3.0
+CREATE OR REPLACE FUNCTION is_contained_2d(box2df, box2df)
+RETURNS boolean
+AS 'MODULE_PATHNAME','gserialized_contains_box2df_box2df_2d'
+LANGUAGE 'c' IMMUTABLE STRICT;
+
+-- logic to create missing brin operators
+-- note we can't use CREATE IF NOT EXISTS since not currently supported for operators or families
+-- an earlier 2.3+ might miss this if doing upgrade from < 9.5 to 9.5+
+-- so we need to include this always for 9.5+
+-- Intentionally not indenting DDL cause our uninstall process can't pick it up
+DO  language 'plpgsql' $$
+BEGIN
+	-- add geometry brin ops if missing and are running a newer than 9.4 --
+	-- use wildcard since if schema postgis is installed in is not in search path, will have schema name in front
+	IF NOT EXISTS(SELECT 1 from pg_operator WHERE oprname = '~' AND 
+		oprleft::regtype::text LIKE '%box2df' AND oprright::regtype::text LIKE '%geometry' ) THEN
+-- Availability: 2.3.0
+CREATE OPERATOR ~ (
+	LEFTARG    = box2df,
+	RIGHTARG   = geometry,
+	PROCEDURE  = contains_2d,
+	COMMUTATOR = @
+);
+	END IF;
+	
+	IF NOT EXISTS(SELECT 1 from pg_operator WHERE oprname = '@' AND 
+		oprleft::regtype::text LIKE '%box2df' AND oprright::regtype::text LIKE '%geometry' ) THEN
+
+-- Availability: 2.3.0
+CREATE OPERATOR @ (
+	LEFTARG    = box2df,
+	RIGHTARG   = geometry,
+	PROCEDURE  = is_contained_2d,
+	COMMUTATOR = ~
+);
+
+-- Availability: 2.3.0
+CREATE OPERATOR && (
+	LEFTARG    = box2df,
+	RIGHTARG   = geometry,
+	PROCEDURE  = overlaps_2d,
+	COMMUTATOR = &&
+);
+
+-- Availability: 2.3.0
+CREATE OR REPLACE FUNCTION contains_2d(geometry, box2df)
+RETURNS boolean
+AS 
+	'SELECT $2 OPERATOR(@extschema at .@) $1;'
+LANGUAGE SQL IMMUTABLE STRICT;
+
+-- Availability: 2.3.0
+CREATE OR REPLACE FUNCTION is_contained_2d(geometry, box2df)
+RETURNS boolean
+AS
+	'SELECT $2 OPERATOR(@extschema at .~) $1;'
+LANGUAGE SQL IMMUTABLE STRICT;
+
+-- Availability: 2.3.0
+CREATE OR REPLACE FUNCTION overlaps_2d(geometry, box2df)
+RETURNS boolean
+AS 
+	'SELECT $2 OPERATOR(@extschema at .&&) $1;'
+LANGUAGE SQL IMMUTABLE STRICT;
+
+
+-- Availability: 2.3.0
+CREATE OPERATOR ~ (
+	LEFTARG = geometry,
+	RIGHTARG = box2df,
+	COMMUTATOR = @,
+	PROCEDURE  = contains_2d
+);
+-- Availability: 2.3.0
+CREATE OPERATOR @ (
+	LEFTARG = geometry,
+	RIGHTARG = box2df,
+	COMMUTATOR = ~,
+	PROCEDURE = is_contained_2d
+);
+-- Availability: 2.3.0		
+CREATE OPERATOR && (
+	LEFTARG    = geometry,
+	RIGHTARG   = box2df,
+	PROCEDURE  = overlaps_2d,
+	COMMUTATOR = &&
+);
+-- Availability: 2.3.0		
+CREATE OPERATOR && (
+	LEFTARG   = box2df,
+	RIGHTARG  = box2df,
+	PROCEDURE = overlaps_2d,
+	COMMUTATOR = &&
+);
+-- Availability: 2.3.0		
+CREATE OPERATOR @ (
+	LEFTARG   = box2df,
+	RIGHTARG  = box2df,
+	PROCEDURE = is_contained_2d,
+	COMMUTATOR = ~
+);
+-- Availability: 2.3.0		
+CREATE OPERATOR ~ (
+	LEFTARG   = box2df,
+	RIGHTARG  = box2df,
+	PROCEDURE = contains_2d,
+	COMMUTATOR = @
+);
+	END IF;
+	
+----------------------------
+-- nd operators           --
+----------------------------
+
+-- Availability: 2.3.0
+CREATE OR REPLACE FUNCTION overlaps_nd(gidx, geometry)
+RETURNS boolean
+AS 'MODULE_PATHNAME','gserialized_gidx_geom_overlaps'
+LANGUAGE 'c' IMMUTABLE STRICT;
+
+-- Availability: 2.3.0
+CREATE OR REPLACE FUNCTION overlaps_nd(gidx, gidx)
+RETURNS boolean
+AS 'MODULE_PATHNAME','gserialized_gidx_gidx_overlaps'
+LANGUAGE 'c' IMMUTABLE STRICT;
+
+	IF NOT EXISTS(SELECT 1 from pg_operator WHERE oprname = '&&&' AND 
+		oprleft::regtype::text LIKE '%gidx' AND oprright::regtype::text LIKE '%geometry' ) THEN
+
+-- Availability: 2.3.0		
+CREATE OPERATOR &&& (
+	LEFTARG    = gidx,
+	RIGHTARG   = geometry,
+	PROCEDURE  = overlaps_nd,
+	COMMUTATOR = &&&
+);
+	
+-- Availability: 2.3.0
+CREATE OR REPLACE FUNCTION overlaps_nd(geometry, gidx)
+RETURNS boolean
+AS 
+	'SELECT $2 OPERATOR(@extschema at .&&&) $1;'
+LANGUAGE SQL IMMUTABLE STRICT;
+
+	
+-- Availability: 2.3.0
+CREATE OPERATOR &&& (
+	LEFTARG    = geometry,
+	RIGHTARG   = gidx,
+	PROCEDURE  = overlaps_nd,
+	COMMUTATOR = &&&
+);
+
+
+-- Availability: 2.3.0
+CREATE OPERATOR &&& (
+	LEFTARG   = gidx,
+	RIGHTARG  = gidx,
+	PROCEDURE = overlaps_nd,
+	COMMUTATOR = &&&
+);
+
+	END IF;
+	
+	------------------------------
+	-- Create operator families --
+	------------------------------
+	
+	-------------
+	-- 2D case --
+	-------------
+	IF NOT EXISTS(SELECT 1 FROM pg_opfamily WHERE opfname = 'brin_geometry_inclusion_ops_2d') THEN
+	
+CREATE OPERATOR FAMILY brin_geometry_inclusion_ops_2d USING brin;
+
+	END IF;
+	
+-- Availability: 2.3.0
+CREATE OR REPLACE FUNCTION geom2d_brin_inclusion_add_value(internal, internal, internal, internal) RETURNS boolean
+	AS 'MODULE_PATHNAME','geom2d_brin_inclusion_add_value'
+	LANGUAGE 'c';
+	
+-- Availability: 2.3.0
+CREATE OR REPLACE FUNCTION geom3d_brin_inclusion_add_value(internal, internal, internal, internal) RETURNS boolean
+	AS 'MODULE_PATHNAME','geom3d_brin_inclusion_add_value'
+	LANGUAGE 'c';
+	
+-- Availability: 2.3.0
+CREATE OR REPLACE FUNCTION geom4d_brin_inclusion_add_value(internal, internal, internal, internal) RETURNS boolean
+	AS 'MODULE_PATHNAME','geom4d_brin_inclusion_add_value'
+	LANGUAGE 'c';
+		
+	IF NOT EXISTS(SELECT 1 FROM pg_opclass WHERE opcname = 'brin_geometry_inclusion_ops_2d') THEN
+	
+-- Availability: 2.3.0
+CREATE OPERATOR CLASS brin_geometry_inclusion_ops_2d
+  DEFAULT FOR TYPE geometry
+  USING brin
+  FAMILY brin_geometry_inclusion_ops_2d AS
+    OPERATOR      3        &&(geometry, geometry),
+    OPERATOR      7        ~(geometry, geometry),
+    OPERATOR      8        @(geometry, geometry),
+    FUNCTION      1        brin_inclusion_opcinfo(internal) ,
+    FUNCTION      2        geom2d_brin_inclusion_add_value(internal, internal, internal, internal) ,
+    FUNCTION      3        brin_inclusion_consistent(internal, internal, internal) ,
+    FUNCTION      4        brin_inclusion_union(internal, internal, internal) ,
+  STORAGE box2df;
+
+ALTER OPERATOR FAMILY brin_geometry_inclusion_ops_2d USING brin ADD
+    OPERATOR      3         &&(box2df, geometry),
+    OPERATOR      7         ~(box2df, geometry),
+    OPERATOR      8         @(box2df, geometry),
+
+    OPERATOR      3         &&(geometry, box2df),
+    OPERATOR      7         ~(geometry, box2df),
+    OPERATOR      8         @(geometry, box2df),
+
+    OPERATOR      3         &&(box2df, box2df),
+    OPERATOR      7         ~(box2df, box2df),
+    OPERATOR      8         @(box2df, box2df);
+
+	END IF;
+	
+		-------------
+		-- 3D case --
+		-------------
+	IF NOT EXISTS(SELECT 1 FROM pg_opfamily WHERE opfname = 'brin_geometry_inclusion_ops_3d') THEN
+-- Availability: 2.3.0
+CREATE OPERATOR FAMILY brin_geometry_inclusion_ops_3d USING brin;
+
+	END IF;	
+	
+	IF NOT EXISTS(SELECT 1 FROM pg_opclass WHERE opcname = 'brin_geometry_inclusion_ops_3d') THEN
+	
+-- Availability: 2.3.0
+CREATE OPERATOR CLASS brin_geometry_inclusion_ops_3d
+  FOR TYPE geometry
+  USING brin
+  FAMILY brin_geometry_inclusion_ops_3d AS
+    OPERATOR      3        &&&(geometry, geometry),
+    FUNCTION      1        brin_inclusion_opcinfo(internal) ,
+    FUNCTION      2        geom3d_brin_inclusion_add_value(internal, internal, internal, internal) ,
+    FUNCTION      3        brin_inclusion_consistent(internal, internal, internal) ,
+    FUNCTION      4        brin_inclusion_union(internal, internal, internal) ,
+  STORAGE gidx;
+
+ALTER OPERATOR FAMILY brin_geometry_inclusion_ops_3d USING brin ADD
+    OPERATOR      3         &&&(gidx, geometry),
+
+    OPERATOR      3         &&&(geometry, gidx),
+
+    OPERATOR      3         &&&(gidx, gidx);
+	
+	END IF;
+	
+	
+		-------------
+		-- 4D case --
+		-------------
+	IF NOT EXISTS(SELECT 1 FROM pg_opfamily WHERE opfname = 'brin_geometry_inclusion_ops_4d') THEN
+	
+-- Availability: 2.3.0
+CREATE OPERATOR FAMILY brin_geometry_inclusion_ops_4d USING brin;
+
+-- Availability: 2.3.0
+CREATE OPERATOR CLASS brin_geometry_inclusion_ops_4d
+  FOR TYPE geometry
+  USING brin
+  FAMILY brin_geometry_inclusion_ops_4d AS
+    OPERATOR      3        &&&(geometry, geometry),
+    FUNCTION      1        brin_inclusion_opcinfo(internal) ,
+    FUNCTION      2        geom4d_brin_inclusion_add_value(internal, internal, internal, internal) ,
+    FUNCTION      3        brin_inclusion_consistent(internal, internal, internal) ,
+    FUNCTION      4        brin_inclusion_union(internal, internal, internal) ,
+  STORAGE gidx;
+
+ALTER OPERATOR FAMILY brin_geometry_inclusion_ops_4d USING brin ADD
+    OPERATOR      3         &&&(gidx, geometry),
+
+    OPERATOR      3         &&&(geometry, gidx),
+
+    OPERATOR      3         &&&(gidx, gidx);
+	
+	END IF;
+		
+---------------------------------------------------------------
+-- END
+---------------------------------------------------------------
+END;
+$$;
+
+-----------------------
+-- BRIN support end
+-----------------------
+#endif
\ No newline at end of file



More information about the postgis-tickets mailing list