1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140
| -- Function: addgeometrycolumn(character varying, character varying, character varying, character varying, integer, character varying, integer)
-- DROP FUNCTION addgeometrycolumn(character varying, character varying, character varying, character varying, integer, character varying, integer);
CREATE OR REPLACE FUNCTION addgeometrycolumn(character varying, character varying, character varying, character varying, integer, character varying, integer)
RETURNS text AS
'
DECLARE
catalog_name alias for $1;
schema_name alias for $2;
table_name alias for $3;
column_name alias for $4;
new_srid alias for $5;
new_type alias for $6;
new_dim alias for $7;
rec RECORD;
schema_ok bool;
real_schema name;
BEGIN
IF ( not ( (new_type =''GEOMETRY'') or
(new_type =''GEOMETRYCOLLECTION'') or
(new_type =''POINT'') or
(new_type =''MULTIPOINT'') or
(new_type =''POLYGON'') or
(new_type =''MULTIPOLYGON'') or
(new_type =''LINESTRING'') or
(new_type =''MULTILINESTRING'') or
(new_type =''GEOMETRYCOLLECTIONM'') or
(new_type =''POINTM'') or
(new_type =''MULTIPOINTM'') or
(new_type =''POLYGONM'') or
(new_type =''MULTIPOLYGONM'') or
(new_type =''LINESTRINGM'') or
(new_type =''MULTILINESTRINGM'')) )
THEN
RAISE EXCEPTION ''Invalid type name - valid ones are:
GEOMETRY, GEOMETRYCOLLECTION, POINT,
MULTIPOINT, POLYGON, MULTIPOLYGON,
LINESTRING, MULTILINESTRING,
GEOMETRYCOLLECTIONM, POINTM,
MULTIPOINTM, POLYGONM, MULTIPOLYGONM,
LINESTRINGM, or MULTILINESTRINGM '';
return ''fail'';
END IF;
IF ( (new_dim >4) or (new_dim <0) ) 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;
IF ( schema_name != '''' ) THEN
schema_ok = ''f'';
FOR rec IN SELECT nspname FROM pg_namespace WHERE text(nspname) = schema_name LOOP
schema_ok := ''t'';
END LOOP;
if ( schema_ok <> ''t'' ) THEN
RAISE NOTICE ''Invalid schema name - using current_schema()'';
SELECT current_schema() into real_schema;
ELSE
real_schema = schema_name;
END IF;
ELSE
SELECT current_schema() into real_schema;
END IF;
-- Add geometry column
EXECUTE ''ALTER TABLE '' ||
quote_ident(real_schema) || ''.'' || quote_ident(table_name)
|| '' ADD COLUMN '' || quote_ident(column_name) ||
'' geometry '';
-- Delete stale record in geometry_column (if any)
EXECUTE ''DELETE FROM geometry_columns WHERE
f_table_catalog = '' || quote_literal('''') ||
'' AND f_table_schema = '' ||
quote_literal(real_schema) ||
'' AND f_table_name = '' || quote_literal(table_name) ||
'' AND f_geometry_column = '' || quote_literal(column_name);
-- Add record in geometry_column
EXECUTE ''INSERT INTO geometry_columns VALUES ('' ||
quote_literal('''') || '','' ||
quote_literal(real_schema) || '','' ||
quote_literal(table_name) || '','' ||
quote_literal(column_name) || '','' ||
new_dim || '','' || new_srid || '','' ||
quote_literal(new_type) || '')'';
-- Add table checks
EXECUTE ''ALTER TABLE '' ||
quote_ident(real_schema) || ''.'' || quote_ident(table_name)
|| '' ADD CONSTRAINT ''
|| quote_ident(''enforce_srid_'' || column_name)
|| '' CHECK (SRID('' || quote_ident(column_name) ||
'') = '' || new_srid || '')'' ;
EXECUTE ''ALTER TABLE '' ||
quote_ident(real_schema) || ''.'' || quote_ident(table_name)
|| '' ADD CONSTRAINT ''
|| quote_ident(''enforce_dims_'' || column_name)
|| '' CHECK (ndims('' || quote_ident(column_name) ||
'') = '' || new_dim || '')'' ;
IF (not(new_type = ''GEOMETRY'')) THEN
EXECUTE ''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)'';
END IF;
return
real_schema || ''.'' ||
table_name || ''.'' || column_name ||
'' SRID:'' || new_srid ||
'' TYPE:'' || new_type ||
'' DIMS:'' || new_dim || chr(10) || '' '';
END;
'
LANGUAGE 'plpgsql' VOLATILE STRICT; |
Partager