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

Function: populate_geometry_columns

Schema

public

Owner

postgres

Descriptions

args: relation_oid, 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 (
 tbl_oid oid,
 use_typmod boolean = true
)
RETURNS integer AS
$span$
DECLARE
gcs         RECORD;
gc          RECORD;
gc_old      RECORD;
gsrid       integer;
gndims      integer;
gtype       text;
query       text;
gc_is_valid boolean;
inserted    integer;
constraint_successful boolean := false;

BEGIN
inserted := 0;

-- Iterate through all geometry columns in this table
FOR gcs IN
SELECT n.nspname, c.relname, a.attname
FROM pg_class c,
pg_attribute a,
pg_type t,
pg_namespace n
WHERE c.relkind IN('r')
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.oid = tbl_oid
LOOP

       RAISE DEBUG 'Processing column %.%.%', gcs.nspname, gcs.relname, gcs.attname;
   
       gc_is_valid := true;
       -- Find the srid, coord_dimension, and type of current geometry
       -- in geometry_columns -- which is now a view
       
       SELECT type, srid, coord_dimension INTO gc_old
           FROM geometry_columns
           WHERE f_table_schema = gcs.nspname AND f_table_name = gcs.relname AND f_geometry_column = gcs.attname;
           
       IF upper(gc_old.type) = 'GEOMETRY' THEN
       -- This is an unconstrained geometry we need to do something
       -- We need to figure out what to set the type by inspecting the data
           EXECUTE 'SELECT st_srid(' || quote_ident(gcs.attname) || ') As srid, GeometryType(' || quote_ident(gcs.attname) || ') As type, ST_NDims(' || quote_ident(gcs.attname) || ') As dims ' ||
                    ' FROM ONLY ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) ||
                    ' WHERE ' || quote_ident(gcs.attname) || ' IS NOT NULL LIMIT 1;'
               INTO gc;
           IF gc IS NULL THEN -- there is no data so we can not determine geometry type
            RAISE WARNING 'No data in table %.%, so no information to determine geometry type and srid', gcs.nspname, gcs.relname;
            RETURN 0;
           END IF;
           gsrid := gc.srid; gtype := gc.type; gndims := gc.dims;
           
           IF use_typmod THEN
               BEGIN
                   EXECUTE 'ALTER TABLE ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || ' ALTER COLUMN ' || quote_ident(gcs.attname) ||
                       ' TYPE geometry(' || postgis_type_name(gtype, gndims, true) || ', ' || gsrid::text  || ') ';
                   inserted := inserted + 1;
               EXCEPTION
                       WHEN invalid_parameter_value OR feature_not_supported THEN
                       RAISE WARNING 'Could not convert ''%'' in ''%.%'' to use typmod with srid %, type %: %', quote_ident(gcs.attname), quote_ident(gcs.nspname), quote_ident(gcs.relname), gsrid, postgis_type_name(gtype, gndims, true), SQLERRM;
                           gc_is_valid := false;
               END;
               
           ELSE
               -- Try to apply srid check to column
            constraint_successful = false;
               IF (gsrid > 0 AND postgis_constraint_srid(gcs.nspname, gcs.relname,gcs.attname) IS NULL ) THEN
                   BEGIN
                       EXECUTE 'ALTER TABLE ONLY ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) ||
                                ' ADD CONSTRAINT ' || quote_ident('enforce_srid_' || gcs.attname) ||
                                ' CHECK (st_srid(' || quote_ident(gcs.attname) || ') = ' || gsrid || ')';
                       constraint_successful := true;
                   EXCEPTION
                       WHEN check_violation THEN
                           RAISE WARNING 'Not inserting ''%'' in ''%.%'' into geometry_columns: could not apply constraint CHECK (st_srid(%) = %)', quote_ident(gcs.attname), quote_ident(gcs.nspname), quote_ident(gcs.relname), quote_ident(gcs.attname), gsrid;
                           gc_is_valid := false;
                   END;
               END IF;
               
               -- Try to apply ndims check to column
               IF (gndims IS NOT NULL AND postgis_constraint_dims(gcs.nspname, gcs.relname,gcs.attname) IS NULL ) THEN
                   BEGIN
                       EXECUTE 'ALTER TABLE ONLY ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || '
                                ADD CONSTRAINT ' || quote_ident('enforce_dims_' || gcs.attname) || '
                                CHECK (st_ndims(' || quote_ident(gcs.attname) || ') = '||gndims||')';
                       constraint_successful := true;
                   EXCEPTION
                       WHEN check_violation THEN
                           RAISE WARNING 'Not inserting ''%'' in ''%.%'' into geometry_columns: could not apply constraint CHECK (st_ndims(%) = %)', quote_ident(gcs.attname), quote_ident(gcs.nspname), quote_ident(gcs.relname), quote_ident(gcs.attname), gndims;
                           gc_is_valid := false;
                   END;
               END IF;
   
               -- Try to apply geometrytype check to column
               IF (gtype IS NOT NULL AND postgis_constraint_type(gcs.nspname, gcs.relname,gcs.attname) IS NULL ) THEN
                   BEGIN
                       EXECUTE 'ALTER TABLE ONLY ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || '
                       ADD CONSTRAINT ' || quote_ident('enforce_geotype_' || gcs.attname) || '
                       CHECK (geometrytype(' || quote_ident(gcs.attname) || ') = ' || quote_literal(gtype) || ')';
                       constraint_successful := true;
                   EXCEPTION
                       WHEN check_violation THEN
                           -- No geometry check can be applied. This column contains a number of geometry types.
                           RAISE WARNING 'Could not add geometry type check (%) to table column: %.%.%', gtype, quote_ident(gcs.nspname),quote_ident(gcs.relname),quote_ident(gcs.attname);
                   END;
               END IF;
                --only count if we were successful in applying at least one constraint
               IF constraint_successful THEN
                inserted := inserted + 1;
               END IF;
           END IF;        
   END IF;

END LOOP;

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

COMMENT ON FUNCTION public.populate_geometry_columns(tbl_oid oid, use_typmod boolean)
IS 'args: relation_oid, 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 13/03/2014 13:23