pointcloud.sql.in

-- 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;

-- 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('pointcloud_formats', '');

CREATE OR REPLACE FUNCTION PC_SchemaGetNDims(pcid integer)
	RETURNS integer
	AS 'MODULE_PATHNAME','pcschema_get_ndims'
	LANGUAGE 'c' IMMUTABLE STRICT;

-- 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;

-- 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;

-- 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;

-- Return the script version number
CREATE OR REPLACE FUNCTION pc_script_version()
	RETURNS text AS $$ SELECT '@POINTCLOUD_VERSION@'::text $$
	LANGUAGE 'sql' IMMUTABLE STRICT;

-- Return the library version number
CREATE OR REPLACE FUNCTION pc_lib_version()
	RETURNS text AS 'MODULE_PATHNAME', 'pc_version'
	LANGUAGE 'c' IMMUTABLE STRICT;

-- Return the extension version number and check sanity
CREATE OR REPLACE FUNCTION pc_version()
	RETURNS text AS
$$
DECLARE
	libver TEXT;
	scrver TEXT;
BEGIN
	scrver := pc_script_version();
	libver := pc_lib_version();
	IF scrver != libver THEN
		RAISE WARNING 'Library (%) and script (%) version mismatch',
			libver, scrver;
	END IF;
	RETURN libver;
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 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;

CREATE OR REPLACE FUNCTION pcpoint_out(pcpoint)
	RETURNS cstring AS 'MODULE_PATHNAME', 'pcpoint_out'
	LANGUAGE 'c' IMMUTABLE STRICT;

CREATE TYPE pcpoint (
	internallength = variable,
	input = pcpoint_in,
	output = pcpoint_out,
	-- send = geometry_send,
	-- receive = geometry_recv,
	typmod_in = pc_typmod_in,
	typmod_out = pc_typmod_out,
	-- delimiter = ':',
	-- alignment = double,
	-- analyze = geometry_analyze,
	storage = external -- do not try to compress it please
);

CREATE OR REPLACE FUNCTION PC_Get(pt pcpoint, dimname text)
	RETURNS numeric AS 'MODULE_PATHNAME', 'pcpoint_get_value'
	LANGUAGE 'c' IMMUTABLE STRICT;

-- Availability: 1.1.0
CREATE OR REPLACE FUNCTION PC_Get(pt pcpoint)
	RETURNS float8[] AS 'MODULE_PATHNAME', 'pcpoint_get_values'
	LANGUAGE 'c' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION PC_MakePoint(pcid integer, vals float8[])
	RETURNS pcpoint AS 'MODULE_PATHNAME', 'pcpoint_from_double_array'
	LANGUAGE 'c' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION PC_AsText(p pcpoint)
	RETURNS text AS 'MODULE_PATHNAME', 'pcpoint_as_text'
	LANGUAGE 'c' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION PC_AsBinary(p pcpoint)
	RETURNS bytea AS 'MODULE_PATHNAME', 'pcpoint_as_bytea'
	LANGUAGE 'c' IMMUTABLE STRICT;

-------------------------------------------------------------------
--  PCPATCH
-------------------------------------------------------------------

CREATE OR REPLACE FUNCTION pcpatch_in(cstring)
	RETURNS pcpatch AS 'MODULE_PATHNAME', 'pcpatch_in'
	LANGUAGE 'c' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pcpatch_out(pcpatch)
	RETURNS cstring AS 'MODULE_PATHNAME', 'pcpatch_out'
	LANGUAGE 'c' IMMUTABLE STRICT;

CREATE TYPE pcpatch (
	internallength = variable,
	input = pcpatch_in,
	output = pcpatch_out,
	-- send = geometry_send,
	-- receive = geometry_recv,
	typmod_in = pc_typmod_in,
	typmod_out = pc_typmod_out,
	-- delimiter = ':',
	-- alignment = double,
	-- analyze = geometry_analyze,
	storage = external
);

CREATE OR REPLACE FUNCTION PC_AsText(p pcpatch)
	RETURNS text AS 'MODULE_PATHNAME', 'pcpatch_as_text'
	LANGUAGE 'c' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION PC_EnvelopeAsBinary(p pcpatch)
	RETURNS bytea AS 'MODULE_PATHNAME', 'pcpatch_envelope_as_bytea'
	LANGUAGE 'c' IMMUTABLE STRICT;

-- 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;

CREATE OR REPLACE FUNCTION PC_Uncompress(p pcpatch)
	RETURNS pcpatch AS 'MODULE_PATHNAME', 'pcpatch_uncompress'
	LANGUAGE 'c' IMMUTABLE STRICT;

