update postcode in place if it already exists

diff --git a/lib-sql/functions/place_triggers.sql b/lib-sql/functions/place_triggers.sql
index dd0f16623e55cacecbd6f7c5baddc611b5ac033a..1538921560eed9a1a8e8c4c7d446d6cb4f25b496 100644 (file)
--- a/lib-sql/functions/place_triggers.sql
+++ b/lib-sql/functions/place_triggers.sql
@@ -97,6 +97,19 @@ BEGIN
     IF NEW.class = 'place' AND NEW.type = 'postcode' THEN
       -- Remove old placex entry.
       DELETE FROM placex where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
+
+      IF existing.osm_type IS NOT NULL THEN
+        IF coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore)
+           OR existing.geometry::text != NEW.geometry::text
+        THEN
+
+          update place set address = NEW.address, geometry = NEW.geometry
+            where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
+        END IF;
+
+        RETURN NULL;
+      END IF;
+
       RETURN NEW;
     END IF;
 

diff --git a/test/bdd/db/update/postcode.feature b/test/bdd/db/update/postcode.feature
index fbac93411db03223d7bbc006df5c2b7c7e3ba248..e25e286711809b3621c0d235de8403b20a7d2ad0 100644 (file)
--- a/test/bdd/db/update/postcode.feature
+++ b/test/bdd/db/update/postcode.feature
@@ -56,3 +56,51 @@ Feature: Update of postcode
            | word  | class | type |
            | 01982 | place | postcode |
 
+     Scenario: Updating a postcode is reflected in postcode table
+        Given the places
+           | osm | class | type     | addr+postcode |  geometry |
+           | N34 | place | postcode | 01982         | country:de |
+        When importing
+        And updating places
+           | osm | class | type     | addr+postcode |  geometry |
+           | N34 | place | postcode | 20453         | country:de |
+        And updating postcodes
+        Then location_postcode contains exactly
+           | country | postcode | geometry |
+           | de      | 20453    | country:de |
+        And word contains
+           | word  | class | type |
+           | 20453 | place | postcode |
+
+     Scenario: When changing from a postcode type, the entry appears in placex
+        When importing
+        And updating places
+           | osm | class | type     | addr+postcode |  geometry |
+           | N34 | place | postcode | 01982         | country:de |
+        Then placex has no entry for N34
+        When updating places
+           | osm | class | type  | addr+postcode | housenr |  geometry |
+           | N34 | place | house | 20453         | 1       | country:de |
+        Then placex contains
+           | object | addr+housenumber | geometry |
+           | N34    | 1                | country:de|
+        When updating postcodes
+        Then location_postcode contains exactly
+           | country | postcode | geometry |
+           | de      | 20453    | country:de |
+        And word contains
+           | word  | class | type |
+           | 20453 | place | postcode |
+
+     Scenario: When changing to a postcode type, the entry disappears from placex
+        When importing
+        And updating places
+           | osm | class | type  | addr+postcode | housenr |  geometry |
+           | N34 | place | house | 20453         | 1       | country:de |
+        Then placex contains
+           | object | addr+housenumber | geometry |
+           | N34    | 1                | country:de|
+        When updating places
+           | osm | class | type     | addr+postcode |  geometry |
+           | N34 | place | postcode | 01982         | country:de |
+        Then placex has no entry for N34