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

Function: droptopogeometrycolumn

Schema

topology

Owner

albenard

Descriptions

args: schema_name, table_name, column_name - Drops the topogeometry column from the table named table_name in schema schema_name and unregisters the columns from topology.layer table.

Options

Definition

CREATE OR REPLACE FUNCTION topology.droptopogeometrycolumn (
 schema varchar,
 tbl varchar,
 col varchar
)
RETURNS text AS
$span$
DECLARE
 rec RECORD;
 lyrinfo RECORD;
 ok BOOL;
 result text;
BEGIN

       -- Get layer and topology info
 ok = false;
 FOR rec IN EXECUTE 'SELECT t.name as toponame, l.* FROM '
      'topology.topology t, topology.layer l '
      ' WHERE l.topology_id = t.id'
      ' AND l.schema_name = ' || quote_literal(schema)
   || ' AND l.table_name = ' || quote_literal(tbl)
   || ' AND l.feature_column = ' || quote_literal(col)
 LOOP
   ok = true;
   lyrinfo = rec;
 END LOOP;

 -- Layer not found
 IF NOT ok THEN
   RAISE EXCEPTION 'No layer registered on %.%.%',
     schema,tbl,col;
 END IF;
   
 -- Clean up the topology schema
 BEGIN
   -- Cleanup the relation table
   EXECUTE 'DELETE FROM ' || quote_ident(lyrinfo.toponame)
     || '.relation '
        ' WHERE '
        'layer_id = ' || lyrinfo.layer_id;

   -- Drop the sequence for topogeoms in this layer
   EXECUTE 'DROP SEQUENCE ' || quote_ident(lyrinfo.toponame)
     || '.topogeo_s_' || lyrinfo.layer_id;
 EXCEPTION
   WHEN UNDEFINED_TABLE THEN
     RAISE NOTICE '%', SQLERRM;
   WHEN OTHERS THEN
     RAISE EXCEPTION 'Got % (%)', SQLERRM, SQLSTATE;
 END;

 ok = false;
 FOR rec IN SELECT * FROM pg_namespace n, pg_class c, pg_attribute a
   WHERE text(n.nspname) = schema
   AND c.relnamespace = n.oid
   AND text(c.relname) = tbl
   AND a.attrelid = c.oid
   AND text(a.attname) = col
 LOOP
   ok = true;
   EXIT;
 END LOOP;


 IF ok THEN
   -- Set feature column to NULL to bypass referential integrity
   -- checks
   EXECUTE 'UPDATE ' || quote_ident(schema) || '.'
     || quote_ident(tbl)
     || ' SET ' || quote_ident(col)
     || ' = NULL';
 END IF;

 -- Delete the layer record
 EXECUTE 'DELETE FROM topology.layer '
      ' WHERE topology_id = ' || lyrinfo.topology_id
   || ' AND layer_id = ' || lyrinfo.layer_id;

 IF ok THEN
   -- Drop the layer column
   EXECUTE 'ALTER TABLE ' || quote_ident(schema) || '.'
     || quote_ident(tbl)
     || ' DROP ' || quote_ident(col)
     || ' cascade';
 END IF;

 result = 'Layer ' || lyrinfo.layer_id || ' ('
   || schema || '.' || tbl || '.' || col
   || ') dropped';

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

COMMENT ON FUNCTION topology.droptopogeometrycolumn(schema varchar, tbl varchar, col varchar)
IS 'args: schema_name, table_name, column_name - Drops the topogeometry column from the table named table_name in schema schema_name and unregisters the columns from topology.layer table.';

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