# HG changeset patch # User jbe # Date 1606676133 -3600 # Node ID b5bc6b35b7163280c22208f0015ab62cce23872b # Parent 8fa0ef5e4ee6fba99d6749ce6434779a8a43a3f3 Allow non-superusers to install extension; Properly support loading extension into schema; Do not allow schema relocation diff -r 8fa0ef5e4ee6 -r b5bc6b35b716 latlon--0.14--0.15.sql --- a/latlon--0.14--0.15.sql Wed Feb 12 13:11:04 2020 +0100 +++ b/latlon--0.14--0.15.sql Sun Nov 29 19:55:33 2020 +0100 @@ -1,3 +1,5 @@ +SET LOCAL search_path TO @extschema@; + ------------------------------------------------------------ -- dummy input/output functions for dummy index key types -- @@ -275,6 +277,18 @@ LANGUAGE C IMMUTABLE STRICT AS '$libdir/latlon-v0010', 'pgl_create_epoint'; +CREATE OR REPLACE FUNCTION epoint_latlon(float8, float8) + RETURNS epoint + LANGUAGE SQL IMMUTABLE STRICT AS $$ + SELECT @extschema@.epoint($1, $2) + $$; + +CREATE OR REPLACE FUNCTION epoint_lonlat(float8, float8) + RETURNS epoint + LANGUAGE SQL IMMUTABLE STRICT AS $$ + SELECT @extschema@.epoint($2, $1) + $$; + CREATE OR REPLACE FUNCTION epoint_with_sample_count(epoint, int4) RETURNS epoint_with_sample_count LANGUAGE C IMMUTABLE STRICT @@ -305,6 +319,74 @@ LANGUAGE C IMMUTABLE STRICT AS '$libdir/latlon-v0010', 'pgl_create_ecircle_from_epoint'; +CREATE OR REPLACE FUNCTION ecluster_concat(ecluster[]) + RETURNS ecluster + LANGUAGE sql IMMUTABLE STRICT AS $$ + SELECT pg_catalog.array_to_string($1, ' ')::@extschema@.ecluster + $$; + +CREATE OR REPLACE FUNCTION ecluster_concat(ecluster, ecluster) + RETURNS ecluster + LANGUAGE sql IMMUTABLE STRICT AS $$ + SELECT ( + $1::pg_catalog.text OPERATOR(pg_catalog.||) ' ' OPERATOR(pg_catalog.||) + $2::pg_catalog.text + )::@extschema@.ecluster + $$; + +CREATE OR REPLACE FUNCTION ecluster_create_multipoint(epoint[]) + RETURNS ecluster + LANGUAGE sql IMMUTABLE STRICT AS $$ + SELECT + pg_catalog.array_to_string( + pg_catalog.array_agg( + 'point (' OPERATOR(pg_catalog.||) unnest OPERATOR(pg_catalog.||) ')' + ), + ' ' + )::@extschema@.ecluster + FROM pg_catalog.unnest($1) + $$; + +CREATE OR REPLACE FUNCTION ecluster_create_path(epoint[]) + RETURNS ecluster + LANGUAGE sql IMMUTABLE STRICT AS $$ + SELECT CASE WHEN "str" OPERATOR(pg_catalog.=) '' THEN + 'empty'::@extschema@.ecluster + ELSE + ( + 'path (' OPERATOR(pg_catalog.||) "str" OPERATOR(pg_catalog.||) ')' + )::@extschema@.ecluster + END + FROM pg_catalog.array_to_string($1, ' ') AS "str" + $$; + +CREATE OR REPLACE FUNCTION ecluster_create_outline(epoint[]) + RETURNS ecluster + LANGUAGE sql IMMUTABLE STRICT AS $$ + SELECT CASE WHEN "str" OPERATOR(pg_catalog.=) '' THEN + 'empty'::@extschema@.ecluster + ELSE + ( + 'outline (' OPERATOR(pg_catalog.||) "str" OPERATOR(pg_catalog.||) ')' + )::@extschema@.ecluster + END + FROM pg_catalog.array_to_string($1, ' ') AS "str" + $$; + +CREATE OR REPLACE FUNCTION ecluster_create_polygon(epoint[]) + RETURNS ecluster + LANGUAGE sql IMMUTABLE STRICT AS $$ + SELECT CASE WHEN "str" OPERATOR(pg_catalog.=) '' THEN + 'empty'::@extschema@.ecluster + ELSE + ( + 'polygon (' OPERATOR(pg_catalog.||) pg_catalog.array_to_string($1, ' ') + OPERATOR(pg_catalog.||) ')' + )::@extschema@.ecluster + END + FROM pg_catalog.array_to_string($1, ' ') AS "str" + $$; + ---------------------- -- getter functions -- @@ -350,6 +432,51 @@ LANGUAGE C IMMUTABLE STRICT AS '$libdir/latlon-v0010', 'pgl_ecircle_radius'; +CREATE OR REPLACE FUNCTION ecluster_extract_points(ecluster) + RETURNS SETOF epoint + LANGUAGE sql IMMUTABLE STRICT AS $$ + SELECT "match"[2]::@extschema@.epoint + FROM pg_catalog.regexp_matches( + $1::pg_catalog.text, e'(^| )point \\(([^)]+)\\)', 'g' + ) AS "match" + $$; + +CREATE OR REPLACE FUNCTION ecluster_extract_paths(ecluster) + RETURNS SETOF epoint[] + LANGUAGE sql IMMUTABLE STRICT AS $$ + SELECT ( + SELECT pg_catalog.array_agg("m2"[1]::@extschema@.epoint) + FROM pg_catalog.regexp_matches("m1"[2], e'[^ ]+ [^ ]+', 'g') AS "m2" + ) + FROM pg_catalog.regexp_matches( + $1::pg_catalog.text, e'(^| )path \\(([^)]+)\\)', 'g' + ) AS "m1" + $$; + +CREATE OR REPLACE FUNCTION ecluster_extract_outlines(ecluster) + RETURNS SETOF epoint[] + LANGUAGE sql IMMUTABLE STRICT AS $$ + SELECT ( + SELECT pg_catalog.array_agg("m2"[1]::@extschema@.epoint) + FROM pg_catalog.regexp_matches("m1"[2], e'[^ ]+ [^ ]+', 'g') AS "m2" + ) + FROM pg_catalog.regexp_matches( + $1::pg_catalog.text, e'(^| )outline \\(([^)]+)\\)', 'g' + ) AS "m1" + $$; + +CREATE OR REPLACE FUNCTION ecluster_extract_polygons(ecluster) + RETURNS SETOF epoint[] + LANGUAGE sql IMMUTABLE STRICT AS $$ + SELECT ( + SELECT pg_catalog.array_agg("m2"[1]::@extschema@.epoint) + FROM pg_catalog.regexp_matches("m1"[2], e'[^ ]+ [^ ]+', 'g') AS "m2" + ) + FROM pg_catalog.regexp_matches( + $1::pg_catalog.text, e'(^| )polygon \\(([^)]+)\\)', 'g' + ) AS "m1" + $$; + --------------- -- operators -- @@ -455,6 +582,145 @@ LANGUAGE C IMMUTABLE STRICT AS '$libdir/latlon-v0010', 'pgl_ecluster_epoint_sc_fair_distance'; +CREATE OR REPLACE FUNCTION epoint_ebox_overlap_commutator(ebox, epoint) + RETURNS boolean + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&) $1'; + +CREATE OR REPLACE FUNCTION epoint_ecircle_overlap_commutator(ecircle, epoint) + RETURNS boolean + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&) $1'; + +CREATE OR REPLACE FUNCTION epoint_ecluster_overlap_commutator(ecluster, epoint) + RETURNS boolean + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&) $1'; + +CREATE OR REPLACE FUNCTION ecircle_ecluster_overlap_commutator(ecluster, ecircle) + RETURNS boolean + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&) $1'; + +CREATE OR REPLACE FUNCTION ebox_ecircle_overlap_castwrap(ebox, ecircle) + RETURNS boolean + LANGUAGE sql IMMUTABLE + AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.&&) $2'; + +CREATE OR REPLACE FUNCTION ebox_ecircle_overlap_castwrap(ecircle, ebox) + RETURNS boolean + LANGUAGE sql IMMUTABLE + AS 'SELECT $1 OPERATOR(@extschema@.&&) $2::@extschema@.ecluster'; + +CREATE OR REPLACE FUNCTION ebox_ecluster_overlap_castwrap(ebox, ecluster) + RETURNS boolean + LANGUAGE sql IMMUTABLE + AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.&&) $2'; + +CREATE OR REPLACE FUNCTION ebox_ecluster_overlap_castwrap(ecluster, ebox) + RETURNS boolean + LANGUAGE sql IMMUTABLE + AS 'SELECT $1 OPERATOR(@extschema@.&&) $2::@extschema@.ecluster'; + +CREATE OR REPLACE FUNCTION epoint_ecluster_may_overlap_commutator(ecluster, epoint) + RETURNS boolean + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&+) $1'; + +CREATE OR REPLACE FUNCTION ebox_ecircle_may_overlap_commutator(ecircle, ebox) + RETURNS boolean + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&+) $1'; + +CREATE OR REPLACE FUNCTION ebox_ecluster_may_overlap_commutator(ecluster, ebox) + RETURNS boolean + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&+) $1'; + +CREATE OR REPLACE FUNCTION ecircle_ecluster_may_overlap_commutator(ecluster, ecircle) + RETURNS boolean + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&+) $1'; + +CREATE OR REPLACE FUNCTION ecluster_contains_commutator(ecluster, ecluster) + RETURNS boolean + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.@>) $1'; + +CREATE OR REPLACE FUNCTION ebox_contains_castwrap(ebox, ebox) + RETURNS boolean + LANGUAGE sql IMMUTABLE AS $$ + SELECT + $1::@extschema@.ecluster OPERATOR(@extschema@.@>) $2::@extschema@.ecluster + $$; + +CREATE OR REPLACE FUNCTION ebox_contains_swapped_castwrap(ebox, ebox) + RETURNS boolean + LANGUAGE sql IMMUTABLE AS $$ + SELECT + $2::@extschema@.ecluster OPERATOR(@extschema@.@>) $1::@extschema@.ecluster + $$; + +CREATE OR REPLACE FUNCTION ebox_ecluster_contains_castwrap(ebox, ecluster) + RETURNS boolean + LANGUAGE sql IMMUTABLE + AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.@>) $2'; + +CREATE OR REPLACE FUNCTION ebox_ecluster_contains_castwrap(ecluster, ebox) + RETURNS boolean + LANGUAGE sql IMMUTABLE + AS 'SELECT $2::@extschema@.ecluster OPERATOR(@extschema@.@>) $1'; + +CREATE OR REPLACE FUNCTION ecluster_ebox_contains_castwrap(ecluster, ebox) + RETURNS boolean + LANGUAGE sql IMMUTABLE + AS 'SELECT $1 OPERATOR(@extschema@.@>) $2::@extschema@.ecluster'; + +CREATE OR REPLACE FUNCTION ecluster_ebox_contains_castwrap(ebox, ecluster) + RETURNS boolean + LANGUAGE sql IMMUTABLE + AS 'SELECT $2 OPERATOR(@extschema@.@>) $1::@extschema@.ecluster'; + +CREATE OR REPLACE FUNCTION epoint_ecircle_distance_commutator(ecircle, epoint) + RETURNS float8 + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.<->) $1'; + +CREATE OR REPLACE FUNCTION epoint_ecluster_distance_commutator(ecluster, epoint) + RETURNS float8 + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.<->) $1'; + +CREATE OR REPLACE FUNCTION ecircle_ecluster_distance_commutator(ecluster, ecircle) + RETURNS float8 + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.<->) $1'; + +CREATE OR REPLACE FUNCTION epoint_ebox_distance_castwrap(epoint, ebox) + RETURNS float8 + LANGUAGE sql IMMUTABLE + AS 'SELECT $1 OPERATOR(@extschema@.<->) $2::@extschema@.ecluster'; + +CREATE OR REPLACE FUNCTION epoint_ebox_distance_castwrap(ebox, epoint) + RETURNS float8 + LANGUAGE sql IMMUTABLE + AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.<->) $2'; + +CREATE OR REPLACE FUNCTION ebox_distance_castwrap(ebox, ebox) + RETURNS float8 + LANGUAGE sql IMMUTABLE AS $$ + SELECT + $1::@extschema@.ecluster OPERATOR(@extschema@.<->) $2::@extschema@.ecluster + $$; + +CREATE OR REPLACE FUNCTION ebox_ecircle_distance_castwrap(ebox, ecircle) + RETURNS float8 + LANGUAGE sql IMMUTABLE + AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.<->) $2'; + +CREATE OR REPLACE FUNCTION ebox_ecircle_distance_castwrap(ecircle, ebox) + RETURNS float8 + LANGUAGE sql IMMUTABLE + AS 'SELECT $1 OPERATOR(@extschema@.<->) $2::@extschema@.ecluster'; + +CREATE OR REPLACE FUNCTION ebox_ecluster_distance_castwrap(ebox, ecluster) + RETURNS float8 + LANGUAGE sql IMMUTABLE + AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.<->) $2'; + +CREATE OR REPLACE FUNCTION ebox_ecluster_distance_castwrap(ecluster, ebox) + RETURNS float8 + LANGUAGE sql IMMUTABLE + AS 'SELECT $1 OPERATOR(@extschema@.<->) $2::@extschema@.ecluster'; + ---------------- -- GiST index -- @@ -510,3 +776,225 @@ LANGUAGE C STRICT AS '$libdir/latlon-v0010', 'pgl_gist_distance'; + +--------------------- +-- alias functions -- +--------------------- + +CREATE OR REPLACE FUNCTION distance(epoint, epoint) + RETURNS float8 + LANGUAGE sql IMMUTABLE AS 'SELECT $1 OPERATOR(@extschema@.<->) $2'; + +CREATE OR REPLACE FUNCTION distance(ecluster, epoint) + RETURNS float8 + LANGUAGE sql IMMUTABLE AS 'SELECT $1 OPERATOR(@extschema@.<->) $2'; + +CREATE OR REPLACE FUNCTION distance_within(epoint, epoint, float8) + RETURNS boolean + LANGUAGE sql IMMUTABLE + AS 'SELECT $1 OPERATOR(@extschema@.&&) @extschema@.ecircle($2, $3)'; + +CREATE OR REPLACE FUNCTION distance_within(ecluster, epoint, float8) + RETURNS boolean + LANGUAGE sql IMMUTABLE + AS 'SELECT $1 OPERATOR(@extschema@.&&) @extschema@.ecircle($2, $3)'; + +CREATE OR REPLACE FUNCTION fair_distance(ecluster, epoint, int4 = 10000) + RETURNS float8 + LANGUAGE sql IMMUTABLE AS $$ + SELECT + $1 OPERATOR(@extschema@.<=>) @extschema@.epoint_with_sample_count($2, $3) + $$; + + +-------------------------------- +-- other data storage formats -- +-------------------------------- + +CREATE OR REPLACE FUNCTION coords_to_epoint(float8, float8, text = 'epoint') + RETURNS epoint + LANGUAGE plpgsql IMMUTABLE STRICT AS $$ + DECLARE + "result" @extschema@.epoint; + BEGIN + IF $3 OPERATOR(pg_catalog.=) 'epoint_lonlat' THEN + -- avoid dynamic command execution for better performance + RETURN @extschema@.epoint($2, $1); + END IF; + IF + $3 OPERATOR(pg_catalog.=) 'epoint' OR + $3 OPERATOR(pg_catalog.=) 'epoint_latlon' + THEN + -- avoid dynamic command execution for better performance + RETURN @extschema@.epoint($1, $2); + END IF; + EXECUTE + 'SELECT ' OPERATOR(pg_catalog.||) $3 OPERATOR(pg_catalog.||) '($1, $2)' INTO STRICT "result" USING $1, $2; + RETURN "result"; + END; + $$; + +CREATE OR REPLACE FUNCTION GeoJSON_LinearRing_vertices(jsonb, text = 'epoint_lonlat') + RETURNS SETOF jsonb + LANGUAGE sql IMMUTABLE STRICT AS $$ + SELECT "result" FROM + ( SELECT pg_catalog.jsonb_array_length($1) - 1 ) + AS "lastindex_row" ("lastindex") + CROSS JOIN LATERAL pg_catalog.jsonb_array_elements( + CASE WHEN + @extschema@.coords_to_epoint( + ($1 OPERATOR(pg_catalog.->) 0 OPERATOR(pg_catalog.->>) 0) + ::pg_catalog.float8, + ($1 OPERATOR(pg_catalog.->) 0 OPERATOR(pg_catalog.->>) 1) + ::pg_catalog.float8, + $2 + ) OPERATOR(pg_catalog.=) @extschema@.coords_to_epoint( + ($1 OPERATOR(pg_catalog.->) "lastindex" OPERATOR(pg_catalog.->>) 0) + ::pg_catalog.float8, + ($1 OPERATOR(pg_catalog.->) "lastindex" OPERATOR(pg_catalog.->>) 1) + ::pg_catalog.float8, + $2 + ) + THEN + $1 - "lastindex" + ELSE + $1 + END + ) AS "result_row" ("result") + $$; + +CREATE OR REPLACE FUNCTION GeoJSON_to_epoint(jsonb, text = 'epoint_lonlat') + RETURNS epoint + LANGUAGE sql IMMUTABLE STRICT AS $$ + SELECT CASE + WHEN $1 OPERATOR(pg_catalog.->>) 'type' OPERATOR(pg_catalog.=) 'Point' THEN + @extschema@.coords_to_epoint( + ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 0) + ::pg_catalog.float8, + ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 1) + ::pg_catalog.float8, + $2 + ) + WHEN $1->>'type' = 'Feature' THEN + @extschema@.GeoJSON_to_epoint($1 OPERATOR(pg_catalog.->) 'geometry', $2) + ELSE + NULL + END + $$; + +CREATE OR REPLACE FUNCTION GeoJSON_to_ecluster(jsonb, text = 'epoint_lonlat') + RETURNS ecluster + LANGUAGE sql IMMUTABLE STRICT AS $$ + SELECT CASE $1 OPERATOR(pg_catalog.->>) 'type' + WHEN 'Point' THEN + @extschema@.coords_to_epoint( + ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 0) + ::pg_catalog.float8, + ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 1) + ::pg_catalog.float8, + $2 + )::@extschema@.ecluster + WHEN 'MultiPoint' THEN + ( SELECT @extschema@.ecluster_create_multipoint(pg_catalog.array_agg( + @extschema@.coords_to_epoint( + ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, + ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, + $2 + ) + )) + FROM pg_catalog.jsonb_array_elements( + $1 OPERATOR(pg_catalog.->) 'coordinates' + ) AS "coord" + ) + WHEN 'LineString' THEN + ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg( + @extschema@.coords_to_epoint( + ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, + ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, + $2 + ) + )) + FROM pg_catalog.jsonb_array_elements( + $1 OPERATOR(pg_catalog.->) 'coordinates' + ) AS "coord" + ) + WHEN 'MultiLineString' THEN + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( + ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg( + coords_to_epoint( + ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, + ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, + $2 + ) + )) + FROM pg_catalog.jsonb_array_elements("coord_array") AS "coord" + ) + )) + FROM pg_catalog.jsonb_array_elements( + $1 OPERATOR(pg_catalog.->) 'coordinates' + ) AS "coord_array" + ) + WHEN 'Polygon' THEN + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( + ( SELECT @extschema@.ecluster_create_polygon(pg_catalog.array_agg( + @extschema@.coords_to_epoint( + ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, + ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, + $2 + ) + )) + FROM @extschema@.GeoJSON_LinearRing_vertices("coord_array", $2) + AS "coord" + ) + )) + FROM pg_catalog.jsonb_array_elements( + $1 OPERATOR(pg_catalog.->) 'coordinates' + ) AS "coord_array" + ) + WHEN 'MultiPolygon' THEN + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( + ( SELECT pg_catalog.ecluster_create_polygon(pg_catalog.array_agg( + @extschema@.coords_to_epoint( + ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, + ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, + $2 + ) + )) + FROM @extschema@.GeoJSON_LinearRing_vertices("coord_array", $2) + AS "coord" + ) + )) + FROM pg_catalog.jsonb_array_elements("coord_array_array") + AS "coord_array" + ) + )) + FROM jsonb_array_elements( + $1 OPERATOR(pg_catalog.->) 'coordinates' + ) AS "coord_array_array" + ) + WHEN 'GeometryCollection' THEN + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( + @extschema@.GeoJSON_to_ecluster("geometry", $2) + )) + FROM pg_catalog.jsonb_array_elements( + $1 OPERATOR(pg_catalog.->) 'geometries' + ) AS "geometry" + ) + WHEN 'Feature' THEN + @extschema@.GeoJSON_to_ecluster( + $1 OPERATOR(pg_catalog.->) 'geometry', $2 + ) + WHEN 'FeatureCollection' THEN + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( + @extschema@.GeoJSON_to_ecluster("feature", $2) + )) + FROM pg_catalog.jsonb_array_elements( + $1 OPERATOR(pg_catalog.->) 'features' + ) AS "feature" + ) + ELSE + NULL + END + $$; + diff -r 8fa0ef5e4ee6 -r b5bc6b35b716 latlon--0.15.sql --- a/latlon--0.15.sql Wed Feb 12 13:11:04 2020 +0100 +++ b/latlon--0.15.sql Sun Nov 29 19:55:33 2020 +0100 @@ -1,3 +1,5 @@ +SET LOCAL search_path TO @extschema@; + ---------------------------------------- -- forward declarations (shell types) -- @@ -568,13 +570,13 @@ CREATE FUNCTION epoint_latlon(float8, float8) RETURNS epoint LANGUAGE SQL IMMUTABLE STRICT AS $$ - SELECT epoint($1, $2) + SELECT @extschema@.epoint($1, $2) $$; CREATE FUNCTION epoint_lonlat(float8, float8) RETURNS epoint LANGUAGE SQL IMMUTABLE STRICT AS $$ - SELECT epoint($2, $1) + SELECT @extschema@.epoint($2, $1) $$; CREATE FUNCTION epoint_with_sample_count(epoint, int4) @@ -610,48 +612,69 @@ CREATE FUNCTION ecluster_concat(ecluster[]) RETURNS ecluster LANGUAGE sql IMMUTABLE STRICT AS $$ - SELECT array_to_string($1, ' ')::ecluster + SELECT pg_catalog.array_to_string($1, ' ')::@extschema@.ecluster $$; CREATE FUNCTION ecluster_concat(ecluster, ecluster) RETURNS ecluster LANGUAGE sql IMMUTABLE STRICT AS $$ - SELECT ($1::text || ' ' || $2::text)::ecluster + SELECT ( + $1::pg_catalog.text OPERATOR(pg_catalog.||) ' ' OPERATOR(pg_catalog.||) + $2::pg_catalog.text + )::@extschema@.ecluster $$; CREATE FUNCTION ecluster_create_multipoint(epoint[]) RETURNS ecluster LANGUAGE sql IMMUTABLE STRICT AS $$ SELECT - array_to_string(array_agg('point (' || unnest || ')'), ' ')::ecluster - FROM unnest($1) + pg_catalog.array_to_string( + pg_catalog.array_agg( + 'point (' OPERATOR(pg_catalog.||) unnest OPERATOR(pg_catalog.||) ')' + ), + ' ' + )::@extschema@.ecluster + FROM pg_catalog.unnest($1) $$; CREATE FUNCTION ecluster_create_path(epoint[]) RETURNS ecluster LANGUAGE sql IMMUTABLE STRICT AS $$ - SELECT CASE WHEN "str" = '' THEN 'empty'::ecluster ELSE - ('path (' || array_to_string($1, ' ') || ')')::ecluster + SELECT CASE WHEN "str" OPERATOR(pg_catalog.=) '' THEN + 'empty'::@extschema@.ecluster + ELSE + ( + 'path (' OPERATOR(pg_catalog.||) "str" OPERATOR(pg_catalog.||) ')' + )::@extschema@.ecluster END - FROM array_to_string($1, ' ') AS "str" + FROM pg_catalog.array_to_string($1, ' ') AS "str" $$; CREATE FUNCTION ecluster_create_outline(epoint[]) RETURNS ecluster LANGUAGE sql IMMUTABLE STRICT AS $$ - SELECT CASE WHEN "str" = '' THEN 'empty'::ecluster ELSE - ('outline (' || array_to_string($1, ' ') || ')')::ecluster + SELECT CASE WHEN "str" OPERATOR(pg_catalog.=) '' THEN + 'empty'::@extschema@.ecluster + ELSE + ( + 'outline (' OPERATOR(pg_catalog.||) "str" OPERATOR(pg_catalog.||) ')' + )::@extschema@.ecluster END - FROM array_to_string($1, ' ') AS "str" + FROM pg_catalog.array_to_string($1, ' ') AS "str" $$; CREATE FUNCTION ecluster_create_polygon(epoint[]) RETURNS ecluster LANGUAGE sql IMMUTABLE STRICT AS $$ - SELECT CASE WHEN "str" = '' THEN 'empty'::ecluster ELSE - ('polygon (' || array_to_string($1, ' ') || ')')::ecluster + SELECT CASE WHEN "str" OPERATOR(pg_catalog.=) '' THEN + 'empty'::@extschema@.ecluster + ELSE + ( + 'polygon (' OPERATOR(pg_catalog.||) pg_catalog.array_to_string($1, ' ') + OPERATOR(pg_catalog.||) ')' + )::@extschema@.ecluster END - FROM array_to_string($1, ' ') AS "str" + FROM pg_catalog.array_to_string($1, ' ') AS "str" $$; @@ -702,38 +725,46 @@ CREATE FUNCTION ecluster_extract_points(ecluster) RETURNS SETOF epoint LANGUAGE sql IMMUTABLE STRICT AS $$ - SELECT "match"[2]::epoint - FROM regexp_matches($1::text, e'(^| )point \\(([^)]+)\\)', 'g') AS "match" + SELECT "match"[2]::@extschema@.epoint + FROM pg_catalog.regexp_matches( + $1::pg_catalog.text, e'(^| )point \\(([^)]+)\\)', 'g' + ) AS "match" $$; CREATE FUNCTION ecluster_extract_paths(ecluster) RETURNS SETOF epoint[] LANGUAGE sql IMMUTABLE STRICT AS $$ SELECT ( - SELECT array_agg("m2"[1]::epoint) - FROM regexp_matches("m1"[2], e'[^ ]+ [^ ]+', 'g') AS "m2" + SELECT pg_catalog.array_agg("m2"[1]::@extschema@.epoint) + FROM pg_catalog.regexp_matches("m1"[2], e'[^ ]+ [^ ]+', 'g') AS "m2" ) - FROM regexp_matches($1::text, e'(^| )path \\(([^)]+)\\)', 'g') AS "m1" + FROM pg_catalog.regexp_matches( + $1::pg_catalog.text, e'(^| )path \\(([^)]+)\\)', 'g' + ) AS "m1" $$; CREATE FUNCTION ecluster_extract_outlines(ecluster) RETURNS SETOF epoint[] LANGUAGE sql IMMUTABLE STRICT AS $$ SELECT ( - SELECT array_agg("m2"[1]::epoint) - FROM regexp_matches("m1"[2], e'[^ ]+ [^ ]+', 'g') AS "m2" + SELECT pg_catalog.array_agg("m2"[1]::@extschema@.epoint) + FROM pg_catalog.regexp_matches("m1"[2], e'[^ ]+ [^ ]+', 'g') AS "m2" ) - FROM regexp_matches($1::text, e'(^| )outline \\(([^)]+)\\)', 'g') AS "m1" + FROM pg_catalog.regexp_matches( + $1::pg_catalog.text, e'(^| )outline \\(([^)]+)\\)', 'g' + ) AS "m1" $$; CREATE FUNCTION ecluster_extract_polygons(ecluster) RETURNS SETOF epoint[] LANGUAGE sql IMMUTABLE STRICT AS $$ SELECT ( - SELECT array_agg("m2"[1]::epoint) - FROM regexp_matches("m1"[2], e'[^ ]+ [^ ]+', 'g') AS "m2" + SELECT pg_catalog.array_agg("m2"[1]::@extschema@.epoint) + FROM pg_catalog.regexp_matches("m1"[2], e'[^ ]+ [^ ]+', 'g') AS "m2" ) - FROM regexp_matches($1::text, e'(^| )polygon \\(([^)]+)\\)', 'g') AS "m1" + FROM pg_catalog.regexp_matches( + $1::pg_catalog.text, e'(^| )polygon \\(([^)]+)\\)', 'g' + ) AS "m1" $$; @@ -852,7 +883,7 @@ CREATE FUNCTION epoint_ebox_overlap_commutator(ebox, epoint) RETURNS boolean - LANGUAGE sql IMMUTABLE AS 'SELECT $2 && $1'; + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&) $1'; CREATE OPERATOR && ( leftarg = ebox, @@ -874,7 +905,7 @@ CREATE FUNCTION epoint_ecircle_overlap_commutator(ecircle, epoint) RETURNS boolean - LANGUAGE sql IMMUTABLE AS 'SELECT $2 && $1'; + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&) $1'; CREATE OPERATOR && ( leftarg = ecircle, @@ -896,7 +927,7 @@ CREATE FUNCTION epoint_ecluster_overlap_commutator(ecluster, epoint) RETURNS boolean - LANGUAGE sql IMMUTABLE AS 'SELECT $2 && $1'; + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&) $1'; CREATE OPERATOR && ( leftarg = ecluster, @@ -936,7 +967,7 @@ CREATE FUNCTION ecircle_ecluster_overlap_commutator(ecluster, ecircle) RETURNS boolean - LANGUAGE sql IMMUTABLE AS 'SELECT $2 && $1'; + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&) $1'; CREATE OPERATOR && ( leftarg = ecluster, @@ -958,7 +989,8 @@ CREATE FUNCTION ebox_ecircle_overlap_castwrap(ebox, ecircle) RETURNS boolean - LANGUAGE sql IMMUTABLE AS 'SELECT $1::ecluster && $2'; + LANGUAGE sql IMMUTABLE + AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.&&) $2'; CREATE OPERATOR && ( leftarg = ebox, @@ -971,7 +1003,8 @@ CREATE FUNCTION ebox_ecircle_overlap_castwrap(ecircle, ebox) RETURNS boolean - LANGUAGE sql IMMUTABLE AS 'SELECT $1 && $2::ecluster'; + LANGUAGE sql IMMUTABLE + AS 'SELECT $1 OPERATOR(@extschema@.&&) $2::@extschema@.ecluster'; CREATE OPERATOR && ( leftarg = ecircle, @@ -984,7 +1017,8 @@ CREATE FUNCTION ebox_ecluster_overlap_castwrap(ebox, ecluster) RETURNS boolean - LANGUAGE sql IMMUTABLE AS 'SELECT $1::ecluster && $2'; + LANGUAGE sql IMMUTABLE + AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.&&) $2'; CREATE OPERATOR && ( leftarg = ebox, @@ -997,7 +1031,8 @@ CREATE FUNCTION ebox_ecluster_overlap_castwrap(ecluster, ebox) RETURNS boolean - LANGUAGE sql IMMUTABLE AS 'SELECT $1 && $2::ecluster'; + LANGUAGE sql IMMUTABLE + AS 'SELECT $1 OPERATOR(@extschema@.&&) $2::@extschema@.ecluster'; CREATE OPERATOR && ( leftarg = ecluster, @@ -1019,7 +1054,7 @@ CREATE FUNCTION epoint_ecluster_may_overlap_commutator(ecluster, epoint) RETURNS boolean - LANGUAGE sql IMMUTABLE AS 'SELECT $2 &&+ $1'; + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&+) $1'; CREATE OPERATOR &&+ ( leftarg = ecluster, @@ -1041,7 +1076,7 @@ CREATE FUNCTION ebox_ecircle_may_overlap_commutator(ecircle, ebox) RETURNS boolean - LANGUAGE sql IMMUTABLE AS 'SELECT $2 &&+ $1'; + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&+) $1'; CREATE OPERATOR &&+ ( leftarg = ecircle, @@ -1063,7 +1098,7 @@ CREATE FUNCTION ebox_ecluster_may_overlap_commutator(ecluster, ebox) RETURNS boolean - LANGUAGE sql IMMUTABLE AS 'SELECT $2 &&+ $1'; + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&+) $1'; CREATE OPERATOR &&+ ( leftarg = ecluster, @@ -1085,7 +1120,7 @@ CREATE FUNCTION ecircle_ecluster_may_overlap_commutator(ecluster, ecircle) RETURNS boolean - LANGUAGE sql IMMUTABLE AS 'SELECT $2 &&+ $1'; + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&+) $1'; CREATE OPERATOR &&+ ( leftarg = ecluster, @@ -1152,7 +1187,7 @@ CREATE FUNCTION ecluster_contains_commutator(ecluster, ecluster) RETURNS boolean - LANGUAGE sql IMMUTABLE AS 'SELECT $2 @> $1'; + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.@>) $1'; CREATE OPERATOR <@ ( leftarg = ecluster, @@ -1165,7 +1200,10 @@ CREATE FUNCTION ebox_contains_castwrap(ebox, ebox) RETURNS boolean - LANGUAGE sql IMMUTABLE AS 'SELECT $1::ecluster @> $2::ecluster'; + LANGUAGE sql IMMUTABLE AS $$ + SELECT + $1::@extschema@.ecluster OPERATOR(@extschema@.@>) $2::@extschema@.ecluster + $$; CREATE OPERATOR @> ( leftarg = ebox, @@ -1178,7 +1216,10 @@ CREATE FUNCTION ebox_contains_swapped_castwrap(ebox, ebox) RETURNS boolean - LANGUAGE sql IMMUTABLE AS 'SELECT $2::ecluster @> $1::ecluster'; + LANGUAGE sql IMMUTABLE AS $$ + SELECT + $2::@extschema@.ecluster OPERATOR(@extschema@.@>) $1::@extschema@.ecluster + $$; CREATE OPERATOR <@ ( leftarg = ebox, @@ -1191,7 +1232,8 @@ CREATE FUNCTION ebox_ecluster_contains_castwrap(ebox, ecluster) RETURNS boolean - LANGUAGE sql IMMUTABLE AS 'SELECT $1::ecluster @> $2'; + LANGUAGE sql IMMUTABLE + AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.@>) $2'; CREATE OPERATOR @> ( leftarg = ebox, @@ -1204,7 +1246,8 @@ CREATE FUNCTION ebox_ecluster_contains_castwrap(ecluster, ebox) RETURNS boolean - LANGUAGE sql IMMUTABLE AS 'SELECT $2::ecluster @> $1'; + LANGUAGE sql IMMUTABLE + AS 'SELECT $2::@extschema@.ecluster OPERATOR(@extschema@.@>) $1'; CREATE OPERATOR <@ ( leftarg = ecluster, @@ -1217,7 +1260,8 @@ CREATE FUNCTION ecluster_ebox_contains_castwrap(ecluster, ebox) RETURNS boolean - LANGUAGE sql IMMUTABLE AS 'SELECT $1 @> $2::ecluster'; + LANGUAGE sql IMMUTABLE + AS 'SELECT $1 OPERATOR(@extschema@.@>) $2::@extschema@.ecluster'; CREATE OPERATOR @> ( leftarg = ecluster, @@ -1230,7 +1274,8 @@ CREATE FUNCTION ecluster_ebox_contains_castwrap(ebox, ecluster) RETURNS boolean - LANGUAGE sql IMMUTABLE AS 'SELECT $2 @> $1::ecluster'; + LANGUAGE sql IMMUTABLE + AS 'SELECT $2 OPERATOR(@extschema@.@>) $1::@extschema@.ecluster'; CREATE OPERATOR <@ ( leftarg = ebox, @@ -1257,7 +1302,7 @@ CREATE FUNCTION epoint_ecircle_distance_commutator(ecircle, epoint) RETURNS float8 - LANGUAGE sql IMMUTABLE AS 'SELECT $2 <-> $1'; + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.<->) $1'; CREATE OPERATOR <-> ( leftarg = ecircle, @@ -1275,7 +1320,7 @@ CREATE FUNCTION epoint_ecluster_distance_commutator(ecluster, epoint) RETURNS float8 - LANGUAGE sql IMMUTABLE AS 'SELECT $2 <-> $1'; + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.<->) $1'; CREATE OPERATOR <-> ( leftarg = ecluster, @@ -1300,7 +1345,7 @@ CREATE FUNCTION ecircle_ecluster_distance_commutator(ecluster, ecircle) RETURNS float8 - LANGUAGE sql IMMUTABLE AS 'SELECT $2 <-> $1'; + LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.<->) $1'; CREATE OPERATOR <-> ( leftarg = ecluster, @@ -1318,7 +1363,8 @@ CREATE FUNCTION epoint_ebox_distance_castwrap(epoint, ebox) RETURNS float8 - LANGUAGE sql IMMUTABLE AS 'SELECT $1 <-> $2::ecluster'; + LANGUAGE sql IMMUTABLE + AS 'SELECT $1 OPERATOR(@extschema@.<->) $2::@extschema@.ecluster'; CREATE OPERATOR <-> ( leftarg = epoint, @@ -1329,7 +1375,8 @@ CREATE FUNCTION epoint_ebox_distance_castwrap(ebox, epoint) RETURNS float8 - LANGUAGE sql IMMUTABLE AS 'SELECT $1::ecluster <-> $2'; + LANGUAGE sql IMMUTABLE + AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.<->) $2'; CREATE OPERATOR <-> ( leftarg = ebox, @@ -1340,7 +1387,10 @@ CREATE FUNCTION ebox_distance_castwrap(ebox, ebox) RETURNS float8 - LANGUAGE sql IMMUTABLE AS 'SELECT $1::ecluster <-> $2::ecluster'; + LANGUAGE sql IMMUTABLE AS $$ + SELECT + $1::@extschema@.ecluster OPERATOR(@extschema@.<->) $2::@extschema@.ecluster + $$; CREATE OPERATOR <-> ( leftarg = ebox, @@ -1351,7 +1401,8 @@ CREATE FUNCTION ebox_ecircle_distance_castwrap(ebox, ecircle) RETURNS float8 - LANGUAGE sql IMMUTABLE AS 'SELECT $1::ecluster <-> $2'; + LANGUAGE sql IMMUTABLE + AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.<->) $2'; CREATE OPERATOR <-> ( leftarg = ebox, @@ -1362,7 +1413,8 @@ CREATE FUNCTION ebox_ecircle_distance_castwrap(ecircle, ebox) RETURNS float8 - LANGUAGE sql IMMUTABLE AS 'SELECT $1 <-> $2::ecluster'; + LANGUAGE sql IMMUTABLE + AS 'SELECT $1 OPERATOR(@extschema@.<->) $2::@extschema@.ecluster'; CREATE OPERATOR <-> ( leftarg = ecircle, @@ -1373,7 +1425,8 @@ CREATE FUNCTION ebox_ecluster_distance_castwrap(ebox, ecluster) RETURNS float8 - LANGUAGE sql IMMUTABLE AS 'SELECT $1::ecluster <-> $2'; + LANGUAGE sql IMMUTABLE + AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.<->) $2'; CREATE OPERATOR <-> ( leftarg = ebox, @@ -1384,7 +1437,8 @@ CREATE FUNCTION ebox_ecluster_distance_castwrap(ecluster, ebox) RETURNS float8 - LANGUAGE sql IMMUTABLE AS 'SELECT $1 <-> $2::ecluster'; + LANGUAGE sql IMMUTABLE + AS 'SELECT $1 OPERATOR(@extschema@.<->) $2::@extschema@.ecluster'; CREATE OPERATOR <-> ( leftarg = ecluster, @@ -1537,23 +1591,28 @@ CREATE FUNCTION distance(epoint, epoint) RETURNS float8 - LANGUAGE sql IMMUTABLE AS 'SELECT $1 <-> $2'; + LANGUAGE sql IMMUTABLE AS 'SELECT $1 OPERATOR(@extschema@.<->) $2'; CREATE FUNCTION distance(ecluster, epoint) RETURNS float8 - LANGUAGE sql IMMUTABLE AS 'SELECT $1 <-> $2'; + LANGUAGE sql IMMUTABLE AS 'SELECT $1 OPERATOR(@extschema@.<->) $2'; CREATE FUNCTION distance_within(epoint, epoint, float8) RETURNS boolean - LANGUAGE sql IMMUTABLE AS 'SELECT $1 && ecircle($2, $3)'; + LANGUAGE sql IMMUTABLE + AS 'SELECT $1 OPERATOR(@extschema@.&&) @extschema@.ecircle($2, $3)'; CREATE FUNCTION distance_within(ecluster, epoint, float8) RETURNS boolean - LANGUAGE sql IMMUTABLE AS 'SELECT $1 && ecircle($2, $3)'; + LANGUAGE sql IMMUTABLE + AS 'SELECT $1 OPERATOR(@extschema@.&&) @extschema@.ecircle($2, $3)'; CREATE FUNCTION fair_distance(ecluster, epoint, int4 = 10000) RETURNS float8 - LANGUAGE sql IMMUTABLE AS 'SELECT $1 <=> epoint_with_sample_count($2, $3)'; + LANGUAGE sql IMMUTABLE AS $$ + SELECT + $1 OPERATOR(@extschema@.<=>) @extschema@.epoint_with_sample_count($2, $3) + $$; -------------------------------- @@ -1564,17 +1623,21 @@ RETURNS epoint LANGUAGE plpgsql IMMUTABLE STRICT AS $$ DECLARE - "result" epoint; + "result" @extschema@.epoint; BEGIN - IF $3 = 'epoint_lonlat' THEN + IF $3 OPERATOR(pg_catalog.=) 'epoint_lonlat' THEN -- avoid dynamic command execution for better performance - RETURN epoint($2, $1); + RETURN @extschema@.epoint($2, $1); END IF; - IF $3 = 'epoint' OR $3 = 'epoint_latlon' THEN + IF + $3 OPERATOR(pg_catalog.=) 'epoint' OR + $3 OPERATOR(pg_catalog.=) 'epoint_latlon' + THEN -- avoid dynamic command execution for better performance - RETURN epoint($1, $2); + RETURN @extschema@.epoint($1, $2); END IF; - EXECUTE 'SELECT ' || $3 || '($1, $2)' INTO STRICT "result" USING $1, $2; + EXECUTE + 'SELECT ' OPERATOR(pg_catalog.||) $3 OPERATOR(pg_catalog.||) '($1, $2)' INTO STRICT "result" USING $1, $2; RETURN "result"; END; $$; @@ -1583,16 +1646,21 @@ RETURNS SETOF jsonb LANGUAGE sql IMMUTABLE STRICT AS $$ SELECT "result" FROM - ( SELECT jsonb_array_length($1) - 1 ) AS "lastindex_row" ("lastindex") - CROSS JOIN LATERAL jsonb_array_elements( + ( SELECT pg_catalog.jsonb_array_length($1) - 1 ) + AS "lastindex_row" ("lastindex") + CROSS JOIN LATERAL pg_catalog.jsonb_array_elements( CASE WHEN - coords_to_epoint( - ($1->0->>0)::float8, - ($1->0->>1)::float8, + @extschema@.coords_to_epoint( + ($1 OPERATOR(pg_catalog.->) 0 OPERATOR(pg_catalog.->>) 0) + ::pg_catalog.float8, + ($1 OPERATOR(pg_catalog.->) 0 OPERATOR(pg_catalog.->>) 1) + ::pg_catalog.float8, $2 - ) = coords_to_epoint( - ($1->"lastindex"->>0)::float8, - ($1->"lastindex"->>1)::float8, + ) OPERATOR(pg_catalog.=) @extschema@.coords_to_epoint( + ($1 OPERATOR(pg_catalog.->) "lastindex" OPERATOR(pg_catalog.->>) 0) + ::pg_catalog.float8, + ($1 OPERATOR(pg_catalog.->) "lastindex" OPERATOR(pg_catalog.->>) 1) + ::pg_catalog.float8, $2 ) THEN @@ -1607,14 +1675,16 @@ RETURNS epoint LANGUAGE sql IMMUTABLE STRICT AS $$ SELECT CASE - WHEN $1->>'type' = 'Point' THEN - coords_to_epoint( - ($1->'coordinates'->>0)::float8, - ($1->'coordinates'->>1)::float8, + WHEN $1 OPERATOR(pg_catalog.->>) 'type' OPERATOR(pg_catalog.=) 'Point' THEN + @extschema@.coords_to_epoint( + ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 0) + ::pg_catalog.float8, + ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 1) + ::pg_catalog.float8, $2 ) WHEN $1->>'type' = 'Feature' THEN - GeoJSON_to_epoint($1->'geometry', $2) + @extschema@.GeoJSON_to_epoint($1 OPERATOR(pg_catalog.->) 'geometry', $2) ELSE NULL END @@ -1623,92 +1693,113 @@ CREATE FUNCTION GeoJSON_to_ecluster(jsonb, text = 'epoint_lonlat') RETURNS ecluster LANGUAGE sql IMMUTABLE STRICT AS $$ - SELECT CASE $1->>'type' + SELECT CASE $1 OPERATOR(pg_catalog.->>) 'type' WHEN 'Point' THEN - coords_to_epoint( - ($1->'coordinates'->>0)::float8, - ($1->'coordinates'->>1)::float8, + @extschema@.coords_to_epoint( + ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 0) + ::pg_catalog.float8, + ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 1) + ::pg_catalog.float8, $2 - )::ecluster + )::@extschema@.ecluster WHEN 'MultiPoint' THEN - ( SELECT ecluster_create_multipoint(array_agg( - coords_to_epoint( - ("coord"->>0)::float8, - ("coord"->>1)::float8, + ( SELECT @extschema@.ecluster_create_multipoint(pg_catalog.array_agg( + @extschema@.coords_to_epoint( + ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, + ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, $2 ) )) - FROM jsonb_array_elements($1->'coordinates') AS "coord" + FROM pg_catalog.jsonb_array_elements( + $1 OPERATOR(pg_catalog.->) 'coordinates' + ) AS "coord" ) WHEN 'LineString' THEN - ( SELECT ecluster_create_path(array_agg( - coords_to_epoint( - ("coord"->>0)::float8, - ("coord"->>1)::float8, + ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg( + @extschema@.coords_to_epoint( + ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, + ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, $2 ) )) - FROM jsonb_array_elements($1->'coordinates') AS "coord" + FROM pg_catalog.jsonb_array_elements( + $1 OPERATOR(pg_catalog.->) 'coordinates' + ) AS "coord" ) WHEN 'MultiLineString' THEN - ( SELECT ecluster_concat(array_agg( - ( SELECT ecluster_create_path(array_agg( + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( + ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg( coords_to_epoint( - ("coord"->>0)::float8, - ("coord"->>1)::float8, + ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, + ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, $2 ) )) - FROM jsonb_array_elements("coord_array") AS "coord" + FROM pg_catalog.jsonb_array_elements("coord_array") AS "coord" ) )) - FROM jsonb_array_elements($1->'coordinates') AS "coord_array" + FROM pg_catalog.jsonb_array_elements( + $1 OPERATOR(pg_catalog.->) 'coordinates' + ) AS "coord_array" ) WHEN 'Polygon' THEN - ( SELECT ecluster_concat(array_agg( - ( SELECT ecluster_create_polygon(array_agg( - coords_to_epoint( - ("coord"->>0)::float8, - ("coord"->>1)::float8, + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( + ( SELECT @extschema@.ecluster_create_polygon(pg_catalog.array_agg( + @extschema@.coords_to_epoint( + ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, + ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, $2 ) )) - FROM GeoJSON_LinearRing_vertices("coord_array", $2) AS "coord" + FROM @extschema@.GeoJSON_LinearRing_vertices("coord_array", $2) + AS "coord" ) )) - FROM jsonb_array_elements($1->'coordinates') AS "coord_array" + FROM pg_catalog.jsonb_array_elements( + $1 OPERATOR(pg_catalog.->) 'coordinates' + ) AS "coord_array" ) WHEN 'MultiPolygon' THEN - ( SELECT ecluster_concat(array_agg( - ( SELECT ecluster_concat(array_agg( - ( SELECT ecluster_create_polygon(array_agg( - coords_to_epoint( - ("coord"->>0)::float8, - ("coord"->>1)::float8, + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( + ( SELECT pg_catalog.ecluster_create_polygon(pg_catalog.array_agg( + @extschema@.coords_to_epoint( + ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, + ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, $2 ) )) - FROM GeoJSON_LinearRing_vertices("coord_array", $2) AS "coord" + FROM @extschema@.GeoJSON_LinearRing_vertices("coord_array", $2) + AS "coord" ) )) - FROM jsonb_array_elements("coord_array_array") AS "coord_array" + FROM pg_catalog.jsonb_array_elements("coord_array_array") + AS "coord_array" ) )) - FROM jsonb_array_elements($1->'coordinates') AS "coord_array_array" + FROM jsonb_array_elements( + $1 OPERATOR(pg_catalog.->) 'coordinates' + ) AS "coord_array_array" ) WHEN 'GeometryCollection' THEN - ( SELECT ecluster_concat(array_agg( - GeoJSON_to_ecluster("geometry", $2) + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( + @extschema@.GeoJSON_to_ecluster("geometry", $2) )) - FROM jsonb_array_elements($1->'geometries') AS "geometry" + FROM pg_catalog.jsonb_array_elements( + $1 OPERATOR(pg_catalog.->) 'geometries' + ) AS "geometry" ) WHEN 'Feature' THEN - GeoJSON_to_ecluster($1->'geometry', $2) + @extschema@.GeoJSON_to_ecluster( + $1 OPERATOR(pg_catalog.->) 'geometry', $2 + ) WHEN 'FeatureCollection' THEN - ( SELECT ecluster_concat(array_agg( - GeoJSON_to_ecluster("feature", $2) + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( + @extschema@.GeoJSON_to_ecluster("feature", $2) )) - FROM jsonb_array_elements($1->'features') AS "feature" + FROM pg_catalog.jsonb_array_elements( + $1 OPERATOR(pg_catalog.->) 'features' + ) AS "feature" ) ELSE NULL diff -r 8fa0ef5e4ee6 -r b5bc6b35b716 latlon.control --- a/latlon.control Wed Feb 12 13:11:04 2020 +0100 +++ b/latlon.control Sun Nov 29 19:55:33 2020 +0100 @@ -1,3 +1,4 @@ comment = 'Geographic data types and spatial indexing for the WGS-84 spheroid' default_version = '0.15' -relocatable = true +trusted = true +relocatable = false