-- complain if script is sourced in psql, rather than via CREATE EXTENSION \echo Use "CREATE EXTENSION pointcloud" to load this file. \quit ------------------------------------------------------------------- -- METADATA and SCHEMA ------------------------------------------------------------------- -- Confirm the XML representation of a schema has everything we need CREATE OR REPLACE FUNCTION PC_SchemaIsValid(schemaxml text) RETURNS boolean AS 'MODULE_PATHNAME','pcschema_is_valid' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; -- Metadata table describing contents of pcpoints CREATE TABLE IF NOT EXISTS pointcloud_formats ( pcid INTEGER PRIMARY KEY -- PCID == 0 is unknown -- PCID > 2^16 is reserved to leave space in typmod CHECK (pcid > 0 AND pcid < 65536), srid INTEGER, -- REFERENCES spatial_ref_sys(srid) schema TEXT CHECK ( PC_SchemaIsValid(schema) ) ); -- Register pointcloud_formats table so the contents are included in pg_dump output SELECT pg_catalog.pg_extension_config_dump('@extschema@.pointcloud_formats', ''); CREATE OR REPLACE FUNCTION PC_SchemaGetNDims(pcid integer) RETURNS integer AS 'MODULE_PATHNAME','pcschema_get_ndims' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; -- Read typmod number from string CREATE OR REPLACE FUNCTION pc_typmod_in(cstring[]) RETURNS integer AS 'MODULE_PATHNAME','pc_typmod_in' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; -- Write typmod number to string CREATE OR REPLACE FUNCTION pc_typmod_out(typmod integer) RETURNS cstring AS 'MODULE_PATHNAME','pc_typmod_out' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; -- Read pcid from typmod number CREATE OR REPLACE FUNCTION pc_typmod_pcid(typmod integer) RETURNS int4 AS 'MODULE_PATHNAME','pc_typmod_pcid' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; -- Return the script version number CREATE OR REPLACE FUNCTION pc_script_version() RETURNS text AS $$ SELECT '1.2.2'::text $$ LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE; -- Return the library version number CREATE OR REPLACE FUNCTION pc_lib_version() RETURNS text AS 'MODULE_PATHNAME', 'pc_version' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; -- Return the pgsql version number CREATE OR REPLACE FUNCTION pc_pgsql_version() RETURNS text AS 'MODULE_PATHNAME', 'pc_pgsql_version' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; -- Return the libxml2 version number CREATE OR REPLACE FUNCTION pc_libxml2_version() RETURNS text AS 'MODULE_PATHNAME', 'pc_libxml2_version' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; -- Return whether lazperf is enabled CREATE OR REPLACE FUNCTION pc_lazperf_enabled() RETURNS boolean AS 'MODULE_PATHNAME', 'pc_lazperf_enabled' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; -- Return the extension version number with the commit hash trimmed, and check sanity CREATE OR REPLACE FUNCTION _pc_version_no_commit(libver text, scrver text) RETURNS text AS $$ DECLARE libver_no_commit TEXT; BEGIN libver_no_commit := split_part(libver, ' ', 1); IF scrver != libver_no_commit THEN RAISE WARNING 'Library (%) and script (%) version mismatch', libver_no_commit, scrver; END IF; RETURN libver_no_commit; END; $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; -- Return the extension version number, and check sanity CREATE OR REPLACE FUNCTION pc_version() RETURNS text AS $$ DECLARE libver TEXT; scrver TEXT; version TEXT; BEGIN libver := @extschema@.pc_lib_version(); scrver := @extschema@.pc_script_version(); version := @extschema@._pc_version_no_commit(libver, scrver); RETURN version; END; $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; -- Return the full version string CREATE OR REPLACE FUNCTION pc_full_version() RETURNS text AS $$ DECLARE pcver TEXT; pclibver TEXT; pcscrver TEXT; pgsqlver TEXT; libxml2ver TEXT; lazperfenabled BOOLEAN; BEGIN pclibver := @extschema@.pc_lib_version(); pcscrver := @extschema@.pc_script_version(); pcver := @extschema@._pc_version_no_commit(pclibver, pcscrver); pgsqlver := @extschema@.pc_pgsql_version(); libxml2ver := @extschema@.pc_libxml2_version(); lazperfenabled := @extschema@.pc_lazperf_enabled(); RETURN 'POINTCLOUD="' || pclibver || '" PGSQL="' || pgsqlver || '" LIBXML2="' || libxml2ver || ' LAZPERF enabled=' || lazperfenabled; END; $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; -- Upgrade pointcloud extension to latest (or specified) version. -- Takes care of in-development upgrades CREATE OR REPLACE FUNCTION pc_upgrade(to_version text DEFAULT NULL) RETURNS text AS $$ DECLARE ver RECORD; target_version text; BEGIN SELECT default_version, installed_version FROM pg_catalog.pg_available_extensions WHERE name = 'pointcloud' INTO ver; IF to_version IS NULL THEN target_version := ver.default_version; ELSE target_version := to_version; END IF; IF target_version != ver.installed_version THEN EXECUTE 'ALTER EXTENSION pointcloud UPDATE TO ' || quote_literal(target_version); ELSE -- Use the "next" trick IF target_version LIKE '%next' THEN target_version := replace(target_version, 'next', ''); EXECUTE 'ALTER EXTENSION pointcloud UPDATE TO ' || quote_literal(target_version); ELSE -- Double upgrade, to get back to "next"-free EXECUTE 'ALTER EXTENSION pointcloud UPDATE TO ' || quote_literal(target_version || 'next'); EXECUTE 'ALTER EXTENSION pointcloud UPDATE TO ' || quote_literal(target_version); END IF; END IF; RETURN @extschema@.pc_version(); END; $$ LANGUAGE 'plpgsql' VOLATILE; ------------------------------------------------------------------- -- PCPOINT ------------------------------------------------------------------- CREATE OR REPLACE FUNCTION pcpoint_in(cstring) RETURNS pcpoint AS 'MODULE_PATHNAME', 'pcpoint_in' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION pcpoint_out(pcpoint) RETURNS cstring AS 'MODULE_PATHNAME', 'pcpoint_out' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_Get(pt pcpoint, dimname text) RETURNS numeric AS 'MODULE_PATHNAME', 'pcpoint_get_value' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; -- Availability: 1.1.0 CREATE OR REPLACE FUNCTION PC_Get(pt pcpoint) RETURNS float8[] AS 'MODULE_PATHNAME', 'pcpoint_get_values' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_MakePoint(pcid integer, vals float8[]) RETURNS pcpoint AS 'MODULE_PATHNAME', 'pcpoint_from_double_array' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_AsText(p pcpoint) RETURNS text AS 'MODULE_PATHNAME', 'pcpoint_as_text' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_AsBinary(p pcpoint) RETURNS bytea AS 'MODULE_PATHNAME', 'pcpoint_as_bytea' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; ------------------------------------------------------------------- -- PCPATCH ------------------------------------------------------------------- CREATE OR REPLACE FUNCTION PC_MakePatch(pcid integer, vals float8[]) RETURNS pcpatch AS 'MODULE_PATHNAME', 'pcpatch_from_float_array' LANGUAGE 'c' IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION pcpatch_in(cstring) RETURNS pcpatch AS 'MODULE_PATHNAME', 'pcpatch_in' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION pcpatch_out(pcpatch) RETURNS cstring AS 'MODULE_PATHNAME', 'pcpatch_out' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_AsText(p pcpatch) RETURNS text AS 'MODULE_PATHNAME', 'pcpatch_as_text' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_EnvelopeAsBinary(p pcpatch) RETURNS bytea AS 'MODULE_PATHNAME', 'pcpatch_envelope_as_bytea' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; -- PC_Envelope is an alias to PC_EnvelopeAsBinary, it is kept for -- backward-compatibility CREATE OR REPLACE FUNCTION PC_Envelope(p pcpatch) RETURNS bytea AS 'MODULE_PATHNAME', 'pcpatch_envelope_as_bytea' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_Uncompress(p pcpatch) RETURNS pcpatch AS 'MODULE_PATHNAME', 'pcpatch_uncompress' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; -- Availability: 1.1.0 CREATE OR REPLACE FUNCTION PC_Compress(p pcpatch, comp text default '', config text default '') RETURNS pcpatch AS 'MODULE_PATHNAME', 'pcpatch_compress' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_NumPoints(p pcpatch) RETURNS int4 AS 'MODULE_PATHNAME', 'pcpatch_numpoints' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; -- Availability: 1.1.0 CREATE OR REPLACE FUNCTION PC_PCId(p pcpatch) RETURNS int4 AS 'MODULE_PATHNAME', 'pcpatch_pcid' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; -- Availability: 1.1.0 CREATE OR REPLACE FUNCTION PC_Summary(p pcpatch) RETURNS text AS 'MODULE_PATHNAME', 'pcpatch_summary' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_Compression(p pcpatch) RETURNS int4 AS 'MODULE_PATHNAME', 'pcpatch_compression' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_Intersects(p1 pcpatch, p2 pcpatch) RETURNS boolean AS 'MODULE_PATHNAME', 'pcpatch_intersects' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_MemSize(p pcpatch) RETURNS int4 AS 'MODULE_PATHNAME', 'pcpatch_size' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_MemSize(p pcpoint) RETURNS int4 AS 'MODULE_PATHNAME', 'pcpoint_size' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; -- Availability: 1.1.0 CREATE OR REPLACE FUNCTION PC_PCId(p pcpoint) RETURNS int4 AS 'MODULE_PATHNAME', 'pcpoint_pcid' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; -- Availability: 1.1.0 -- @param statno 0:min 1:max 2:avg CREATE OR REPLACE FUNCTION _PC_PatchStat(p pcpatch, statno int) RETURNS pcpoint AS 'MODULE_PATHNAME', 'pcpatch_get_stat' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; -- Availability: 1.1.0 CREATE OR REPLACE FUNCTION PC_PatchMin(p pcpatch) RETURNS pcpoint AS $$ SELECT _PC_PatchStat(p, 0) $$ LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE; -- Availability: 1.1.0 CREATE OR REPLACE FUNCTION PC_PatchMax(p pcpatch) RETURNS pcpoint AS $$ SELECT _PC_PatchStat(p, 1) $$ LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE; -- Availability: 1.1.0 CREATE OR REPLACE FUNCTION PC_PatchAvg(p pcpatch) RETURNS pcpoint AS $$ SELECT _PC_PatchStat(p, 2) $$ LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE; -- Availability: 1.1.0 -- @param statno 0:min 1:max 2:avg CREATE OR REPLACE FUNCTION _PC_PatchStat(p pcpatch, statno int, attr text) RETURNS numeric AS 'MODULE_PATHNAME', 'pcpatch_get_stat' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_PatchMin(p pcpatch, attr text) RETURNS numeric AS $$ SELECT _PC_PatchStat(p, 0, attr) $$ LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_PatchMax(p pcpatch, attr text) RETURNS numeric AS $$ SELECT _PC_PatchStat(p, 1, attr) $$ LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_PatchAvg(p pcpatch, attr text) RETURNS numeric AS $$ SELECT _PC_PatchStat(p, 2, attr) $$ LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_FilterLessThan(p pcpatch, attr text, v1 float8 default 0.0, v2 float8 default 0.0, mode int4 default 0) RETURNS pcpatch AS 'MODULE_PATHNAME', 'pcpatch_filter' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_FilterGreaterThan(p pcpatch, attr text, v1 float8 default 0.0, v2 float8 default 0.0, mode int4 default 1) RETURNS pcpatch AS 'MODULE_PATHNAME', 'pcpatch_filter' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_FilterEquals(p pcpatch, attr text, v1 float8 default 0.0, v2 float8 default 0.0, mode int4 default 2) RETURNS pcpatch AS 'MODULE_PATHNAME', 'pcpatch_filter' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_FilterBetween(p pcpatch, attr text, v1 float8 default 0.0, v2 float8 default 0.0, mode int4 default 3) RETURNS pcpatch AS 'MODULE_PATHNAME', 'pcpatch_filter' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_PointN(p pcpatch, n int4) RETURNS pcpoint AS 'MODULE_PATHNAME', 'pcpatch_pointn' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_Sort(p pcpatch, attr text[]) RETURNS pcpatch AS 'MODULE_PATHNAME', 'pcpatch_sort' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_IsSorted(p pcpatch, attr text[], strict boolean default false) RETURNS boolean AS 'MODULE_PATHNAME', 'pcpatch_is_sorted' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_Range(p pcpatch, first int4, count int4) RETURNS pcpatch AS 'MODULE_PATHNAME', 'pcpatch_range' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_BoundingDiagonalAsBinary(p pcpatch) RETURNS bytea AS 'MODULE_PATHNAME', 'pcpatch_bounding_diagonal_as_bytea' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_SetPCId(p pcpatch, pcid int4, def float8 default 0.0) RETURNS pcpatch AS 'MODULE_PATHNAME', 'pcpatch_setpcid' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_Transform(p pcpatch, pcid int4, def float8 default 0.0) RETURNS pcpatch AS 'MODULE_PATHNAME', 'pcpatch_transform' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; ------------------------------------------------------------------- -- POINTCLOUD_COLUMNS ------------------------------------------------------------------- -- Last Changed: 1.0.1 CREATE OR REPLACE VIEW pointcloud_columns AS SELECT n.nspname AS schema, c.relname AS table, a.attname AS column, pc_typmod_pcid(a.atttypmod) AS pcid, p.srid AS srid, t.typname AS type FROM pg_class c, pg_type t, pg_namespace n, pg_attribute a LEFT OUTER JOIN pointcloud_formats p ON ( pc_typmod_pcid(a.atttypmod) = p.pcid ) WHERE t.typname IN ('pcpatch','pcpoint') AND a.attisdropped = false AND a.atttypid = t.oid AND a.attrelid = c.oid AND c.relnamespace = n.oid AND NOT pg_is_other_temp_schema(c.relnamespace) AND has_table_privilege( c.oid, 'SELECT'::text ); -- Special cast for enforcing typmod restrictions CREATE OR REPLACE FUNCTION pcpatch(p pcpatch, typmod integer, explicit boolean) RETURNS pcpatch AS 'MODULE_PATHNAME', 'pcpatch_enforce_typmod' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION pcpoint(p pcpoint, typmod integer, explicit boolean) RETURNS pcpoint AS 'MODULE_PATHNAME', 'pcpoint_enforce_typmod' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; ------------------------------------------------------------------- -- AGGREGATE GENERIC SUPPORT ------------------------------------------------------------------- CREATE OR REPLACE FUNCTION pointcloud_abs_in(cstring) RETURNS pointcloud_abs AS 'MODULE_PATHNAME' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION pointcloud_abs_out(pointcloud_abs) RETURNS cstring AS 'MODULE_PATHNAME' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; ------------------------------------------------------------------- -- AGGREGATE PCPOINT ------------------------------------------------------------------- CREATE OR REPLACE FUNCTION PC_Patch(pcpoint[]) RETURNS pcpatch AS 'MODULE_PATHNAME', 'pcpatch_from_pcpoint_array' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; CREATE OR REPLACE FUNCTION pcpoint_agg_transfn (pointcloud_abs, pcpoint) RETURNS pointcloud_abs AS 'MODULE_PATHNAME', 'pointcloud_agg_transfn' LANGUAGE 'c' PARALLEL SAFE; CREATE OR REPLACE FUNCTION pcpoint_agg_final_array (pointcloud_abs) RETURNS pcpoint[] AS 'MODULE_PATHNAME', 'pcpoint_agg_final_array' LANGUAGE 'c' PARALLEL SAFE; CREATE OR REPLACE FUNCTION pcpoint_agg_final_pcpatch (pointcloud_abs) RETURNS pcpatch AS 'MODULE_PATHNAME', 'pcpoint_agg_final_pcpatch' LANGUAGE 'c' PARALLEL SAFE; ------------------------------------------------------------------- -- AGGREGATE / EXPLODE PCPATCH ------------------------------------------------------------------- CREATE OR REPLACE FUNCTION pcpatch_agg_final_array (pointcloud_abs) RETURNS pcpatch[] AS 'MODULE_PATHNAME', 'pcpatch_agg_final_array' LANGUAGE 'c' PARALLEL SAFE; CREATE OR REPLACE FUNCTION pcpatch_agg_final_pcpatch (pointcloud_abs) RETURNS pcpatch AS 'MODULE_PATHNAME', 'pcpatch_agg_final_pcpatch' LANGUAGE 'c' PARALLEL SAFE; CREATE OR REPLACE FUNCTION pcpatch_agg_transfn (pointcloud_abs, pcpatch) RETURNS pointcloud_abs AS 'MODULE_PATHNAME', 'pointcloud_agg_transfn' LANGUAGE 'c' PARALLEL SAFE; CREATE OR REPLACE FUNCTION PC_Explode(p pcpatch) RETURNS setof pcpoint AS 'MODULE_PATHNAME', 'pcpatch_unnest' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE; ------------------------------------------------------------------- -- SQL Utility Functions -------------------------------------------------------------------