[SCM] PostGIS branch master updated. 3.6.0rc2-33-gff7d11679
git at osgeo.org
git at osgeo.org
Fri Sep 5 14:56:49 PDT 2025
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, master has been updated
via ff7d11679c1851caa081a730a70fd29d7e8f84e7 (commit)
from c8d484f696bb72327faf4cf0be196cf23964ab3e (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 ff7d11679c1851caa081a730a70fd29d7e8f84e7
Author: Paul Ramsey <pramsey at cleverelephant.ca>
Date: Fri Sep 5 14:53:19 2025 -0700
Return to regex and constraint support for geometry_columns view, references #5978
diff --git a/NEWS b/NEWS
index af8c01d2f..30797bd86 100644
--- a/NEWS
+++ b/NEWS
@@ -1,6 +1,7 @@
PostGIS 3.7.0dev
xxxx/xx/xx
+ - #5978, return ability of geometry_columns to read constraints
PostGIS 3.6.0
2025/xx/xx
diff --git a/postgis/postgis.sql.in b/postgis/postgis.sql.in
index 50c9ba732..03a7fdde8 100644
--- a/postgis/postgis.sql.in
+++ b/postgis/postgis.sql.in
@@ -6383,17 +6383,43 @@ LANGUAGE 'sql' STABLE STRICT PARALLEL SAFE _COST_MEDIUM;
-- Changed: 2.5.2 replace use of pg_constraint.consrc with pg_get_constraintdef, consrc removed pg12
CREATE OR REPLACE VIEW geometry_columns AS
- SELECT current_database()::character varying(256) AS f_table_catalog,
- n.nspname AS f_table_schema,
- c.relname AS f_table_name,
- a.attname AS f_geometry_column,
- COALESCE(postgis_typmod_dims(a.atttypmod), 2) AS coord_dimension,
- COALESCE(NULLIF(postgis_typmod_srid(a.atttypmod), 0), 0) AS srid,
- replace(replace(COALESCE(NULLIF(upper(postgis_typmod_type(a.atttypmod)), 'GEOMETRY'::text), 'GEOMETRY'::text), 'ZM'::text, ''::text), 'Z'::text, ''::text)::character varying(30) AS type
+SELECT current_database()::character varying(256) AS f_table_catalog,
+ n.nspname AS f_table_schema,
+ c.relname AS f_table_name,
+ a.attname AS f_geometry_column,
+ COALESCE(postgis_typmod_dims(a.atttypmod), sn.ndims, 2) AS coord_dimension,
+ COALESCE(NULLIF(postgis_typmod_srid(a.atttypmod), 0), sr.srid, 0) AS srid,
+ replace(replace(COALESCE(NULLIF(upper(postgis_typmod_type(a.atttypmod)), 'GEOMETRY'::text), st.type, 'GEOMETRY'::text), 'ZM'::text, ''::text), 'Z'::text, ''::text)::character varying(30) AS type
FROM pg_class c
- JOIN pg_attribute a ON a.attrelid = c.oid AND NOT a.attisdropped
- JOIN pg_namespace n ON c.relnamespace = n.oid
- JOIN pg_type t ON a.atttypid = t.oid
+ JOIN pg_attribute a ON a.attrelid = c.oid AND NOT a.attisdropped
+ JOIN pg_namespace n ON c.relnamespace = n.oid
+ JOIN pg_type t ON a.atttypid = t.oid
+ LEFT JOIN ( SELECT s.connamespace,
+ s.conrelid,
+ s.conkey,
+ (regexp_match(s.consrc, $$geometrytype\(\w+\)\s*=\s*'(\w+)'$$, 'i'))[1]::text AS type
+ FROM (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
+ FROM pg_constraint) AS s
+ WHERE s.consrc ~* $$geometrytype\(\w+\)\s*=\s*'\w+'$$::text
+
+) st ON st.connamespace = n.oid AND st.conrelid = c.oid AND (a.attnum = ANY (st.conkey))
+ LEFT JOIN ( SELECT s.connamespace,
+ s.conrelid,
+ s.conkey,
+ (regexp_match(s.consrc, $$ndims\(\w+\)\s*=\s*(\d+)$$, 'i'))[1]::integer AS ndims
+ FROM (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
+ FROM pg_constraint) AS s
+ WHERE s.consrc ~* $$ndims\(\w+\)\s*=\s*\d+$$::text
+) sn ON sn.connamespace = n.oid AND sn.conrelid = c.oid AND (a.attnum = ANY (sn.conkey))
+ LEFT JOIN ( SELECT s.connamespace,
+ s.conrelid,
+ s.conkey,
+ (regexp_match(s.consrc, $$srid\(\w+\)\s*=\s*(\d+)$$, 'i'))[1]::integer As srid
+ FROM (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
+ FROM pg_constraint) AS s
+ WHERE s.consrc ~* $$srid\(\w+\)\s*=\s*\d+$$::text
+
+) sr ON sr.connamespace = n.oid AND sr.conrelid = c.oid AND (a.attnum = ANY (sr.conkey))
WHERE (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'm'::"char", 'f'::"char", 'p'::"char"]))
AND NOT c.relname = 'raster_columns'::name AND t.typname = 'geometry'::name
AND NOT pg_is_other_temp_schema(c.relnamespace) AND has_table_privilege(c.oid, 'SELECT'::text);
diff --git a/regress/core/tickets.sql b/regress/core/tickets.sql
index 9ac1f216b..81eae6fae 100644
--- a/regress/core/tickets.sql
+++ b/regress/core/tickets.sql
@@ -1579,3 +1579,22 @@ WHERE ST_DFullyWithin(
SELECT '#5876', ST_AsText(ST_AddPoint(
'LINESTRING (1 1, 2 2)'::geometry,
'POINT EMPTY'::geometry), 2);
+
+-- -------------------------------------------------------------------------------------
+-- #5978, geometry_columns not showing right SRID and Type
+-- #5829, SELECT geometry_columns returns unexpected error
+CREATE TABLE test5829 (
+ geom geometry);
+ALTER TABLE test5829
+ ADD CONSTRAINT c1
+ CHECK (ST_SRID(geom)=4326 and ST_IsValid(geom));
+CREATE TABLE public.test5978 (
+ OBJECTID SERIAL NOT NULL,
+ PKEY INTEGER,
+ PRIMARY KEY (OBJECTID));
+SELECT AddGeometryColumn('public', 'test5978', 'shape', 4326, upper('POINT'), 2, false);
+SELECT AddGeometryColumn('public', 'test5978', 'geometry', 4326, upper('POINT'), 2, true);
+SELECT f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, type
+ FROM geometry_columns WHERE f_table_name IN ('test5829', 'test5978')
+ ORDER BY f_table_name, f_geometry_column;
+DROP TABLE IF EXISTS test5829, test5978;
diff --git a/regress/core/tickets_expected b/regress/core/tickets_expected
index d18abe567..37218c8f9 100644
--- a/regress/core/tickets_expected
+++ b/regress/core/tickets_expected
@@ -484,3 +484,8 @@ ERROR: Geometry contains invalid coordinates
#5747|0
#5855|Knosesmauet
#5876|LINESTRING(1 1,2 2)
+public.test5978.shape SRID:4326 TYPE:POINT DIMS:2
+public.test5978.geometry SRID:4326 TYPE:POINT DIMS:2
+public|test5829|geom|2|4326|GEOMETRY
+public|test5978|geometry|2|4326|POINT
+public|test5978|shape|2|4326|POINT
-----------------------------------------------------------------------
Summary of changes:
NEWS | 1 +
postgis/postgis.sql.in | 46 +++++++++++++++++++++++++++++++++----------
regress/core/tickets.sql | 19 ++++++++++++++++++
regress/core/tickets_expected | 5 +++++
4 files changed, 61 insertions(+), 10 deletions(-)
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list