From 777ca05fce8f302e22f3897c63f774e003cc6530 Mon Sep 17 00:00:00 2001
From: Regina Obe <lr@pcorp.us>
Date: Sun, 8 May 2011 17:18:38 +0000
Subject: [PATCH] more cleanup.  Get rid of more deprecated functions, put in
 more stable,imuutable markers.  Change some functions from plpgsql to sql

git-svn-id: http://svn.osgeo.org/postgis/trunk@7112 b70326c6-7e19-0410-871a-916f4a2858ee
---
 .../tiger_2010/geocode/reverse_geocode.sql               | 2 +-
 .../tiger_geocoder/tiger_2010/normalize/end_soundex.sql  | 2 +-
 .../tiger_2010/normalize/get_last_words.sql              | 2 +-
 .../tiger_2010/normalize/location_extract.sql            | 2 +-
 .../normalize/location_extract_countysub_exact.sql       | 2 +-
 .../normalize/location_extract_place_exact.sql           | 2 +-
 .../normalize/location_extract_place_fuzzy.sql           | 2 +-
 .../tiger_geocoder/tiger_2010/normalize/pprint_addy.sql  | 2 +-
 .../tiger_2010/normalize/state_extract.sql               | 2 +-
 extras/tiger_geocoder/tiger_2010/utility/cull_null.sql   | 6 ++----
 .../tiger_2010/utility/levenshtein_ignore_case.sql       | 9 ++-------
 .../tiger_2010/utility/nullable_levenshtein.sql          | 2 +-
 extras/tiger_geocoder/tiger_2010/utility/utmzone.sql     | 6 +++---
 13 files changed, 17 insertions(+), 24 deletions(-)

diff --git a/extras/tiger_geocoder/tiger_2010/geocode/reverse_geocode.sql b/extras/tiger_geocoder/tiger_2010/geocode/reverse_geocode.sql
index eb97e25e7..1b0638c19 100644
--- a/extras/tiger_geocoder/tiger_2010/geocode/reverse_geocode.sql
+++ b/extras/tiger_geocoder/tiger_2010/geocode/reverse_geocode.sql
@@ -98,4 +98,4 @@ $$
 -- default to not include street range in cross streets
     SELECT reverse_geocode($1,false);
 $$
-language sql STABLE;
\ No newline at end of file
+language sql STABLE COST 1000;
\ No newline at end of file
diff --git a/extras/tiger_geocoder/tiger_2010/normalize/end_soundex.sql b/extras/tiger_geocoder/tiger_2010/normalize/end_soundex.sql
index 02500ddb1..834b6ed1a 100644
--- a/extras/tiger_geocoder/tiger_2010/normalize/end_soundex.sql
+++ b/extras/tiger_geocoder/tiger_2010/normalize/end_soundex.sql
@@ -14,4 +14,4 @@ BEGIN
   END IF;
   return tempString;
 END;
-$_$ LANGUAGE plpgsql;
+$_$ LANGUAGE plpgsql IMMUTABLE;
diff --git a/extras/tiger_geocoder/tiger_2010/normalize/get_last_words.sql b/extras/tiger_geocoder/tiger_2010/normalize/get_last_words.sql
index 54058a288..7e9cfd3de 100644
--- a/extras/tiger_geocoder/tiger_2010/normalize/get_last_words.sql
+++ b/extras/tiger_geocoder/tiger_2010/normalize/get_last_words.sql
@@ -26,4 +26,4 @@ BEGIN
 
   RETURN result;
 END;
-$_$ LANGUAGE plpgsql;
+$_$ LANGUAGE plpgsql IMMUTABLE COST 10;
diff --git a/extras/tiger_geocoder/tiger_2010/normalize/location_extract.sql b/extras/tiger_geocoder/tiger_2010/normalize/location_extract.sql
index e90166038..b855303a8 100644
--- a/extras/tiger_geocoder/tiger_2010/normalize/location_extract.sql
+++ b/extras/tiger_geocoder/tiger_2010/normalize/location_extract.sql
@@ -82,4 +82,4 @@ BEGIN
 
   RETURN location;
 END;
-$_$ LANGUAGE plpgsql;
+$_$ LANGUAGE plpgsql STABLE COST 100;
diff --git a/extras/tiger_geocoder/tiger_2010/normalize/location_extract_countysub_exact.sql b/extras/tiger_geocoder/tiger_2010/normalize/location_extract_countysub_exact.sql
index f2303a344..a73d3d6fb 100644
--- a/extras/tiger_geocoder/tiger_2010/normalize/location_extract_countysub_exact.sql
+++ b/extras/tiger_geocoder/tiger_2010/normalize/location_extract_countysub_exact.sql
@@ -53,4 +53,4 @@ BEGIN
 
   RETURN location;
 END;
-$_$ LANGUAGE plpgsql;
+$_$ LANGUAGE plpgsql STABLE COST 10;
diff --git a/extras/tiger_geocoder/tiger_2010/normalize/location_extract_place_exact.sql b/extras/tiger_geocoder/tiger_2010/normalize/location_extract_place_exact.sql
index 3f5a1fd98..62fc7da06 100644
--- a/extras/tiger_geocoder/tiger_2010/normalize/location_extract_place_exact.sql
+++ b/extras/tiger_geocoder/tiger_2010/normalize/location_extract_place_exact.sql
@@ -56,4 +56,4 @@ BEGIN
 
   RETURN location;
 END;
