1SET client_min_messages TO warning; 2\set VERBOSITY terse 3 4CREATE SCHEMA tm; 5 6-- Test construction of typed tables 7 8CREATE TABLE tm.circularstring (id serial, g geometry(circularstring) ); 9CREATE TABLE tm.circularstring0 (id serial, g geometry(circularstring, 0) ); 10CREATE TABLE tm.circularstring4326 (id serial, g geometry(circularstring, 4326) ); 11CREATE TABLE tm.circularstringm (id serial, g geometry(circularstringm) ); 12CREATE TABLE tm.circularstringm0 (id serial, g geometry(circularstringm, 0) ); 13CREATE TABLE tm.circularstringm4326 (id serial, g geometry(circularstringm, 4326) ); 14CREATE TABLE tm.circularstringz (id serial, g geometry(circularstringz) ); 15CREATE TABLE tm.circularstringz0 (id serial, g geometry(circularstringz, 0) ); 16CREATE TABLE tm.circularstringz4326 (id serial, g geometry(circularstringz, 4326) ); 17CREATE TABLE tm.circularstringzm (id serial, g geometry(circularstringzm) ); 18CREATE TABLE tm.circularstringzm0 (id serial, g geometry(circularstringzm, 0) ); 19CREATE TABLE tm.circularstringzm4326 (id serial, g geometry(circularstringzm, 4326) ); 20 21CREATE TABLE tm.compoundcurve (id serial, g geometry(compoundcurve) ); 22CREATE TABLE tm.compoundcurve0 (id serial, g geometry(compoundcurve, 0) ); 23CREATE TABLE tm.compoundcurve4326 (id serial, g geometry(compoundcurve, 4326) ); 24CREATE TABLE tm.compoundcurvem (id serial, g geometry(compoundcurvem) ); 25CREATE TABLE tm.compoundcurvem0 (id serial, g geometry(compoundcurvem, 0) ); 26CREATE TABLE tm.compoundcurvem4326 (id serial, g geometry(compoundcurvem, 4326) ); 27CREATE TABLE tm.compoundcurvez (id serial, g geometry(compoundcurvez) ); 28CREATE TABLE tm.compoundcurvez0 (id serial, g geometry(compoundcurvez, 0) ); 29CREATE TABLE tm.compoundcurvez4326 (id serial, g geometry(compoundcurvez, 4326) ); 30CREATE TABLE tm.compoundcurvezm (id serial, g geometry(compoundcurvezm) ); 31CREATE TABLE tm.compoundcurvezm0 (id serial, g geometry(compoundcurvezm, 0) ); 32CREATE TABLE tm.compoundcurvezm4326 (id serial, g geometry(compoundcurvezm, 4326) ); 33 34CREATE TABLE tm.curvepolygon (id serial, g geometry(curvepolygon) ); 35CREATE TABLE tm.curvepolygon0 (id serial, g geometry(curvepolygon, 0) ); 36CREATE TABLE tm.curvepolygon4326 (id serial, g geometry(curvepolygon, 4326) ); 37CREATE TABLE tm.curvepolygonm (id serial, g geometry(curvepolygonm) ); 38CREATE TABLE tm.curvepolygonm0 (id serial, g geometry(curvepolygonm, 0) ); 39CREATE TABLE tm.curvepolygonm4326 (id serial, g geometry(curvepolygonm, 4326) ); 40CREATE TABLE tm.curvepolygonz (id serial, g geometry(curvepolygonz) ); 41CREATE TABLE tm.curvepolygonz0 (id serial, g geometry(curvepolygonz, 0) ); 42CREATE TABLE tm.curvepolygonz4326 (id serial, g geometry(curvepolygonz, 4326) ); 43CREATE TABLE tm.curvepolygonzm (id serial, g geometry(curvepolygonzm) ); 44CREATE TABLE tm.curvepolygonzm0 (id serial, g geometry(curvepolygonzm, 0) ); 45CREATE TABLE tm.curvepolygonzm4326 (id serial, g geometry(curvepolygonzm, 4326) ); 46 47CREATE TABLE tm.geometry (id serial, g geometry(geometry), gg geography(geometry) ); 48CREATE TABLE tm.geometry0 (id serial, g geometry(geometry, 0), gg geography(geometry, 0) ); 49CREATE TABLE tm.geometry4326 (id serial, g geometry(geometry, 4326), gg geography(geometry, 4326) ); 50CREATE TABLE tm.geometrym (id serial, g geometry(geometrym), gg geography(geometrym) ); 51CREATE TABLE tm.geometrym0 (id serial, g geometry(geometrym, 0), gg geography(geometrym, 0) ); 52CREATE TABLE tm.geometrym4326 (id serial, g geometry(geometrym, 4326), gg geography(geometrym, 4326) ); 53CREATE TABLE tm.geometryz (id serial, g geometry(geometryz), gg geography(geometryz) ); 54CREATE TABLE tm.geometryz0 (id serial, g geometry(geometryz, 0), gg geography(geometryz, 0) ); 55CREATE TABLE tm.geometryz4326 (id serial, g geometry(geometryz, 4326), gg geography(geometryz, 4326) ); 56CREATE TABLE tm.geometryzm (id serial, g geometry(geometryzm), gg geography(geometryzm) ); 57CREATE TABLE tm.geometryzm0 (id serial, g geometry(geometryzm, 0), gg geography(geometryzm, 0) ); 58CREATE TABLE tm.geometryzm4326 (id serial, g geometry(geometryzm, 4326), gg geography(geometryzm, 4326) ); 59 60CREATE TABLE tm.geometrycollection (id serial, g geometry(geometrycollection), gg geography(geometrycollection) ); 61CREATE TABLE tm.geometrycollection0 (id serial, g geometry(geometrycollection, 0), gg geography(geometrycollection, 0) ); 62CREATE TABLE tm.geometrycollection4326 (id serial, g geometry(geometrycollection, 4326), gg geography(geometrycollection, 4326) ); 63CREATE TABLE tm.geometrycollectionm (id serial, g geometry(geometrycollectionm), gg geography(geometrycollectionm) ); 64CREATE TABLE tm.geometrycollectionm0 (id serial, g geometry(geometrycollectionm, 0), gg geography(geometrycollectionm, 0) ); 65CREATE TABLE tm.geometrycollectionm4326 (id serial, g geometry(geometrycollectionm, 4326), gg geography(geometrycollectionm, 4326) ); 66CREATE TABLE tm.geometrycollectionz (id serial, g geometry(geometrycollectionz), gg geography(geometrycollectionz) ); 67CREATE TABLE tm.geometrycollectionz0 (id serial, g geometry(geometrycollectionz, 0), gg geography(geometrycollectionz, 0) ); 68CREATE TABLE tm.geometrycollectionz4326 (id serial, g geometry(geometrycollectionz, 4326), gg geography(geometrycollectionz, 4326) ); 69CREATE TABLE tm.geometrycollectionzm (id serial, g geometry(geometrycollectionzm), gg geography(geometrycollectionzm) ); 70CREATE TABLE tm.geometrycollectionzm0 (id serial, g geometry(geometrycollectionzm, 0), gg geography(geometrycollectionzm, 0) ); 71CREATE TABLE tm.geometrycollectionzm4326 (id serial, g geometry(geometrycollectionzm, 4326), gg geography(geometrycollectionzm, 4326) ); 72 73CREATE TABLE tm.linestring (id serial, g geometry(linestring), gg geography(linestring) ); 74CREATE TABLE tm.linestring0 (id serial, g geometry(linestring, 0), gg geography(linestring, 0) ); 75CREATE TABLE tm.linestring4326 (id serial, g geometry(linestring, 4326), gg geography(linestring, 4326) ); 76CREATE TABLE tm.linestringm (id serial, g geometry(linestringm), gg geography(linestringm) ); 77CREATE TABLE tm.linestringm0 (id serial, g geometry(linestringm, 0), gg geography(linestringm, 0) ); 78CREATE TABLE tm.linestringm4326 (id serial, g geometry(linestringm, 4326), gg geography(linestringm, 4326) ); 79CREATE TABLE tm.linestringz (id serial, g geometry(linestringz), gg geography(linestringz) ); 80CREATE TABLE tm.linestringz0 (id serial, g geometry(linestringz, 0), gg geography(linestringz, 0) ); 81CREATE TABLE tm.linestringz4326 (id serial, g geometry(linestringz, 4326), gg geography(linestringz, 4326) ); 82CREATE TABLE tm.linestringzm (id serial, g geometry(linestringzm), gg geography(linestringzm) ); 83CREATE TABLE tm.linestringzm0 (id serial, g geometry(linestringzm, 0), gg geography(linestringzm, 0) ); 84CREATE TABLE tm.linestringzm4326 (id serial, g geometry(linestringzm, 4326), gg geography(linestringzm, 4326) ); 85 86CREATE TABLE tm.multicurve (id serial, g geometry(multicurve) ); 87CREATE TABLE tm.multicurve0 (id serial, g geometry(multicurve, 0) ); 88CREATE TABLE tm.multicurve4326 (id serial, g geometry(multicurve, 4326) ); 89CREATE TABLE tm.multicurvem (id serial, g geometry(multicurvem) ); 90CREATE TABLE tm.multicurvem0 (id serial, g geometry(multicurvem, 0) ); 91CREATE TABLE tm.multicurvem4326 (id serial, g geometry(multicurvem, 4326) ); 92CREATE TABLE tm.multicurvez (id serial, g geometry(multicurvez) ); 93CREATE TABLE tm.multicurvez0 (id serial, g geometry(multicurvez, 0) ); 94CREATE TABLE tm.multicurvez4326 (id serial, g geometry(multicurvez, 4326) ); 95CREATE TABLE tm.multicurvezm (id serial, g geometry(multicurvezm) ); 96CREATE TABLE tm.multicurvezm0 (id serial, g geometry(multicurvezm, 0) ); 97CREATE TABLE tm.multicurvezm4326 (id serial, g geometry(multicurvezm, 4326) ); 98 99CREATE TABLE tm.multilinestring (id serial, g geometry(multilinestring), gg geography(multilinestring) ); 100CREATE TABLE tm.multilinestring0 (id serial, g geometry(multilinestring, 0), gg geography(multilinestring, 0) ); 101CREATE TABLE tm.multilinestring4326 (id serial, g geometry(multilinestring, 4326), gg geography(multilinestring, 4326) ); 102CREATE TABLE tm.multilinestringm (id serial, g geometry(multilinestringm), gg geography(multilinestringm) ); 103CREATE TABLE tm.multilinestringm0 (id serial, g geometry(multilinestringm, 0), gg geography(multilinestringm, 0) ); 104CREATE TABLE tm.multilinestringm4326 (id serial, g geometry(multilinestringm, 4326), gg geography(multilinestringm, 4326) ); 105CREATE TABLE tm.multilinestringz (id serial, g geometry(multilinestringz), gg geography(multilinestringz) ); 106CREATE TABLE tm.multilinestringz0 (id serial, g geometry(multilinestringz, 0), gg geography(multilinestringz, 0) ); 107CREATE TABLE tm.multilinestringz4326 (id serial, g geometry(multilinestringz, 4326), gg geography(multilinestringz, 4326) ); 108CREATE TABLE tm.multilinestringzm (id serial, g geometry(multilinestringzm), gg geography(multilinestringzm) ); 109CREATE TABLE tm.multilinestringzm0 (id serial, g geometry(multilinestringzm, 0), gg geography(multilinestringzm, 0) ); 110CREATE TABLE tm.multilinestringzm4326 (id serial, g geometry(multilinestringzm, 4326), gg geography(multilinestringzm, 4326) ); 111 112CREATE TABLE tm.multipolygon (id serial, g geometry(multipolygon), gg geography(multipolygon) ); 113CREATE TABLE tm.multipolygon0 (id serial, g geometry(multipolygon, 0), gg geography(multipolygon, 0) ); 114CREATE TABLE tm.multipolygon4326 (id serial, g geometry(multipolygon, 4326), gg geography(multipolygon, 4326) ); 115CREATE TABLE tm.multipolygonm (id serial, g geometry(multipolygonm), gg geography(multipolygonm) ); 116CREATE TABLE tm.multipolygonm0 (id serial, g geometry(multipolygonm, 0), gg geography(multipolygonm, 0) ); 117CREATE TABLE tm.multipolygonm4326 (id serial, g geometry(multipolygonm, 4326), gg geography(multipolygonm, 4326) ); 118CREATE TABLE tm.multipolygonz (id serial, g geometry(multipolygonz), gg geography(multipolygonz) ); 119CREATE TABLE tm.multipolygonz0 (id serial, g geometry(multipolygonz, 0), gg geography(multipolygonz, 0) ); 120CREATE TABLE tm.multipolygonz4326 (id serial, g geometry(multipolygonz, 4326), gg geography(multipolygonz, 4326) ); 121CREATE TABLE tm.multipolygonzm (id serial, g geometry(multipolygonzm), gg geography(multipolygonzm) ); 122CREATE TABLE tm.multipolygonzm0 (id serial, g geometry(multipolygonzm, 0), gg geography(multipolygonzm, 0) ); 123CREATE TABLE tm.multipolygonzm4326 (id serial, g geometry(multipolygonzm, 4326), gg geography(multipolygonzm, 4326) ); 124 125CREATE TABLE tm.multipoint (id serial, g geometry(multipoint), gg geography(multipoint) ); 126CREATE TABLE tm.multipoint0 (id serial, g geometry(multipoint, 0), gg geography(multipoint, 0) ); 127CREATE TABLE tm.multipoint4326 (id serial, g geometry(multipoint, 4326), gg geography(multipoint, 4326) ); 128CREATE TABLE tm.multipointm (id serial, g geometry(multipointm), gg geography(multipointm) ); 129CREATE TABLE tm.multipointm0 (id serial, g geometry(multipointm, 0), gg geography(multipointm, 0) ); 130CREATE TABLE tm.multipointm4326 (id serial, g geometry(multipointm, 4326), gg geography(multipointm, 4326) ); 131CREATE TABLE tm.multipointz (id serial, g geometry(multipointz), gg geography(multipointz) ); 132CREATE TABLE tm.multipointz0 (id serial, g geometry(multipointz, 0), gg geography(multipointz, 0) ); 133CREATE TABLE tm.multipointz4326 (id serial, g geometry(multipointz, 4326), gg geography(multipointz, 4326) ); 134CREATE TABLE tm.multipointzm (id serial, g geometry(multipointzm), gg geography(multipointzm) ); 135CREATE TABLE tm.multipointzm0 (id serial, g geometry(multipointzm, 0), gg geography(multipointzm, 0) ); 136CREATE TABLE tm.multipointzm4326 (id serial, g geometry(multipointzm, 4326), gg geography(multipointzm, 4326) ); 137 138CREATE TABLE tm.multisurface (id serial, g geometry(multisurface) ); 139CREATE TABLE tm.multisurface0 (id serial, g geometry(multisurface, 0) ); 140CREATE TABLE tm.multisurface4326 (id serial, g geometry(multisurface, 4326) ); 141CREATE TABLE tm.multisurfacem (id serial, g geometry(multisurfacem) ); 142CREATE TABLE tm.multisurfacem0 (id serial, g geometry(multisurfacem, 0) ); 143CREATE TABLE tm.multisurfacem4326 (id serial, g geometry(multisurfacem, 4326) ); 144CREATE TABLE tm.multisurfacez (id serial, g geometry(multisurfacez) ); 145CREATE TABLE tm.multisurfacez0 (id serial, g geometry(multisurfacez, 0) ); 146CREATE TABLE tm.multisurfacez4326 (id serial, g geometry(multisurfacez, 4326) ); 147CREATE TABLE tm.multisurfacezm (id serial, g geometry(multisurfacezm) ); 148CREATE TABLE tm.multisurfacezm0 (id serial, g geometry(multisurfacezm, 0) ); 149CREATE TABLE tm.multisurfacezm4326 (id serial, g geometry(multisurfacezm, 4326) ); 150 151CREATE TABLE tm.point (id serial, g geometry(point), gg geography(point) ); 152CREATE TABLE tm.point0 (id serial, g geometry(point, 0), gg geography(point, 0) ); 153CREATE TABLE tm.point4326 (id serial, g geometry(point, 4326), gg geography(point, 4326) ); 154CREATE TABLE tm.pointm (id serial, g geometry(pointm), gg geography(pointm) ); 155CREATE TABLE tm.pointm0 (id serial, g geometry(pointm, 0), gg geography(pointm, 0) ); 156CREATE TABLE tm.pointm4326 (id serial, g geometry(pointm, 4326), gg geography(pointm, 4326) ); 157CREATE TABLE tm.pointz (id serial, g geometry(pointz), gg geography(pointz) ); 158CREATE TABLE tm.pointz0 (id serial, g geometry(pointz, 0), gg geography(pointz, 0) ); 159CREATE TABLE tm.pointz4326 (id serial, g geometry(pointz, 4326), gg geography(pointz, 4326) ); 160CREATE TABLE tm.pointzm (id serial, g geometry(pointzm), gg geography(pointzm) ); 161CREATE TABLE tm.pointzm0 (id serial, g geometry(pointzm, 0), gg geography(pointzm, 0) ); 162CREATE TABLE tm.pointzm4326 (id serial, g geometry(pointzm, 4326), gg geography(pointzm, 4326) ); 163 164CREATE TABLE tm.polygon (id serial, g geometry(polygon), gg geography(polygon) ); 165CREATE TABLE tm.polygon0 (id serial, g geometry(polygon, 0), gg geography(polygon, 0) ); 166CREATE TABLE tm.polygon4326 (id serial, g geometry(polygon, 4326), gg geography(polygon, 4326) ); 167CREATE TABLE tm.polygonm (id serial, g geometry(polygonm), gg geography(polygonm) ); 168CREATE TABLE tm.polygonm0 (id serial, g geometry(polygonm, 0), gg geography(polygonm, 0) ); 169CREATE TABLE tm.polygonm4326 (id serial, g geometry(polygonm, 4326), gg geography(polygonm, 4326) ); 170CREATE TABLE tm.polygonz (id serial, g geometry(polygonz), gg geography(polygonz) ); 171CREATE TABLE tm.polygonz0 (id serial, g geometry(polygonz, 0), gg geography(polygonz, 0) ); 172CREATE TABLE tm.polygonz4326 (id serial, g geometry(polygonz, 4326), gg geography(polygonz, 4326) ); 173CREATE TABLE tm.polygonzm (id serial, g geometry(polygonzm), gg geography(polygonzm) ); 174CREATE TABLE tm.polygonzm0 (id serial, g geometry(polygonzm, 0), gg geography(polygonzm, 0) ); 175CREATE TABLE tm.polygonzm4326 (id serial, g geometry(polygonzm, 4326), gg geography(polygonzm, 4326) ); 176 177CREATE TABLE tm.polyhedralsurface (id serial, g geometry(polyhedralsurface) ); 178CREATE TABLE tm.polyhedralsurface0 (id serial, g geometry(polyhedralsurface, 0) ); 179CREATE TABLE tm.polyhedralsurface4326 (id serial, g geometry(polyhedralsurface, 4326) ); 180CREATE TABLE tm.polyhedralsurfacem (id serial, g geometry(polyhedralsurfacem) ); 181CREATE TABLE tm.polyhedralsurfacem0 (id serial, g geometry(polyhedralsurfacem, 0) ); 182CREATE TABLE tm.polyhedralsurfacem4326 (id serial, g geometry(polyhedralsurfacem, 4326) ); 183CREATE TABLE tm.polyhedralsurfacez (id serial, g geometry(polyhedralsurfacez) ); 184CREATE TABLE tm.polyhedralsurfacez0 (id serial, g geometry(polyhedralsurfacez, 0) ); 185CREATE TABLE tm.polyhedralsurfacez4326 (id serial, g geometry(polyhedralsurfacez, 4326) ); 186CREATE TABLE tm.polyhedralsurfacezm (id serial, g geometry(polyhedralsurfacezm) ); 187CREATE TABLE tm.polyhedralsurfacezm0 (id serial, g geometry(polyhedralsurfacezm, 0) ); 188CREATE TABLE tm.polyhedralsurfacezm4326 (id serial, g geometry(polyhedralsurfacezm, 4326) ); 189 190CREATE TABLE tm.tin (id serial, g geometry(tin) ); 191CREATE TABLE tm.tin0 (id serial, g geometry(tin, 0) ); 192CREATE TABLE tm.tin4326 (id serial, g geometry(tin, 4326) ); 193CREATE TABLE tm.tinm (id serial, g geometry(tinm) ); 194CREATE TABLE tm.tinm0 (id serial, g geometry(tinm, 0) ); 195CREATE TABLE tm.tinm4326 (id serial, g geometry(tinm, 4326) ); 196CREATE TABLE tm.tinz (id serial, g geometry(tinz) ); 197CREATE TABLE tm.tinz0 (id serial, g geometry(tinz, 0) ); 198CREATE TABLE tm.tinz4326 (id serial, g geometry(tinz, 4326) ); 199CREATE TABLE tm.tinzm (id serial, g geometry(tinzm) ); 200CREATE TABLE tm.tinzm0 (id serial, g geometry(tinzm, 0) ); 201CREATE TABLE tm.tinzm4326 (id serial, g geometry(tinzm, 4326) ); 202 203CREATE TABLE tm.triangle (id serial, g geometry(triangle) ); 204CREATE TABLE tm.triangle0 (id serial, g geometry(triangle, 0) ); 205CREATE TABLE tm.triangle4326 (id serial, g geometry(triangle, 4326) ); 206CREATE TABLE tm.trianglem (id serial, g geometry(trianglem) ); 207CREATE TABLE tm.trianglem0 (id serial, g geometry(trianglem, 0) ); 208CREATE TABLE tm.trianglem4326 (id serial, g geometry(trianglem, 4326) ); 209CREATE TABLE tm.trianglez (id serial, g geometry(trianglez) ); 210CREATE TABLE tm.trianglez0 (id serial, g geometry(trianglez, 0) ); 211CREATE TABLE tm.trianglez4326 (id serial, g geometry(trianglez, 4326) ); 212CREATE TABLE tm.trianglezm (id serial, g geometry(trianglezm) ); 213CREATE TABLE tm.trianglezm0 (id serial, g geometry(trianglezm, 0) ); 214CREATE TABLE tm.trianglezm4326 (id serial, g geometry(trianglezm, 4326) ); 215 216SELECT 'g', 217 f_table_name, f_geometry_column, 218 coord_dimension, srid, type 219from geometry_columns ORDER BY f_table_name; 220 221SELECT 'gg', 222 f_table_name, f_geography_column, 223 coord_dimension, srid, type 224from geography_columns ORDER BY f_table_name; 225 226SELECT distinct 'catalog-schema', f_table_catalog = current_database(),f_table_schema FROM geometry_columns 227UNION 228SELECT distinct 'catalog-schema', f_table_catalog = current_database(),f_table_schema FROM geography_columns 229; 230 231CREATE TABLE tm.types (id serial, g geometry); 232 233INSERT INTO tm.types(g) values ('POINT EMPTY'); 234INSERT INTO tm.types(g) values ('POINT(0 0)'); 235INSERT INTO tm.types(g) values ('LINESTRING EMPTY'); 236INSERT INTO tm.types(g) values ('POLYGON EMPTY'); 237INSERT INTO tm.types(g) values ('MULTIPOINT EMPTY'); 238INSERT INTO tm.types(g) values ('MULTIPOINT(0 0)'); 239INSERT INTO tm.types(g) values ('MULTILINESTRING EMPTY'); 240INSERT INTO tm.types(g) values ('MULTIPOLYGON EMPTY'); 241INSERT INTO tm.types(g) values ('GEOMETRYCOLLECTION EMPTY'); 242INSERT INTO tm.types(g) values ('CIRCULARSTRING EMPTY'); 243INSERT INTO tm.types(g) values ('COMPOUNDCURVE EMPTY'); 244INSERT INTO tm.types(g) values ('CURVEPOLYGON EMPTY'); 245INSERT INTO tm.types(g) values ('MULTICURVE EMPTY'); 246INSERT INTO tm.types(g) values ('MULTISURFACE EMPTY'); 247INSERT INTO tm.types(g) values ('POLYHEDRALSURFACE EMPTY'); 248INSERT INTO tm.types(g) values ('TRIANGLE EMPTY'); 249INSERT INTO tm.types(g) values ('TIN EMPTY'); 250 251-- all zm flags (17 is the number of base types) 252INSERT INTO tm.types(g) 253SELECT st_force3dz(g) FROM tm.types WHERE id <= 17 ORDER BY id; 254INSERT INTO tm.types(g) 255SELECT st_force3dm(g) FROM tm.types WHERE id <= 17 ORDER BY id; 256INSERT INTO tm.types(g) 257SELECT st_force4d(g) FROM tm.types WHERE id <= 17 ORDER BY id; 258 259-- known srid 260INSERT INTO tm.types(g) 261SELECT st_setsrid(g,4326) FROM tm.types ORDER BY id; 262 263-- Expected: 17 (base count) * 4 (zmflag combinations) * 2 (srids) 264SELECT 'num_types', count(*) from tm.types; 265 266-- Now try to insert each type into each table 267CREATE FUNCTION tm.insert_all(tmpfile_prefix text) 268RETURNS TABLE(out_where varchar, out_srid int, out_type varchar, out_flags varchar, out_status text) 269AS 270$$ 271DECLARE 272 sql text; 273 rec RECORD; 274 rec2 RECORD; 275 tmpfile text; 276 cnt INT; 277 hasgeog BOOL; 278BEGIN 279 280 tmpfile := tmpfile_prefix; 281 282 FOR rec2 IN SELECT * from tm.types ORDER BY id 283 LOOP 284 tmpfile := tmpfile_prefix || rec2.id; 285 sql := 'COPY ( SELECT g FROM tm.types WHERE id = ' || rec2.id || ') TO ' 286 || quote_literal(tmpfile) 287 || ' WITH BINARY '; 288 EXECUTE sql; 289 END LOOP; 290 291 FOR rec IN SELECT * from geometry_columns 292 WHERE f_table_name != 'types' ORDER BY 3 293 LOOP 294 out_where := rec.f_table_name; 295 296 hasgeog := rec.type NOT LIKE '%CURVE%' 297 AND rec.type NOT LIKE '%CIRCULAR%' 298 AND rec.type NOT LIKE '%SURFACE%' 299 AND rec.type NOT LIKE 'TRIANGLE%' 300 AND rec.type NOT LIKE 'TIN%'; 301 302 FOR rec2 IN SELECT * from tm.types ORDER BY id 303 LOOP 304 out_srid := ST_Srid(rec2.g); 305 out_type := substr(ST_GeometryType(rec2.g), 4); 306 IF NOT ST_IsEmpty(rec2.g) THEN 307 out_type := out_type || 'NE'; 308 END IF; 309 out_flags := ST_zmflag(rec2.g); 310 BEGIN 311 sql := 'INSERT INTO ' 312 || quote_ident(rec.f_table_schema) 313 || '.' || quote_ident(rec.f_table_name) 314 || '(g) VALUES (' 315 || quote_literal(rec2.g::text) 316 || ');'; 317 EXECUTE sql; 318 out_status := 'OK'; 319 EXCEPTION 320 WHEN OTHERS THEN 321 out_status := 'KO'; -- || SQLERRM; 322 END; 323 324 -- binary insertion { 325 tmpfile := tmpfile_prefix || rec2.id; 326 sql := 'COPY ' 327 || quote_ident(rec.f_table_schema) 328 || '.' || quote_ident(rec.f_table_name) 329 || '(g) FROM ' 330 || quote_literal(tmpfile) || ' WITH BINARY '; 331 BEGIN 332 EXECUTE sql; 333 out_status := out_status || '-BOK'; 334 EXCEPTION 335 WHEN OTHERS THEN 336 out_status := out_status || '-BKO'; 337 END; 338 -- } 339 340 IF NOT hasgeog THEN 341 RETURN NEXT; 342 CONTINUE; 343 END IF; 344 345 BEGIN 346 sql := 'INSERT INTO ' 347 || quote_ident(rec.f_table_schema) 348 || '.' || quote_ident(rec.f_table_name) 349 || '(gg) VALUES (' 350 || quote_literal(rec2.g::text) 351 || ');'; 352 EXECUTE sql; 353 out_status := out_status || '-GOK'; 354 EXCEPTION 355 WHEN OTHERS THEN 356 out_status := out_status || '-GKO'; 357 END; 358 359 -- binary insertion (geography) { 360 sql := 'COPY ' 361 || quote_ident(rec.f_table_schema) 362 || '.' || quote_ident(rec.f_table_name) 363 || '(gg) FROM ' 364 || quote_literal(tmpfile) || ' WITH BINARY '; 365 BEGIN 366 EXECUTE sql; 367 out_status := out_status || '-BGOK'; 368 EXCEPTION 369 WHEN OTHERS THEN 370 out_status := out_status || '-BGKO'; -- || SQLERRM; 371 END; 372 -- } 373 374 RETURN NEXT; 375 END LOOP; 376 377 -- Count number of geometries in the table 378 sql := 'SELECT count(g) FROM ' 379 || quote_ident(rec.f_table_schema) 380 || '.' || quote_ident(rec.f_table_name); 381 EXECUTE sql INTO STRICT cnt; 382 383 out_srid := NULL; 384 out_type := 'COUNT'; 385 out_flags := cnt; 386 out_status := NULL; 387 RETURN NEXT; 388 389 IF hasgeog THEN 390 -- Count number of geographies in the table 391 sql := 'SELECT count(gg) FROM ' 392 || quote_ident(rec.f_table_schema) 393 || '.' || quote_ident(rec.f_table_name); 394 EXECUTE sql INTO STRICT cnt; 395 396 out_srid := NULL; 397 out_type := 'GCOUNT'; 398 out_flags := cnt; 399 out_status := NULL; 400 RETURN NEXT; 401 END IF; 402 403 END LOOP; 404END; 405$$ LANGUAGE 'plpgsql'; 406 407SELECT * FROM tm.insert_all(:tmpfile); 408 409DROP SCHEMA tm CASCADE; 410 411