From 2f058bca6ca8aa3266bbd37148f233764b9da597 Mon Sep 17 00:00:00 2001
From: Sandro Santilli <strk@keybit.net>
Date: Wed, 25 Nov 2015 23:22:15 +0000
Subject: [PATCH] Add topology.populate_topology_layer function

Useful for passing data around.

git-svn-id: http://svn.osgeo.org/postgis/trunk@14432 b70326c6-7e19-0410-871a-916f4a2858ee
---
 NEWS                     |  3 ++-
 topology/topology.sql.in | 47 ++++++++++++++++++++++++++++++++++++++++
 2 files changed, 49 insertions(+), 1 deletion(-)

diff --git a/NEWS b/NEWS
index 9324d6047..b9ca64672 100644
--- a/NEWS
+++ b/NEWS
@@ -5,7 +5,8 @@ PostGIS 2.3.0
  * Deprecated signatures *
  * New Features *
 
-  - TopoGeom_addElement, TopoGeom_remElement (Sando Santilli)
+  - TopoGeom_addElement, TopoGeom_remElement (Sandro Santilli)
+  - populate_topology_layer (Sandro Santilli)
 
 PostGIS 2.2.0
 2015/10/07
diff --git a/topology/topology.sql.in b/topology/topology.sql.in
index 92af06f34..070266924 100644
--- a/topology/topology.sql.in
+++ b/topology/topology.sql.in
@@ -812,6 +812,53 @@ LANGUAGE 'plpgsql' VOLATILE;
 --
 --} DropTopoGeometryColumn
 
+-- {
+--
+-- populate_topology_layer
+--
+-- Register missing layers into topology.topology, looking at
+-- their constraints.
+--
+-- The function doesn't attempt to determine if a layer is
+-- hierarchical or primitive, but always assumes primitive.
+--
+-- }{
+DROP FUNCTION IF EXISTS topology.populate_topology_layer();
+CREATE OR REPLACE FUNCTION topology.populate_topology_layer()
+	RETURNS TABLE(schema_name text, table_name text, feature_column text)
+AS
+$$
+  INSERT INTO topology.layer
+  WITH checks AS (
+  SELECT
+    n.nspname sch, r.relname tab,
+    replace(c.conname, 'check_topogeom_', '') col,
+    --c.consrc src,
+    regexp_matches(c.consrc,
+      '\.topology_id = (\d+).*\.layer_id = (\d+).*\.type = (\d+)') inf
+  FROM pg_constraint c, pg_class r, pg_namespace n
+  WHERE c.conname LIKE 'check_topogeom_%'
+    AND r.oid = c.conrelid
+    AND n.oid = r.relnamespace
+  ), newrows AS (
+    SELECT inf[1]::int as topology_id,
+           inf[2]::int as layer_id,
+          sch, tab, col, inf[3]::int as feature_type --, src
+    FROM checks c
+    WHERE NOT EXISTS (
+      SELECT * FROM topology.layer l
+      WHERE l.schema_name = c.sch
+        AND l.table_name = c.tab
+        AND l.feature_column = c.col
+    )
+  )
+  SELECT topology_id, layer_id, sch,
+         tab, col, feature_type,
+         0, NULL
+  FROM newrows RETURNING schema_name,table_name,feature_column;
+$$
+LANGUAGE 'sql' VOLATILE;
+
 
 --{
 -- CreateTopoGeom(topology_name, topogeom_type, layer_id, elements)
-- 
2.40.0