-$_$ LANGUAGE plpgsql;
+$_$ LANGUAGE plpgsql STABLE COST 100;
diff --git a/extras/tiger_geocoder/tiger_2010/normalize/location_extract_place_fuzzy.sql b/extras/tiger_geocoder/tiger_2010/normalize/location_extract_place_fuzzy.sql
index 899558ace..30451b0da 100644
--- a/extras/tiger_geocoder/tiger_2010/normalize/location_extract_place_fuzzy.sql
+++ b/extras/tiger_geocoder/tiger_2010/normalize/location_extract_place_fuzzy.sql
@@ -85,4 +85,4 @@ BEGIN
 
   RETURN location;
 END;
-$_$ LANGUAGE plpgsql;
+$_$ LANGUAGE plpgsql STABLE;
diff --git a/extras/tiger_geocoder/tiger_2010/normalize/pprint_addy.sql b/extras/tiger_geocoder/tiger_2010/normalize/pprint_addy.sql
index beb216fd6..b09605e6a 100644
--- a/extras/tiger_geocoder/tiger_2010/normalize/pprint_addy.sql
+++ b/extras/tiger_geocoder/tiger_2010/normalize/pprint_addy.sql
@@ -33,4 +33,4 @@ BEGIN
   RETURN trim(result);
 
 END;
-$_$ LANGUAGE plpgsql;
+$_$ LANGUAGE plpgsql IMMUTABLE;
diff --git a/extras/tiger_geocoder/tiger_2010/normalize/state_extract.sql b/extras/tiger_geocoder/tiger_2010/normalize/state_extract.sql
index 193df2bd3..a599f9f6a 100644
--- a/extras/tiger_geocoder/tiger_2010/normalize/state_extract.sql
+++ b/extras/tiger_geocoder/tiger_2010/normalize/state_extract.sql
@@ -86,4 +86,4 @@ BEGIN
 
   RETURN result;
 END;
-$_$ LANGUAGE plpgsql;
+$_$ LANGUAGE plpgsql STABLE;
diff --git a/extras/tiger_geocoder/tiger_2010/utility/cull_null.sql b/extras/tiger_geocoder/tiger_2010/utility/cull_null.sql
index dd11ced6b..4c9c89a01 100644
--- a/extras/tiger_geocoder/tiger_2010/utility/cull_null.sql
+++ b/extras/tiger_geocoder/tiger_2010/utility/cull_null.sql
@@ -2,7 +2,5 @@
 -- This is used to concatinate values that may be null.
 CREATE OR REPLACE FUNCTION cull_null(VARCHAR) RETURNS VARCHAR
 AS $_$
-BEGIN
-    RETURN coalesce($1,'');
-END;
-$_$ LANGUAGE plpgsql;
+    SELECT coalesce($1,'');
+$_$ LANGUAGE sql IMMUTABLE;
diff --git a/extras/tiger_geocoder/tiger_2010/utility/levenshtein_ignore_case.sql b/extras/tiger_geocoder/tiger_2010/utility/levenshtein_ignore_case.sql
index 7e1fa9fa2..6447ae604 100644
--- a/extras/tiger_geocoder/tiger_2010/utility/levenshtein_ignore_case.sql
+++ b/extras/tiger_geocoder/tiger_2010/utility/levenshtein_ignore_case.sql
@@ -1,10 +1,5 @@
 -- This function determines the levenshtein distance irespective of case.
 CREATE OR REPLACE FUNCTION levenshtein_ignore_case(VARCHAR, VARCHAR) RETURNS INTEGER
 AS $_$
-DECLARE
-  result INTEGER;
-BEGIN
-  result := levenshtein(upper($1), upper($2));
-  RETURN result;
-END
-$_$ LANGUAGE plpgsql;
+  SELECT levenshtein(upper($1), upper($2));
+$_$ LANGUAGE sql IMMUTABLE;
diff --git a/extras/tiger_geocoder/tiger_2010/utility/nullable_levenshtein.sql b/extras/tiger_geocoder/tiger_2010/utility/nullable_levenshtein.sql
index 69be7377b..8bc205b67 100644
--- a/extras/tiger_geocoder/tiger_2010/utility/nullable_levenshtein.sql
+++ b/extras/tiger_geocoder/tiger_2010/utility/nullable_levenshtein.sql
@@ -25,4 +25,4 @@ BEGIN
 
   RETURN result;
 END
-$_$ LANGUAGE plpgsql;
+$_$ LANGUAGE plpgsql IMMUTABLE COST 10;
diff --git a/extras/tiger_geocoder/tiger_2010/utility/utmzone.sql b/extras/tiger_geocoder/tiger_2010/utility/utmzone.sql
index 485e0b7ce..5afdfc410 100644
--- a/extras/tiger_geocoder/tiger_2010/utility/utmzone.sql
+++ b/extras/tiger_geocoder/tiger_2010/utility/utmzone.sql
@@ -5,13 +5,13 @@ DECLARE
     zone int;
     pref int;
 BEGIN
-    geomgeog:=transform($1,4326);
-    IF (y(geomgeog))>0 THEN
+    geomgeog:=ST_Transform($1,4326);
+    IF (ST_Y(geomgeog))>0 THEN
         pref:=32600;
     ELSE
         pref:=32700;
     END IF;
-    zone:=floor((x(geomgeog)+180)/6)+1;
+    zone:=floor((ST_X(geomgeog)+180)/6)+1;
     RETURN zone+pref;
 END;
 $BODY$ LANGUAGE 'plpgsql' immutable;
-- 
2.50.1