From c0998a4e638048196a6abfab1504eca6b1a1462f Mon Sep 17 00:00:00 2001 From: Regina Obe Date: Sun, 22 Jan 2017 05:46:18 +0000 Subject: [PATCH] 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) git-svn-id: http://svn.osgeo.org/postgis/trunk@15290 b70326c6-7e19-0410-871a-916f4a2858ee --- postgis/geography.sql.in | 89 +-------- postgis/geography_brin.sql.in | 104 +++++++++++ postgis/postgis.sql.in | 295 +---------------------------- postgis/postgis_brin.sql.in | 336 ++++++++++++++++++++++++++++++++++ 4 files changed, 444 insertions(+), 380 deletions(-) create mode 100644 postgis/geography_brin.sql.in create mode 100644 postgis/postgis_brin.sql.in diff --git a/postgis/geography.sql.in b/postgis/geography.sql.in index 9caf40d95..36a21555b 100644 --- a/postgis/geography.sql.in +++ b/postgis/geography.sql.in @@ -286,93 +286,8 @@ CREATE OPERATOR CLASS gist_geography_ops FUNCTION 6 geography_gist_picksplit (internal, internal), FUNCTION 7 geography_gist_same (box2d, box2d, internal); -#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; - --- 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@.&&) $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 +-- moved to separate file cause its invovled +#include "geography_brin.sql.in" -- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -- B-Tree Functions diff --git a/postgis/geography_brin.sql.in b/postgis/geography_brin.sql.in new file mode 100644 index 000000000..d6503d8fe --- /dev/null +++ b/postgis/geography_brin.sql.in @@ -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@.&&) $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 diff --git a/postgis/postgis.sql.in b/postgis/postgis.sql.in index 28ac94e32..e408f6533 100644 --- a/postgis/postgis.sql.in +++ b/postgis/postgis.sql.in @@ -5569,300 +5569,9 @@ CREATE OR REPLACE FUNCTION ST_InterpolatePoint(Line geometry, Point geometry) AS 'MODULE_PATHNAME', 'ST_InterpolatePoint' LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL; -#if POSTGIS_PGSQL_VERSION > 94 --------------------------------------------------------------------- --- BRIN support -- --------------------------------------------------------------------- ---------------------------------- --- 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; - --- 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@.&&) $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@.&&&) $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 +-- moved to separate file cause its invovled +#include "postgis_brin.sql.in" --------------------------------------------------------------- -- USER CONTRIBUTED diff --git a/postgis/postgis_brin.sql.in b/postgis/postgis_brin.sql.in new file mode 100644 index 000000000..d3dd98398 --- /dev/null +++ b/postgis/postgis_brin.sql.in @@ -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@.@) $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@.~) $1;' +LANGUAGE SQL IMMUTABLE STRICT; + +-- Availability: 2.3.0 +CREATE OR REPLACE FUNCTION overlaps_2d(geometry, box2df) +RETURNS boolean +AS + 'SELECT $2 OPERATOR(@extschema@.&&) $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@.&&&) $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 -- 2.40.0