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

Function: layertrigger

Schema

topology

Owner

albenard

Descriptions

There is no description for function layertrigger

Options

Definition

CREATE OR REPLACE FUNCTION topology.layertrigger (
)
RETURNS trigger AS
$span$
DECLARE
 rec RECORD;
 ok BOOL;
 toponame varchar;
 query TEXT;
BEGIN

 --RAISE NOTICE 'LayerTrigger called % % at % level', TG_WHEN, TG_OP, TG_LEVEL;


 IF TG_OP = 'INSERT' THEN
   RAISE EXCEPTION 'LayerTrigger not meant to be called on INSERT';
 ELSIF TG_OP = 'UPDATE' THEN
   RAISE EXCEPTION 'The topology.layer table cannot be updated';
 END IF;


 -- Check for existance of any feature column referencing
 -- this layer
 FOR rec IN SELECT * FROM pg_namespace n, pg_class c, pg_attribute a
   WHERE text(n.nspname) = OLD.schema_name
   AND c.relnamespace = n.oid
   AND text(c.relname) = OLD.table_name
   AND a.attrelid = c.oid
   AND text(a.attname) = OLD.feature_column
 LOOP
   query = 'SELECT * '
        ' FROM ' || quote_ident(OLD.schema_name)
     || '.' || quote_ident(OLD.table_name)
     || ' WHERE layer_id('
     || quote_ident(OLD.feature_column)||') '
        '=' || OLD.layer_id
     || ' LIMIT 1';
   --RAISE NOTICE '%', query;
   FOR rec IN EXECUTE query
   LOOP
     RAISE NOTICE 'A feature referencing layer % of topology % still exists in %.%.%', OLD.layer_id, OLD.topology_id, OLD.schema_name, OLD.table_name, OLD.feature_column;
     RETURN NULL;
   END LOOP;
 END LOOP;


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

 IF toponame IS NULL THEN
   RAISE NOTICE 'Could not find name of topology with id %',
     OLD.layer_id;
 END IF;

 -- Check if any record in the relation table references this layer
 FOR rec IN SELECT c.oid FROM pg_namespace n, pg_class c
   WHERE text(n.nspname) = toponame AND c.relnamespace = n.oid
         AND c.relname = 'relation'
 LOOP
   query = 'SELECT * '
        ' FROM ' || quote_ident(toponame)
     || '.relation '
        ' WHERE layer_id = '|| OLD.layer_id
     || ' LIMIT 1';
   --RAISE NOTICE '%', query;
   FOR rec IN EXECUTE query
   LOOP
     RAISE NOTICE 'A record in %.relation still references layer %', toponame, OLD.layer_id;
     RETURN NULL;
   END LOOP;
 END LOOP;

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

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