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

Function: topologysummary

Schema

topology

Owner

albenard

Descriptions

args: topology_schema_name - Takes a topology name and provides summary totals of types of objects in topology

Options

Definition

CREATE OR REPLACE FUNCTION topology.topologysummary (
 atopology varchar
)
RETURNS text AS
$span$
DECLARE
 rec RECORD;
 rec2 RECORD;
 var_topology_id integer;
 n int4;
 missing int4;
 sql text;
 ret text;
 tgcount int4;
BEGIN

 ret := 'Topology ' || quote_ident(atopology) ;

 BEGIN
   SELECT * FROM topology.topology WHERE name = atopology INTO STRICT rec;
   -- TODO: catch <no_rows> to give a nice error message
   var_topology_id := rec.id;

   ret := ret || ' (id ' || rec.id || ', '
              || 'SRID ' || rec.srid || ', '
              || 'precision ' || rec.precision;
   IF rec.hasz THEN ret := ret || ', has Z'; END IF;
   ret := ret || E')\n';
 EXCEPTION
   WHEN NO_DATA_FOUND THEN
     ret := ret || E' (unregistered)\n';
 END;

 BEGIN
   EXECUTE 'SELECT count(*) FROM ' || quote_ident(atopology)
     || '.node ' INTO STRICT n;
   ret = ret || n || ' nodes, ';
 EXCEPTION
   WHEN UNDEFINED_TABLE OR INVALID_SCHEMA_NAME THEN
     IF NOT EXISTS (
         SELECT * FROM pg_catalog.pg_namespace WHERE nspname = atopology
        )
     THEN
       ret = ret || 'missing schema';
       RETURN ret;
     ELSE
       ret = ret || 'missing nodes, ';
     END IF;
 END;

 BEGIN
   EXECUTE 'SELECT count(*) FROM ' || quote_ident(atopology)
     || '.edge' INTO STRICT n;
   ret = ret || n || ' edges, ';
 EXCEPTION
   WHEN UNDEFINED_TABLE OR INVALID_SCHEMA_NAME THEN
     ret = ret || 'missing edges, ';
 END;

 BEGIN
   EXECUTE 'SELECT count(*) FROM ' || quote_ident(atopology)
     || '.face' INTO STRICT n;
   ret = ret || greatest(n-1,0) || ' faces, '; -- -1 is face=0
 EXCEPTION
   WHEN UNDEFINED_TABLE OR INVALID_SCHEMA_NAME THEN
     ret = ret || 'missing faces, ';
 END;

 BEGIN
   EXECUTE 'SELECT count(distinct layer_id) AS ln, '
     || 'count(distinct (layer_id,topogeo_id)) AS tn FROM '
     || quote_ident(atopology) || '.relation' INTO STRICT rec;
   tgcount := rec.tn;
   ret = ret || rec.tn || ' topogeoms in ' || rec.ln || E' layers\n';
 EXCEPTION
   WHEN UNDEFINED_TABLE THEN
     ret = ret || E'missing relations\n';
   WHEN UNDEFINED_COLUMN THEN
     ret = ret || E'corrupted relations\n';
 END;

 -- print information about registered layers
 FOR rec IN SELECT * FROM topology.layer l
   WHERE l.topology_id = var_topology_id
   ORDER by layer_id
 LOOP -- {
   ret = ret || 'Layer ' || rec.layer_id || ', type ';
   CASE
     WHEN rec.feature_type = 1 THEN
       ret = ret || 'Puntal';
     WHEN rec.feature_type = 2 THEN
       ret = ret || 'Lineal';
     WHEN rec.feature_type = 3 THEN
       ret = ret || 'Polygonal';
     WHEN rec.feature_type = 4 THEN
       ret = ret || 'Mixed';
     ELSE
       ret = ret || '???';
   END CASE;

   ret = ret || ' (' || rec.feature_type || '), ';

   BEGIN

     EXECUTE 'SELECT count(*) FROM ( SELECT DISTINCT topogeo_id FROM '
       || quote_ident(atopology)
       || '.relation r WHERE r.layer_id = ' || rec.layer_id
       || ' ) foo ' INTO STRICT n;

     ret = ret || n || ' topogeoms' || E'\n';

   EXCEPTION WHEN UNDEFINED_TABLE OR UNDEFINED_COLUMN THEN
     n := NULL;
     ret = ret || 'X topogeoms' || E'\n';
   END;

     IF rec.level > 0 THEN
       ret = ret || ' Hierarchy level ' || rec.level
                 || ', child layer ' || rec.child_id || E'\n';
     END IF;

     ret = ret || ' Deploy: ';
     IF rec.feature_column != '' THEN
       ret = ret || quote_ident(rec.schema_name) || '.'
                 || quote_ident(rec.table_name) || '.'
                 || quote_ident(rec.feature_column);

       IF n > 0 THEN
         sql := 'SELECT count(*) FROM ( SELECT topogeo_id FROM '
           || quote_ident(atopology)
           || '.relation r WHERE r.layer_id = ' || rec.layer_id
           || ' EXCEPT SELECT DISTINCT id('
           || quote_ident(rec.feature_column) || ') FROM '
           || quote_ident(rec.schema_name) || '.'
           || quote_ident(rec.table_name) || ') as foo';
         BEGIN
           EXECUTE sql INTO STRICT missing;
           IF missing > 0 THEN
             ret = ret || ' (' || missing || ' missing topogeoms)';
           END IF;
         EXCEPTION
           WHEN UNDEFINED_TABLE THEN
             ret = ret || ' ( unexistent table )';
           WHEN UNDEFINED_COLUMN THEN
             ret = ret || ' ( unexistent column )';
         END;
       END IF;
       ret = ret || E'\n';

     ELSE
       ret = ret || E'NONE (detached)\n';
     END IF;

 END LOOP; -- }

 -- print information about unregistered layers containing topogeoms
 IF tgcount > 0 THEN -- {

   sql := 'SELECT layer_id FROM '
       || quote_ident(atopology) || '.relation EXCEPT SELECT layer_id'
       || ' FROM topology.layer WHERE topology_id = $1 ORDER BY layer_id';
   --RAISE DEBUG '%', sql;
   FOR rec IN  EXECUTE sql USING var_topology_id
   LOOP -- {
     ret = ret || 'Layer ' || rec.layer_id::text || ', UNREGISTERED, ';

     EXECUTE 'SELECT count(*) FROM ( SELECT DISTINCT topogeo_id FROM '
       || quote_ident(atopology)
       || '.relation r WHERE r.layer_id = ' || rec.layer_id
       || ' ) foo ' INTO STRICT n;

     ret = ret || n || ' topogeoms' || E'\n';

   END LOOP; -- }

 END IF; -- }

 RETURN ret;
END
$span$
LANGUAGE 'plpgsql'
STABLE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER
COST 100;

COMMENT ON FUNCTION topology.topologysummary(atopology varchar)
IS 'args: topology_schema_name - Takes a topology name and provides summary totals of types of objects in topology';

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