-- 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;

CREATE OR REPLACE FUNCTION PC_NumPoints(p pcpatch)
	RETURNS int4 AS 'MODULE_PATHNAME', 'pcpatch_numpoints'
	LANGUAGE 'c' IMMUTABLE STRICT;

-- Availability: 1.1.0
CREATE OR REPLACE FUNCTION PC_PCId(p pcpatch)
	RETURNS int4 AS 'MODULE_PATHNAME', 'pcpatch_pcid'
	LANGUAGE 'c' IMMUTABLE STRICT;

-- Availability: 1.1.0
CREATE OR REPLACE FUNCTION PC_Summary(p pcpatch)
	RETURNS text AS 'MODULE_PATHNAME', 'pcpatch_summary'
	LANGUAGE 'c' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION PC_Compression(p pcpatch)
	RETURNS int4 AS 'MODULE_PATHNAME', 'pcpatch_compression'
	LANGUAGE 'c' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION PC_Intersects(p1 pcpatch, p2 pcpatch)
	RETURNS boolean AS 'MODULE_PATHNAME', 'pcpatch_intersects'
	LANGUAGE 'c' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION PC_MemSize(p pcpatch)
	RETURNS int4 AS 'MODULE_PATHNAME', 'pcpatch_size'
	LANGUAGE 'c' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION PC_MemSize(p pcpoint)
	RETURNS int4 AS 'MODULE_PATHNAME', 'pcpoint_size'
	LANGUAGE 'c' IMMUTABLE STRICT;

-- Availability: 1.1.0
CREATE OR REPLACE FUNCTION PC_PCId(p pcpoint)
	RETURNS int4 AS 'MODULE_PATHNAME', 'pcpoint_pcid'
	LANGUAGE 'c' IMMUTABLE STRICT;

-- 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;

-- Availability: 1.1.0
CREATE OR REPLACE FUNCTION PC_PatchMin(p pcpatch)
	RETURNS pcpoint AS $$ SELECT _PC_PatchStat(p, 0) $$
	LANGUAGE 'sql' IMMUTABLE STRICT;

-- Availability: 1.1.0
CREATE OR REPLACE FUNCTION PC_PatchMax(p pcpatch)
	RETURNS pcpoint AS $$ SELECT _PC_PatchStat(p, 1) $$
	LANGUAGE 'sql' IMMUTABLE STRICT;

-- Availability: 1.1.0
CREATE OR REPLACE FUNCTION PC_PatchAvg(p pcpatch)
	RETURNS pcpoint AS $$ SELECT _PC_PatchStat(p, 2) $$
	LANGUAGE 'sql' IMMUTABLE STRICT;

-- 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;

CREATE OR REPLACE FUNCTION PC_PatchMin(p pcpatch, attr text)
	RETURNS numeric AS $$ SELECT _PC_PatchStat(p, 0, attr) $$
	LANGUAGE 'sql' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION PC_PatchMax(p pcpatch, attr text)
	RETURNS numeric AS $$ SELECT _PC_PatchStat(p, 1, attr) $$
	LANGUAGE 'sql' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION PC_PatchAvg(p pcpatch, attr text)
	RETURNS numeric AS $$ SELECT _PC_PatchStat(p, 2, attr) $$
	LANGUAGE 'sql' IMMUTABLE STRICT;

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;

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;

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;

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;

CREATE OR REPLACE FUNCTION PC_PointN(p pcpatch, n int4)
	RETURNS pcpoint AS 'MODULE_PATHNAME', 'pcpatch_pointn'
	LANGUAGE 'c' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION PC_Sort(p pcpatch, attr text[])
	RETURNS pcpatch AS 'MODULE_PATHNAME', 'pcpatch_sort'
	LANGUAGE 'c' IMMUTABLE STRICT;

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;

CREATE OR REPLACE FUNCTION PC_Range(p pcpatch, first int4, count int4)
	RETURNS pcpatch AS 'MODULE_PATHNAME', 'pcpatch_range'
	LANGUAGE 'c' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION PC_BoundingDiagonalAsBinary(p pcpatch)
	RETURNS bytea AS 'MODULE_PATHNAME', 'pcpatch_bounding_diagonal_as_bytea'
	LANGUAGE 'c' IMMUTABLE STRICT;

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;

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;

-------------------------------------------------------------------
--  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;

