[postgis-tickets] [SCM] PostGIS branch spatial-ref-sys-view updated. 3.2.0-459-g17f9bfb6b
git at osgeo.org
git at osgeo.org
Tue Feb 1 17:34:52 PST 2022
This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "PostGIS".
The branch, spatial-ref-sys-view has been updated
via 17f9bfb6bdfba2905597d3dc21ec978442e43de9 (commit)
from 7afbc96406cde4e7a4198f21cad5bfaf8144e0ac (commit)
Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.
- Log -----------------------------------------------------------------
commit 17f9bfb6bdfba2905597d3dc21ec978442e43de9
Author: Sandro Santilli <strk at kbt.io>
Date: Wed Feb 2 02:31:15 2022 +0100
Enhance spatial_ref_sys view trigger
- Raise an exception if an attempt is made to insert records with SRID
already known (system or user)
- Raise an exception if an attempt is made to delete records which
only exist as system ones (no user override)
- Raise notices about overrides addition/removal
- Test all these cases
diff --git a/postgis/postgis.sql.in b/postgis/postgis.sql.in
index 2388d287d..feac5fd24 100644
--- a/postgis/postgis.sql.in
+++ b/postgis/postgis.sql.in
@@ -2088,18 +2088,48 @@ CREATE OR REPLACE FUNCTION spatial_ref_sys_trigger()
RETURNS trigger AS $BODY$
BEGIN
IF ( TG_OP = 'INSERT' ) THEN
- INSERT INTO spatial_ref_sys_user VALUES (NEW.*);
- RETURN NEW;
+ IF EXISTS ( SELECT 1 FROM spatial_ref_sys_postgis WHERE srid = NEW.srid )
+ THEN
+ RAISE unique_violation
+ USING MESSAGE = format('Duplicate system SRID value (%s) already exists.', NEW.srid);
+ ELSE
+ BEGIN
+ INSERT INTO spatial_ref_sys_user VALUES (NEW.*);
+ RETURN NEW;
+ EXCEPTION
+ WHEN unique_violation THEN
+ RAISE unique_violation
+ USING MESSAGE = format('Duplicate user override for SRID value (%s) already exists.', NEW.srid);
+ END;
+ END IF;
ELSIF ( TG_OP = 'DELETE' ) THEN
DELETE FROM spatial_ref_sys_user WHERE srid = OLD.srid;
IF NOT FOUND THEN
+ IF EXISTS ( SELECT 1 FROM spatial_ref_sys_postgis WHERE srid = OLD.srid )
+ THEN
+ RAISE EXCEPTION 'System SRID (%) cannot be deleted', OLD.srid;
+ END IF;
RETURN NULL;
ELSE
+ RAISE NOTICE 'User override for SRID value % deleted', OLD.srid;
RETURN OLD;
END IF;
ELSIF ( TG_OP = 'UPDATE' ) THEN
- INSERT INTO spatial_ref_sys_user VALUES (NEW.*);
- RETURN NULL;
+ BEGIN
+ INSERT INTO spatial_ref_sys_user VALUES (NEW.*);
+ RAISE NOTICE 'User override for SRID value % added', OLD.srid;
+ EXCEPTION
+ WHEN unique_violation THEN
+ UPDATE spatial_ref_sys_user
+ SET
+ auth_name = NEW.auth_name,
+ auth_srid = NEW.auth_srid,
+ srtext = NEW.srtext,
+ proj4text = NEW.proj4text
+ WHERE srid = NEW.srid;
+ RAISE NOTICE 'SRID override % updated', OLD.srid;
+ END;
+ RETURN NEW;
ELSE
RAISE EXCEPTION 'Unsupported trigger operation %', TG_OP;
END IF;
diff --git a/regress/core/spatial_ref_sys.sql b/regress/core/spatial_ref_sys.sql
index b2ac0fc62..15b17d953 100644
--- a/regress/core/spatial_ref_sys.sql
+++ b/regress/core/spatial_ref_sys.sql
@@ -1,23 +1,44 @@
-BEGIN;
-
-- https://trac.osgeo.org/postgis/ticket/5024
SELECT '#5024', 'spatial_ref_sys entries' AS table_name, count(*) FROM spatial_ref_sys;
INSERT INTO spatial_ref_sys(srid,auth_name,auth_srid,srtext,proj4text)
- SELECT srid,'overridden',0,srtext,proj4text
- FROM spatial_ref_sys WHERE srid = 4326;
-SELECT 'inserted_overridden_srid', srid, auth_name
- FROM spatial_ref_sys
- WHERE srid = 4326;
+ SELECT srid,'inserted_duplicate',0,srtext,proj4text
+ FROM spatial_ref_sys WHERE srid = 4326
+ RETURNING 'insert-duplicate-return', srid, auth_name;
-DELETE FROM spatial_ref_sys WHERE srid = 4326;
-SELECT 'deleted_overridden_srid', srid, auth_name
- FROM spatial_ref_sys
- WHERE srid = 4326;
+UPDATE spatial_ref_sys
+ SET auth_name = 'updated'
+ WHERE srid = 4326
+ RETURNING 'update-return', srid, auth_name;
-UPDATE spatial_ref_sys SET auth_name = 'overridden' WHERE srid = 4326;
-SELECT 'updated_overridden_srid', srid, auth_name
+DELETE FROM spatial_ref_sys WHERE srid = 4326
+RETURNING 'delete-return', srid, auth_name;
+SELECT 'select-after-delete', srid, auth_name
FROM spatial_ref_sys
WHERE srid = 4326;
-ROLLBACK;
+DELETE FROM spatial_ref_sys WHERE srid = 4326
+RETURNING 'delete-again-return', srid, auth_name;
+
+-- Insert, update and delete a custom SRID
+
+INSERT INTO spatial_ref_sys(srid,auth_name,auth_srid,srtext,proj4text)
+ SELECT 901000,'inserted_custom',0,srtext,proj4text
+ FROM spatial_ref_sys WHERE srid = 4326
+ RETURNING 'insert-unallocated-return', srid, auth_name;
+
+INSERT INTO spatial_ref_sys(srid,auth_name,auth_srid,srtext,proj4text)
+ SELECT 901000,'inserted_custom',0,srtext,proj4text
+ FROM spatial_ref_sys WHERE srid = 4326
+ RETURNING 'insert-unallocated-duplicate-return', srid, auth_name;
+
+UPDATE spatial_ref_sys
+ SET auth_name = 'updated_custom'
+ WHERE srid = 901000
+ RETURNING 'update-unallocated-return', srid, auth_name;
+
+DELETE FROM spatial_ref_sys WHERE srid = 901000
+RETURNING 'delete-custom-return', srid, auth_name;
+SELECT 'select-after-delete-custom', srid, auth_name
+ FROM spatial_ref_sys
+ WHERE srid = 901000;
diff --git a/regress/core/spatial_ref_sys_expected b/regress/core/spatial_ref_sys_expected
index 531df8493..472b603d2 100644
--- a/regress/core/spatial_ref_sys_expected
+++ b/regress/core/spatial_ref_sys_expected
@@ -1,4 +1,14 @@
#5024|spatial_ref_sys entries|8500
-inserted_overridden_srid|4326|overridden
-deleted_overridden_srid|4326|EPSG
-updated_overridden_srid|4326|overridden
+ERROR: Duplicate system SRID value (4326) already exists.
+NOTICE: User override for SRID value 4326 added
+update-return|4326|updated
+NOTICE: User override for SRID value 4326 deleted
+delete-return|4326|updated
+select-after-delete|4326|EPSG
+ERROR: System SRID (4326) cannot be deleted
+insert-unallocated-return|901000|inserted_custom
+ERROR: Duplicate user override for SRID value (901000) already exists.
+NOTICE: SRID override 901000 updated
+update-unallocated-return|901000|updated_custom
+NOTICE: User override for SRID value 901000 deleted
+delete-custom-return|901000|updated_custom
-----------------------------------------------------------------------
Summary of changes:
postgis/postgis.sql.in | 38 ++++++++++++++++++++++++---
regress/core/spatial_ref_sys.sql | 49 +++++++++++++++++++++++++----------
regress/core/spatial_ref_sys_expected | 16 +++++++++---
3 files changed, 82 insertions(+), 21 deletions(-)
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list