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

Function: addtopogeometrycolumn

Schema

topology

Owner

albenard

Descriptions

args: topology_name, schema_name, table_name, column_name, feature_type, child_layer - Adds a topogeometry column to an existing table, registers this new column as a layer in topology.layer and returns the new layer_id.

Options

Definition

CREATE OR REPLACE FUNCTION topology.addtopogeometrycolumn (
 toponame varchar,
 schema varchar,
 tbl varchar,
 col varchar,
 ltype varchar,
 child integer
)
RETURNS integer AS
$span$
DECLARE
 intltype integer;
 newlevel integer;
 topoid integer;
 rec RECORD;
 newlayer_id integer;
 query text;
BEGIN

 -- Get topology id
 SELECT id INTO topoid
   FROM topology.topology WHERE name = toponame;

 IF NOT FOUND THEN
   RAISE EXCEPTION 'Topology % does not exist', quote_literal(toponame);
 END IF;

 IF ltype ILIKE '%POINT%' OR ltype ILIKE 'PUNTAL' THEN
   intltype = 1;
 ELSIF ltype ILIKE '%LINE%' OR ltype ILIKE 'LINEAL' THEN
   intltype = 2;
 ELSIF ltype ILIKE '%POLYGON%' OR ltype ILIKE 'AREAL' THEN
   intltype = 3;
 ELSIF ltype ILIKE '%COLLECTION%' OR ltype ILIKE 'GEOMETRY' THEN
   intltype = 4;
 ELSE
   RAISE EXCEPTION 'Layer type must be one of POINT,LINE,POLYGON,COLLECTION';
 END IF;

 --
 -- Add new TopoGeometry column in schema.table
 --
 EXECUTE 'ALTER TABLE ' || quote_ident(schema)
   || '.' || quote_ident(tbl)
   || ' ADD COLUMN ' || quote_ident(col)
   || ' topology.TopoGeometry;';


 --
 -- See if child id exists and extract its level
 --
 IF child IS NOT NULL THEN
   SELECT level + 1 FROM topology.layer
     WHERE layer_id = child
       AND topology_id = topoid
     INTO newlevel;
   IF newlevel IS NULL THEN
     RAISE EXCEPTION 'Child layer % does not exist in topology "%"', child, toponame;
   END IF;
 END IF;

 --
 -- Get new layer id from sequence
 --
 EXECUTE 'SELECT nextval(' ||
   quote_literal(
     quote_ident(toponame) || '.layer_id_seq'
   ) || ')' INTO STRICT newlayer_id;

 EXECUTE 'INSERT INTO '
      'topology.layer(topology_id, '
      'layer_id, level, child_id, schema_name, '
      'table_name, feature_column, feature_type) '
      'VALUES ('
   || topoid || ','
   || newlayer_id || ',' || COALESCE(newlevel, 0) || ','
   || COALESCE(child::text, 'NULL') || ','
   || quote_literal(schema) || ','
   || quote_literal(tbl) || ','
   || quote_literal(col) || ','
   || intltype || ');';


 --
 -- Create a sequence for TopoGeometries in this new layer
 --
 EXECUTE 'CREATE SEQUENCE ' || quote_ident(toponame)
   || '.topogeo_s_' || newlayer_id;

 --
 -- Add constraints on TopoGeom column
 --
 EXECUTE 'ALTER TABLE ' || quote_ident(schema)
   || '.' || quote_ident(tbl)
   || ' ADD CONSTRAINT "check_topogeom_' || col || '" CHECK ('
      'topology_id(' || quote_ident(col) || ') = ' || topoid
   || ' AND '
      'layer_id(' || quote_ident(col) || ') = ' || newlayer_id
   || ' AND '
      'type(' || quote_ident(col) || ') = ' || intltype
   || ');';

 --
 -- Add dependency of the feature column on the topology schema
 --
 query = 'INSERT INTO pg_catalog.pg_depend SELECT '
      'fcat.oid, fobj.oid, fsub.attnum, tcat.oid, '
      'tobj.oid, 0, ''n'' '
      'FROM pg_class fcat, pg_namespace fnsp, '
      ' pg_class fobj, pg_attribute fsub, '
      ' pg_class tcat, pg_namespace tobj '
      ' WHERE fcat.relname = ''pg_class'' '
      ' AND fnsp.nspname = ' || quote_literal(schema)
   || ' AND fobj.relnamespace = fnsp.oid '
      ' AND fobj.relname = ' || quote_literal(tbl)
   || ' AND fsub.attrelid = fobj.oid '
      ' AND fsub.attname = ' || quote_literal(col)
   || ' AND tcat.relname = ''pg_namespace'' '
      ' AND tobj.nspname = ' || quote_literal(toponame);

--
-- The only reason to add this dependency is to avoid
-- simple drop of a feature column. Still, drop cascade
-- will remove both the feature column and the sequence
-- corrupting the topology anyway ...
--

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

COMMENT ON FUNCTION topology.addtopogeometrycolumn(toponame varchar, schema varchar, tbl varchar, col varchar, ltype varchar, child integer)
IS 'args: topology_name, schema_name, table_name, column_name, feature_type, child_layer - Adds a topogeometry column to an existing table, registers this new column as a layer in topology.layer and returns the new layer_id.';

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