CREATE CAST (pcpatch AS pcpatch) WITH FUNCTION pcpatch(pcpatch, integer, boolean) AS IMPLICIT;

CREATE OR REPLACE FUNCTION pcpoint(p pcpoint, typmod integer, explicit boolean)
	RETURNS pcpoint AS 'MODULE_PATHNAME', 'pcpoint_enforce_typmod'
	LANGUAGE 'c' IMMUTABLE STRICT;

CREATE CAST (pcpoint AS pcpoint) WITH FUNCTION pcpoint(pcpoint, integer, boolean) AS IMPLICIT;

-------------------------------------------------------------------
--  AGGREGATE GENERIC SUPPORT
-------------------------------------------------------------------

CREATE OR REPLACE FUNCTION pointcloud_abs_in(cstring)
	RETURNS pointcloud_abs AS 'MODULE_PATHNAME'
	LANGUAGE 'c' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pointcloud_abs_out(pointcloud_abs)
	RETURNS cstring AS 'MODULE_PATHNAME'
	LANGUAGE 'c' IMMUTABLE STRICT;

CREATE TYPE pointcloud_abs (
	internallength = 8,
	input = pointcloud_abs_in,
	output = pointcloud_abs_out,
	alignment = double
);

-------------------------------------------------------------------
--  AGGREGATE PCPOINT
-------------------------------------------------------------------

CREATE OR REPLACE FUNCTION PC_Patch(pcpoint[])
	RETURNS pcpatch AS 'MODULE_PATHNAME', 'pcpatch_from_pcpoint_array'
	LANGUAGE 'c' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pcpoint_agg_transfn (pointcloud_abs, pcpoint)
	RETURNS pointcloud_abs AS 'MODULE_PATHNAME', 'pointcloud_agg_transfn'
	LANGUAGE 'c';

CREATE OR REPLACE FUNCTION pcpoint_agg_final_array (pointcloud_abs)
	RETURNS pcpoint[] AS 'MODULE_PATHNAME', 'pcpoint_agg_final_array'
	LANGUAGE 'c';

CREATE OR REPLACE FUNCTION pcpoint_agg_final_pcpatch (pointcloud_abs)
	RETURNS pcpatch AS 'MODULE_PATHNAME', 'pcpoint_agg_final_pcpatch'
	LANGUAGE 'c';

CREATE AGGREGATE PC_Patch (
	BASETYPE = pcpoint,
	SFUNC = pcpoint_agg_transfn,
	STYPE = pointcloud_abs,
	FINALFUNC = pcpoint_agg_final_pcpatch
);

CREATE AGGREGATE PC_Point_Agg (
	BASETYPE = pcpoint,
	SFUNC = pcpoint_agg_transfn,
	STYPE = pointcloud_abs,
	FINALFUNC = pcpoint_agg_final_array
);

-------------------------------------------------------------------
--  AGGREGATE / EXPLODE PCPATCH
-------------------------------------------------------------------

CREATE OR REPLACE FUNCTION pcpatch_agg_final_array (pointcloud_abs)
	RETURNS pcpatch[] AS 'MODULE_PATHNAME', 'pcpatch_agg_final_array'
	LANGUAGE 'c';

CREATE OR REPLACE FUNCTION pcpatch_agg_final_pcpatch (pointcloud_abs)
	RETURNS pcpatch AS 'MODULE_PATHNAME', 'pcpatch_agg_final_pcpatch'
	LANGUAGE 'c';

CREATE OR REPLACE FUNCTION pcpatch_agg_transfn (pointcloud_abs, pcpatch)
	RETURNS pointcloud_abs AS 'MODULE_PATHNAME', 'pointcloud_agg_transfn'
	LANGUAGE 'c';

CREATE AGGREGATE PC_Patch_Agg (
	BASETYPE = pcpatch,
	SFUNC = pcpatch_agg_transfn,
	STYPE = pointcloud_abs,
	FINALFUNC = pcpatch_agg_final_array
);

CREATE AGGREGATE PC_Union (
	BASETYPE = pcpatch,
	SFUNC = pcpatch_agg_transfn,
	STYPE = pointcloud_abs,
	FINALFUNC = pcpatch_agg_final_pcpatch
);

CREATE OR REPLACE FUNCTION PC_Explode(p pcpatch)
	RETURNS setof pcpoint AS 'MODULE_PATHNAME', 'pcpatch_unnest'
	LANGUAGE 'c' IMMUTABLE STRICT;


-------------------------------------------------------------------
--  SQL Utility Functions
-------------------------------------------------------------------