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

Function: addgeometrycolumn

Schema

public

Owner

postgres

Descriptions

args: catalog_name, schema_name, table_name, column_name, srid, type, dimension, use_typmod=true - Adds a geometry column to an existing table of attributes. By default uses type modifier to define rather than constraints. Pass in false for use_typmod to get old check constraint based behavior

Options

Definition

CREATE OR REPLACE FUNCTION public.addgeometrycolumn (
 catalog_name varchar,
 schema_name varchar,
 table_name varchar,
 column_name varchar,
 new_srid_in integer,
 new_type varchar,
 new_dim integer,
 use_typmod boolean = true
)
RETURNS text AS
$span$
DECLARE
rec RECORD;
sr varchar;
real_schema name;
sql text;
new_srid integer;

BEGIN

-- Verify geometry type
IF (postgis_type_name(new_type,new_dim) IS NULL )
THEN
RAISE EXCEPTION 'Invalid type name "%(%)" - valid ones are:
POINT, MULTIPOINT,
LINESTRING, MULTILINESTRING,
POLYGON, MULTIPOLYGON,
CIRCULARSTRING, COMPOUNDCURVE, MULTICURVE,
CURVEPOLYGON, MULTISURFACE,
GEOMETRY, GEOMETRYCOLLECTION,
POINTM, MULTIPOINTM,
LINESTRINGM, MULTILINESTRINGM,
POLYGONM, MULTIPOLYGONM,
CIRCULARSTRINGM, COMPOUNDCURVEM, MULTICURVEM
CURVEPOLYGONM, MULTISURFACEM, TRIANGLE, TRIANGLEM,
POLYHEDRALSURFACE, POLYHEDRALSURFACEM, TIN, TINM
or GEOMETRYCOLLECTIONM', new_type, new_dim;
RETURN 'fail';
END IF;


-- Verify dimension
IF ( (new_dim >4) OR (new_dim <2) ) THEN
RAISE EXCEPTION 'invalid dimension';
RETURN 'fail';
END IF;

IF ( (new_type LIKE '%M') AND (new_dim!=3) ) THEN
RAISE EXCEPTION 'TypeM needs 3 dimensions';
RETURN 'fail';
END IF;


-- Verify SRID
IF ( new_srid_in > 0 ) THEN
IF new_srid_in > 998999 THEN
RAISE EXCEPTION 'AddGeometryColumn() - SRID must be <= %', 998999;
END IF;
new_srid := new_srid_in;
SELECT SRID INTO sr FROM spatial_ref_sys WHERE SRID = new_srid;
IF NOT FOUND THEN
RAISE EXCEPTION 'AddGeometryColumn() - invalid SRID';
RETURN 'fail';
END IF;
ELSE
new_srid := public.ST_SRID('POINT EMPTY'::public.geometry);
IF ( new_srid_in != new_srid ) THEN
RAISE NOTICE 'SRID value % converted to the officially unknown SRID value %', new_srid_in, new_srid;
END IF;
END IF;


-- Verify schema
IF ( schema_name IS NOT NULL AND schema_name != '' ) THEN
sql := 'SELECT nspname FROM pg_namespace ' ||
'WHERE text(nspname) = ' || quote_literal(schema_name) ||
'LIMIT 1';
RAISE DEBUG '%', sql;
EXECUTE sql INTO real_schema;

IF ( real_schema IS NULL ) THEN
RAISE EXCEPTION 'Schema % is not a valid schemaname', quote_literal(schema_name);
RETURN 'fail';
END IF;
END IF;

IF ( real_schema IS NULL ) THEN
RAISE DEBUG 'Detecting schema';
sql := 'SELECT n.nspname AS schemaname ' ||
'FROM pg_catalog.pg_class c ' ||
 'JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace ' ||
'WHERE c.relkind = ' || quote_literal('r') ||
' AND n.nspname NOT IN (' || quote_literal('pg_catalog') || ', ' || quote_literal('pg_toast') || ')' ||
' AND pg_catalog.pg_table_is_visible(c.oid)' ||
' AND c.relname = ' || quote_literal(table_name);
RAISE DEBUG '%', sql;
EXECUTE sql INTO real_schema;

IF ( real_schema IS NULL ) THEN
RAISE EXCEPTION 'Table % does not occur in the search_path', quote_literal(table_name);
RETURN 'fail';
END IF;
END IF;


-- Add geometry column to table
IF use_typmod THEN
    sql := 'ALTER TABLE ' ||
           quote_ident(real_schema) || '.' || quote_ident(table_name)
           || ' ADD COLUMN ' || quote_ident(column_name) ||
           ' geometry(' || postgis_type_name(new_type, new_dim) || ', ' || new_srid::text || ')';
       RAISE DEBUG '%', sql;
ELSE
       sql := 'ALTER TABLE ' ||
           quote_ident(real_schema) || '.' || quote_ident(table_name)
           || ' ADD COLUMN ' || quote_ident(column_name) ||
           ' geometry ';
       RAISE DEBUG '%', sql;
   END IF;
EXECUTE sql;

IF NOT use_typmod THEN
       -- Add table CHECKs
       sql := 'ALTER TABLE ' ||
           quote_ident(real_schema) || '.' || quote_ident(table_name)
           || ' ADD CONSTRAINT '
           || quote_ident('enforce_srid_' || column_name)
           || ' CHECK (st_srid(' || quote_ident(column_name) ||
           ') = ' || new_srid::text || ')' ;
       RAISE DEBUG '%', sql;
       EXECUTE sql;

       sql := 'ALTER TABLE ' ||
           quote_ident(real_schema) || '.' || quote_ident(table_name)
           || ' ADD CONSTRAINT '
           || quote_ident('enforce_dims_' || column_name)
           || ' CHECK (st_ndims(' || quote_ident(column_name) ||
           ') = ' || new_dim::text || ')' ;
       RAISE DEBUG '%', sql;
       EXECUTE sql;

       IF ( NOT (new_type = 'GEOMETRY')) THEN
           sql := 'ALTER TABLE ' ||
               quote_ident(real_schema) || '.' || quote_ident(table_name) || ' ADD CONSTRAINT ' ||
               quote_ident('enforce_geotype_' || column_name) ||
               ' CHECK (GeometryType(' ||
               quote_ident(column_name) || ')=' ||
               quote_literal(new_type) || ' OR (' ||
               quote_ident(column_name) || ') is null)';
           RAISE DEBUG '%', sql;
           EXECUTE sql;
       END IF;
   END IF;

RETURN
real_schema || '.' ||
table_name || '.' || column_name ||
' SRID:' || new_srid::text ||
' TYPE:' || new_type ||
' DIMS:' || new_dim::text || ' ';
END;
$span$
LANGUAGE 'plpgsql'
VOLATILE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER
COST 100;

COMMENT ON FUNCTION public.addgeometrycolumn(catalog_name varchar, schema_name varchar, table_name varchar, column_name varchar, new_srid_in integer, new_type varchar, new_dim integer, use_typmod boolean)
IS 'args: catalog_name, schema_name, table_name, column_name, srid, type, dimension, use_typmod=true - Adds a geometry column to an existing table of attributes. By default uses type modifier to define rather than constraints. Pass in false for use_typmod to get old check constraint based behavior';

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