pggeodb.nancy.inra.fr/db_robot - db_robot on pggeodb.nancy.inra.fr

Function: populate_topology_layer

Schema

topology

Owner

postgres

Descriptions

Adds missing entries to topology.layer table by reading metadata from topo tables.

Options

Definition

CREATE OR REPLACE FUNCTION topology.populate_topology_layer (
)
RETURNS TABLE (
 schema_name text,
 table_name text,
 feature_column text
) AS
$span$
 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;
$span$
LANGUAGE 'sql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

COMMENT ON FUNCTION topology.populate_topology_layer()
IS 'Adds missing entries to topology.layer table by reading metadata from topo tables.';

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 26/02/2014 11:51