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

Function: populate_geometry_columns

Schema

public

Owner

albenard

Descriptions

args: use_typmod=true - Ensures geometry columns are defined with type modifiers or have appropriate spatial constraints This ensures they will be registered correctly in geometry_columns view. By default will convert all geometry columns with no type modifier to ones with type modifiers. To get old behavior set use_typmod=false

Options

Definition

CREATE OR REPLACE FUNCTION public.populate_geometry_columns (
 use_typmod boolean = true
)
RETURNS text AS
$span$
DECLARE
inserted    integer;
oldcount    integer;
probed      integer;
stale       integer;
gcs         RECORD;
gc          RECORD;
gsrid       integer;
gndims      integer;
gtype       text;
query       text;
gc_is_valid boolean;

BEGIN
SELECT count(*) INTO oldcount FROM geometry_columns;
inserted := 0;

-- Count the number of geometry columns in all tables and views
SELECT count(DISTINCT c.oid) INTO probed
FROM pg_class c,
pg_attribute a,
pg_type t,
pg_namespace n
WHERE c.relkind IN('r','v','f')
AND t.typname = 'geometry'
AND a.attisdropped = false
AND a.atttypid = t.oid
AND a.attrelid = c.oid
AND c.relnamespace = n.oid
AND n.nspname NOT ILIKE 'pg_temp%' AND c.relname != 'raster_columns' ;

-- Iterate through all non-dropped geometry columns
RAISE DEBUG 'Processing Tables.....';

FOR gcs IN
SELECT DISTINCT ON (c.oid) c.oid, n.nspname, c.relname
FROM pg_class c,
pg_attribute a,
pg_type t,
pg_namespace n
WHERE c.relkind IN( 'r', 'f')
AND t.typname = 'geometry'
AND a.attisdropped = false
AND a.atttypid = t.oid
AND a.attrelid = c.oid
AND c.relnamespace = n.oid
AND n.nspname NOT ILIKE 'pg_temp%' AND c.relname != 'raster_columns'
LOOP

inserted := inserted + populate_geometry_columns(gcs.oid, use_typmod);
END LOOP;

IF oldcount > inserted THEN
   stale = oldcount-inserted;
ELSE
   stale = 0;
END IF;

RETURN 'probed:' ||probed|| ' inserted:'||inserted;
END
$span$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

COMMENT ON FUNCTION public.populate_geometry_columns(use_typmod boolean)
IS 'args: use_typmod=true - Ensures geometry columns are defined with type modifiers or have appropriate spatial constraints This ensures they will be registered correctly in geometry_columns view. By default will convert all geometry columns with no type modifier to ones with type modifiers. To get old behavior set use_typmod=false';

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