[postgis-tickets] r14432 - Add topology.populate_topology_layer function

[Sandro Santilli]

Author: strk
Date: 2015-11-25 15:22:15 -0800 (Wed, 25 Nov 2015)
New Revision: 14432

Modified:
   trunk/NEWS
   trunk/topology/topology.sql.in
Log:
Add topology.populate_topology_layer function

Useful for passing data around.

Modified: trunk/NEWS
===================================================================
--- trunk/NEWS	2015-11-25 21:49:01 UTC (rev 14431)
+++ trunk/NEWS	2015-11-25 23:22:15 UTC (rev 14432)
@@ -5,7 +5,8 @@
  * 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

Modified: trunk/topology/topology.sql.in
===================================================================
--- trunk/topology/topology.sql.in	2015-11-25 21:49:01 UTC (rev 14431)
+++ trunk/topology/topology.sql.in	2015-11-25 23:22:15 UTC (rev 14432)
@@ -812,7 +812,54 @@
 --
 --} 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)
 --