Schema
Owner
postgres
Descriptions
args: topology_schema_name - Takes a topology name and provides summary totals of types of objects in topology
Options
Option | Value |
---|---|
Returns |
text |
Language |
|
Parameters |
atopology varchar |
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 07/12/2018 13:23 |