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; |
