From 426be352b23973e96b00c6a738e3e6ce37d844d9 Mon Sep 17 00:00:00 2001
From: Bborie Park <bkpark at ucdavis.edu>
Date: Sat, 2 May 2015 19:48:17 +0000
Subject: [PATCH] round to 10 places instead of numeric(25,10). ticket #3006

git-svn-id: http://svn.osgeo.org/postgis/trunk@13467 b70326c6-7e19-0410-871a-916f4a2858ee
---
 raster/rt_pg/rtpostgis.sql.in                 | 48 ++++++++++++++-----
 raster/rt_pg/rtpostgis_upgrade_cleanup.sql.in |  2 +
 2 files changed, 39 insertions(+), 11 deletions(-)

diff --git a/raster/rt_pg/rtpostgis.sql.in b/raster/rt_pg/rtpostgis.sql.in
index f380612d1..1286c94b9 100644
--- a/raster/rt_pg/rtpostgis.sql.in
+++ b/raster/rt_pg/rtpostgis.sql.in
@@ -255,8 +255,8 @@ CREATE OR REPLACE FUNCTION st_summary(rast raster)
 		msg := msg || 'and extent of ' || extent;
 
 		IF
-			metadata.skewx::numeric(16, 10) <> 0::numeric(16, 10) OR 
-			metadata.skewy::numeric(16, 10) <> 0::numeric(16, 10)
+			round(metadata.skewx::numeric, 10) <> round(0::numeric, 10) OR 
+			round(metadata.skewy::numeric, 10) <> round(0::numeric, 10)
 		THEN
 			msg := 'Skewed ' || overlay(msg placing 'r' from 1 for 1);
 		END IF;
@@ -6945,7 +6945,22 @@ CREATE OR REPLACE FUNCTION _drop_raster_constraint_srid(rastschema name, rasttab
 CREATE OR REPLACE FUNCTION _raster_constraint_info_scale(rastschema name, rasttable name, rastcolumn name, axis char)
 	RETURNS double precision AS $$
 	SELECT
-		replace(replace(split_part(split_part(s.consrc, ' = ', 2), '::', 1), ')', ''), '(', '')::double precision
+		replace(
+			replace(
+				replace(
+					replace(
+						split_part(
+							split_part(s.consrc, ' = ', 2),
+						 	'::', 1
+						),
+						'round(', ''
+					),
+					')', ''
+				),
+				'(', ''
+			),
+			' ', ''
+		)::double precision
 	FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
 	WHERE n.nspname = $1
 		AND c.relname = $2
@@ -6994,9 +7009,9 @@ CREATE OR REPLACE FUNCTION _add_raster_constraint_scale(rastschema name, rasttab
 
 		sql := 'ALTER TABLE ' || fqtn
 			|| ' ADD CONSTRAINT ' || quote_ident(cn)
-			|| ' CHECK (st_scale' || $4 || '('
+			|| ' CHECK (round(st_scale' || $4 || '('
 			|| quote_ident($3)
-			|| ')::numeric(25,10) = (' || attr || ')::numeric(25,10))';
+			|| ')::numeric, 10) = round(' || attr || '::numeric, 10))';
 		RETURN _add_raster_constraint(cn, sql);
 	END;
 	$$ LANGUAGE 'plpgsql' VOLATILE STRICT
@@ -7525,7 +7540,18 @@ CREATE OR REPLACE FUNCTION _drop_raster_constraint_pixel_types(rastschema name,
 CREATE OR REPLACE FUNCTION _raster_constraint_info_nodata_values(rastschema name, rasttable name, rastcolumn name)
 	RETURNS double precision[] AS $$
 	SELECT
-		trim(both '''' from split_part(replace(replace(split_part(s.consrc, ' = ', 2), ')', ''), '(', ''), '::', 1))::double precision[]
+		trim(both '''' from
+			split_part(
+				replace(
+					replace(
+						split_part(s.consrc, ' = ', 2),
+					 	')', ''
+					),
+					'(', ''
+				),
+				'::', 1
+			)
+		)::double precision[]
 	FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
 	WHERE n.nspname = $1
 		AND c.relname = $2
@@ -7539,8 +7565,8 @@ CREATE OR REPLACE FUNCTION _raster_constraint_info_nodata_values(rastschema name
   COST 100;
 
 CREATE OR REPLACE FUNCTION _raster_constraint_nodata_values(rast raster)
-	RETURNS double precision[] AS
-	$$ SELECT array_agg(nodatavalue)::double precision[] FROM st_bandmetadata($1, ARRAY[]::int[]); $$
+	RETURNS numeric[] AS
+	$$ SELECT array_agg(round(nodatavalue::numeric, 10))::numeric[] FROM st_bandmetadata($1, ARRAY[]::int[]); $$
 	LANGUAGE 'sql' STABLE STRICT;
 
 CREATE OR REPLACE FUNCTION _add_raster_constraint_nodata_values(rastschema name, rasttable name, rastcolumn name)
@@ -7549,7 +7575,7 @@ CREATE OR REPLACE FUNCTION _add_raster_constraint_nodata_values(rastschema name,
 		fqtn text;
 		cn name;
 		sql text;
-		attr double precision[];
+		attr numeric[];
 		max int;
 	BEGIN
 		fqtn := '';
@@ -7579,7 +7605,7 @@ CREATE OR REPLACE FUNCTION _add_raster_constraint_nodata_values(rastschema name,
 		sql := 'ALTER TABLE ' || fqtn
 			|| ' ADD CONSTRAINT ' || quote_ident(cn)
 			|| ' CHECK (_raster_constraint_nodata_values(' || quote_ident($3)
-			|| ')::numeric(16,10)[] = ''{';
+			|| ')::numeric[] = ''{';
 		FOR x in 1..max LOOP
 			IF attr[x] IS NULL THEN
 				sql := sql || 'NULL';
@@ -7590,7 +7616,7 @@ CREATE OR REPLACE FUNCTION _add_raster_constraint_nodata_values(rastschema name,
 				sql := sql || ',';
 			END IF;
 		END LOOP;
-		sql := sql || '}''::numeric(16,10)[])';
+		sql := sql || '}''::numeric[])';
 
 		RETURN _add_raster_constraint(cn, sql);
 	END;
diff --git a/raster/rt_pg/rtpostgis_upgrade_cleanup.sql.in b/raster/rt_pg/rtpostgis_upgrade_cleanup.sql.in
index ead170248..ea377eede 100644
--- a/raster/rt_pg/rtpostgis_upgrade_cleanup.sql.in
+++ b/raster/rt_pg/rtpostgis_upgrade_cleanup.sql.in
@@ -578,3 +578,5 @@ DROP FUNCTION IF EXISTS _st_aspect4ma(float8[], text, text[]);
 DROP FUNCTION IF EXISTS _st_hillshade4ma(float8[], text, text[]);
 DROP FUNCTION IF EXISTS _st_slope4ma(float8[], text, text[]);
 
+-- function signature change
+DROP FUNCTION IF EXISTS _raster_constraint_nodata_values(rast raster);
-- 
2.50.1