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

Function: gettopogeomelements

Schema

topology

Owner

albenard

Descriptions

args: toponame, layer_id, tg_id - Returns a set of topoelement objects containing the topological element_id,element_type of the given TopoGeometry (primitive elements)

Options

Definition

CREATE OR REPLACE FUNCTION topology.gettopogeomelements (
 toponame varchar,
 layerid integer,
 tgid integer
)
RETURNS SETOF topology.topoelement AS
$span$
DECLARE
 ret topology.TopoElement;
 rec RECORD;
 rec2 RECORD;
 query text;
 query2 text;
 lyr RECORD;
 ok bool;
 topoid INTEGER;
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;

 -- Get layer info
 ok = false;
 FOR rec IN EXECUTE 'SELECT * FROM topology.layer '
      ' WHERE layer_id = $1 AND topology_id = $2'
      USING layerid, topoid
 LOOP
   lyr = rec;
   ok = true;
 END LOOP;

 IF NOT ok THEN
   RAISE EXCEPTION 'Layer % does not exist', layerid;
 END IF;


 query = 'SELECT abs(element_id) as element_id, element_type FROM '
   || quote_ident(toponame) || '.relation WHERE '
      ' layer_id = ' || layerid
   || ' AND topogeo_id = ' || quote_literal(tgid)
   || ' ORDER BY element_type, element_id';

 --RAISE NOTICE 'Query: %', query;

 FOR rec IN EXECUTE query
 LOOP
   IF lyr.level > 0 THEN
     query2 = 'SELECT * from topology.GetTopoGeomElements('
       || quote_literal(toponame) || ','
       || rec.element_type
       || ','
       || rec.element_id
       || ') as ret;';
     --RAISE NOTICE 'Query2: %', query2;
     FOR rec2 IN EXECUTE query2
     LOOP
       RETURN NEXT rec2.ret;
     END LOOP;
   ELSE
     ret = '{' || rec.element_id || ',' || rec.element_type || '}';
     RETURN NEXT ret;
   END IF;
 
 END LOOP;

 RETURN;
END;
$span$
LANGUAGE 'plpgsql'
STABLE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

COMMENT ON FUNCTION topology.gettopogeomelements(toponame varchar, layerid integer, tgid integer)
IS 'args: toponame, layer_id, tg_id - Returns a set of topoelement objects containing the topological element_id,element_type of the given TopoGeometry (primitive elements)';

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