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

Function: copytopology

Schema

topology

Owner

albenard

Descriptions

args: existing_topology_name, new_name - Makes a copy of a topology structure (nodes, edges, faces, layers and TopoGeometries).

Options

Definition

CREATE OR REPLACE FUNCTION topology.copytopology (
 atopology varchar,
 newtopo varchar
)
RETURNS integer AS
$span$
DECLARE
 rec RECORD;
 rec2 RECORD;
 oldtopo_id integer;
 newtopo_id integer;
 n int4;
 ret text;
BEGIN

 SELECT * FROM topology.topology where name = atopology
 INTO strict rec;
 oldtopo_id = rec.id;
 -- TODO: more gracefully handle unexistent topology

 SELECT topology.CreateTopology(newtopo, rec.SRID, rec.precision, rec.hasZ)
 INTO strict newtopo_id;

 -- Copy faces
 EXECUTE 'INSERT INTO ' || quote_ident(newtopo)
   || '.face SELECT * FROM ' || quote_ident(atopology)
   || '.face WHERE face_id != 0';
 -- Update faces sequence
 EXECUTE 'SELECT setval(' || quote_literal(
     quote_ident(newtopo) || '.face_face_id_seq'
   ) || ', (SELECT last_value FROM '
   || quote_ident(atopology) || '.face_face_id_seq))';

 -- Copy nodes
 EXECUTE 'INSERT INTO ' || quote_ident(newtopo)
   || '.node SELECT * FROM ' || quote_ident(atopology)
   || '.node';
 -- Update node sequence
 EXECUTE 'SELECT setval(' || quote_literal(
     quote_ident(newtopo) || '.node_node_id_seq'
   ) || ', (SELECT last_value FROM '
   || quote_ident(atopology) || '.node_node_id_seq))';

 -- Copy edges
 EXECUTE 'INSERT INTO ' || quote_ident(newtopo)
   || '.edge_data SELECT * FROM ' || quote_ident(atopology)
   || '.edge_data';
 -- Update edge sequence
 EXECUTE 'SELECT setval(' || quote_literal(
     quote_ident(newtopo) || '.edge_data_edge_id_seq'
   ) || ', (SELECT last_value FROM '
   || quote_ident(atopology) || '.edge_data_edge_id_seq))';

 -- Copy layers and their TopoGeometry sequences
 FOR rec IN SELECT * FROM topology.layer WHERE topology_id = oldtopo_id
 LOOP
   INSERT INTO topology.layer (topology_id, layer_id, feature_type,
     level, child_id, schema_name, table_name, feature_column)
     VALUES (newtopo_id, rec.layer_id, rec.feature_type,
             rec.level, rec.child_id, newtopo,
             'LAYER' ||  rec.layer_id, '');
   -- Create layer's TopoGeometry sequences
   EXECUTE 'SELECT last_value FROM '
     || quote_ident(atopology) || '.topogeo_s_' || rec.layer_id
     INTO STRICT n;
   EXECUTE 'CREATE SEQUENCE ' || quote_ident(newtopo)
     || '.topogeo_s_' || rec.layer_id;
   EXECUTE 'SELECT setval(' || quote_literal(
     quote_ident(newtopo) || '.topogeo_s_' || rec.layer_id
     ) || ', ' || n || ')';
 END LOOP;

 -- Copy TopoGeometry definitions
 EXECUTE 'INSERT INTO ' || quote_ident(newtopo)
   || '.relation SELECT * FROM ' || quote_ident(atopology)
   || '.relation';

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

COMMENT ON FUNCTION topology.copytopology(atopology varchar, newtopo varchar)
IS 'args: existing_topology_name, new_name - Makes a copy of a topology structure (nodes, edges, faces, layers and TopoGeometries).';

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