PostGIS  3.1.6dev-r@@SVN_REVISION@@

◆ ShpLoaderGetSQLHeader()

int ShpLoaderGetSQLHeader ( SHPLOADERSTATE state,
char **  strheader 
)

TODO: if the table has more then one geometry column the DROP TABLE call will leave spurious records in geometry_columns.

If the geometry column in the table being dropped does not match 'the_geom' or the name specified with -g an error is returned by DropGeometryColumn.

The table to be dropped might not exist.

If we are in dump mode and a transform was asked for need to create a temp table to store original data You may ask, why don't we go straight into the main table and then do an alter table alter column afterwards Main reason is so we don't incur the penalty of WAL logging when we change the typmod in final run.

create a temp table with same structure as main except for no restriction on geometry type

out input data is going to be in different srid from target, so need to remove type constraint

Definition at line 1277 of file shp2pgsql-core.c.

1278 {
1279  stringbuffer_t *sb;
1280  char *ret;
1281  int j;
1282 
1283  /* Create the stringbuffer containing the header; we use this API as it's easier
1284  for handling string resizing during append */
1285  sb = stringbuffer_create();
1286  stringbuffer_clear(sb);
1287 
1288  /* Set the client encoding if required */
1289  if (state->config->encoding)
1290  {
1291  stringbuffer_aprintf(sb, "SET CLIENT_ENCODING TO UTF8;\n");
1292  }
1293 
1294  /* Use SQL-standard string escaping rather than PostgreSQL standard */
1295  stringbuffer_aprintf(sb, "SET STANDARD_CONFORMING_STRINGS TO ON;\n");
1296 
1297  /* Drop table if requested */
1298  if (state->config->opt == 'd')
1299  {
1311  if (state->config->schema)
1312  {
1313  if (state->config->readshape == 1 && (! state->config->geography) )
1314  {
1315  stringbuffer_aprintf(sb, "SELECT DropGeometryColumn('%s','%s','%s');\n",
1316  state->config->schema, state->config->table, state->geo_col);
1317  }
1318 
1319  stringbuffer_aprintf(sb, "DROP TABLE IF EXISTS \"%s\".\"%s\";\n", state->config->schema,
1320  state->config->table);
1321  }
1322  else
1323  {
1324  if (state->config->readshape == 1 && (! state->config->geography) )
1325  {
1326  stringbuffer_aprintf(sb, "SELECT DropGeometryColumn('','%s','%s');\n",
1327  state->config->table, state->geo_col);
1328  }
1329 
1330  stringbuffer_aprintf(sb, "DROP TABLE IF EXISTS \"%s\";\n", state->config->table);
1331  }
1332  }
1333 
1334  /* Start of transaction if we are using one */
1335  if (state->config->usetransaction)
1336  {
1337  stringbuffer_aprintf(sb, "BEGIN;\n");
1338  }
1339 
1340  /* If not in 'append' mode create the spatial table */
1341  if (state->config->opt != 'a')
1342  {
1343  /*
1344  * Create a table for inserting the shapes into with appropriate
1345  * columns and types
1346  */
1347  if (state->config->schema)
1348  {
1349  stringbuffer_aprintf(sb, "CREATE TABLE \"%s\".\"%s\" (gid serial",
1350  state->config->schema, state->config->table);
1351  }
1352  else
1353  {
1354  stringbuffer_aprintf(sb, "CREATE TABLE \"%s\" (gid serial", state->config->table);
1355  }
1356 
1357  /* Generate the field types based upon the shapefile information */
1358  for (j = 0; j < state->num_fields; j++)
1359  {
1360  stringbuffer_aprintf(sb, ",\n\"%s\" ", state->field_names[j]);
1361 
1362  /* First output the raw field type string */
1363  stringbuffer_aprintf(sb, "%s", state->pgfieldtypes[j]);
1364 
1365  /* Some types do have typmods */
1366  /* Apply width typmod for varchar if there is positive width **/
1367  if (!strcmp("varchar", state->pgfieldtypes[j]) && state->widths[j] > 0)
1368  stringbuffer_aprintf(sb, "(%d)", state->widths[j]);
1369 
1370  if (!strcmp("numeric", state->pgfieldtypes[j]))
1371  {
1372  /* Doubles we just allow PostgreSQL to auto-detect the size */
1373  if (state->types[j] != FTDouble)
1374  stringbuffer_aprintf(sb, "(%d,0)", state->widths[j]);
1375  }
1376  }
1377 
1378  /* Add the geography column directly to the table definition, we don't
1379  need to do an AddGeometryColumn() call. */
1380  if (state->config->readshape == 1 && state->config->geography)
1381  {
1382  char *dimschar;
1383 
1384  if (state->pgdims == 4)
1385  dimschar = "ZM";
1386  else
1387  dimschar = "";
1388 
1389  if (state->to_srid == SRID_UNKNOWN ){
1390  state->to_srid = 4326;
1391  }
1392 
1393  stringbuffer_aprintf(sb, ",\n\"%s\" geography(%s%s,%d)", state->geo_col, state->pgtype, dimschar, state->to_srid);
1394  }
1395  stringbuffer_aprintf(sb, ")");
1396 
1397  /* Tablespace is optional. */
1398  if (state->config->tablespace != NULL)
1399  {
1400  stringbuffer_aprintf(sb, " TABLESPACE \"%s\"", state->config->tablespace);
1401  }
1402  stringbuffer_aprintf(sb, ";\n");
1403 
1404  /* Create the primary key. This is done separately because the index for the PK needs
1405  * to be in the correct tablespace. */
1406 
1407  /* TODO: Currently PostgreSQL does not allow specifying an index to use for a PK (so you get
1408  * a default one called table_pkey) and it does not provide a way to create a PK index
1409  * in a specific tablespace. So as a hacky solution we create the PK, then move the
1410  * index to the correct tablespace. Eventually this should be:
1411  * CREATE INDEX table_pkey on table(gid) TABLESPACE tblspc;
1412  * ALTER TABLE table ADD PRIMARY KEY (gid) USING INDEX table_pkey;
1413  * A patch has apparently been submitted to PostgreSQL to enable this syntax, see this thread:
1414  * http://archives.postgresql.org/pgsql-hackers/2011-01/msg01405.php */
1415  stringbuffer_aprintf(sb, "ALTER TABLE ");
1416 
1417  /* Schema is optional, include if present. */
1418  if (state->config->schema)
1419  {
1420  stringbuffer_aprintf(sb, "\"%s\".",state->config->schema);
1421  }
1422  stringbuffer_aprintf(sb, "\"%s\" ADD PRIMARY KEY (gid);\n", state->config->table);
1423 
1424  /* Tablespace is optional for the index. */
1425  if (state->config->idxtablespace != NULL)
1426  {
1427  stringbuffer_aprintf(sb, "ALTER INDEX ");
1428  if (state->config->schema)
1429  {
1430  stringbuffer_aprintf(sb, "\"%s\".",state->config->schema);
1431  }
1432 
1433  /* WARNING: We're assuming the default "table_pkey" name for the primary
1434  * key index. PostgreSQL may use "table_pkey1" or similar in the
1435  * case of a name conflict, so you may need to edit the produced
1436  * SQL in this rare case. */
1437  stringbuffer_aprintf(sb, "\"%s_pkey\" SET TABLESPACE \"%s\";\n",
1438  state->config->table, state->config->idxtablespace);
1439  }
1440 
1441  /* Create the geometry column with an addgeometry call */
1442  if (state->config->readshape == 1 && (!state->config->geography))
1443  {
1444  /* If they didn't specify a target SRID, see if they specified a source SRID. */
1445  int32_t srid = state->to_srid;
1446  if (state->config->schema)
1447  {
1448  stringbuffer_aprintf(sb, "SELECT AddGeometryColumn('%s','%s','%s','%d',",
1449  state->config->schema, state->config->table, state->geo_col, srid);
1450  }
1451  else
1452  {
1453  stringbuffer_aprintf(sb, "SELECT AddGeometryColumn('','%s','%s','%d',",
1454  state->config->table, state->geo_col, srid);
1455  }
1456 
1457  stringbuffer_aprintf(sb, "'%s',%d);\n", state->pgtype, state->pgdims);
1458  }
1459  }
1460 
1464  if (state->config->dump_format && state->to_srid != state->from_srid){
1466  stringbuffer_aprintf(sb, "CREATE TEMP TABLE \"pgis_tmp_%s\" AS SELECT * FROM ", state->config->table);
1467  /* Schema is optional, include if present. */
1468  if (state->config->schema)
1469  {
1470  stringbuffer_aprintf(sb, "\"%s\".",state->config->schema);
1471  }
1472  stringbuffer_aprintf(sb, "\"%s\" WHERE false;\n", state->config->table, state->geo_col);
1474  stringbuffer_aprintf(sb, "ALTER TABLE \"pgis_tmp_%s\" ALTER COLUMN \"%s\" TYPE geometry USING ( (\"%s\"::geometry) ); \n", state->config->table, state->geo_col, state->geo_col);
1475  }
1476 
1477  /* Copy the string buffer into a new string, destroying the string buffer */
1478  ret = (char *)malloc(strlen((char *)stringbuffer_getstring(sb)) + 1);
1479  strcpy(ret, (char *)stringbuffer_getstring(sb));
1481 
1482  *strheader = ret;
1483 
1484  return SHPLOADEROK;
1485 }
#define SRID_UNKNOWN
Unknown SRID value.
Definition: liblwgeom.h:229
void * malloc(YYSIZE_T)
@ FTDouble
Definition: shapefil.h:641
#define SHPLOADEROK
void stringbuffer_clear(stringbuffer_t *s)
Reset the stringbuffer_t.
Definition: stringbuffer.c:88
int stringbuffer_aprintf(stringbuffer_t *s, const char *fmt,...)
Appends a formatted string to the current string buffer, using the format and argument list provided.
Definition: stringbuffer.c:228
stringbuffer_t * stringbuffer_create(void)
Allocate a new stringbuffer_t.
Definition: stringbuffer.c:33
void stringbuffer_destroy(stringbuffer_t *s)
Free the stringbuffer_t and all memory managed within it.
Definition: stringbuffer.c:76
const char * stringbuffer_getstring(stringbuffer_t *s)
Returns a reference to the internal string being managed by the stringbuffer.
Definition: stringbuffer.c:113
DBFFieldType * types
SHPLOADERCONFIG * config

References shp_loader_state::config, shp_loader_config::dump_format, shp_loader_config::encoding, shp_loader_state::field_names, shp_loader_state::from_srid, FTDouble, shp_loader_state::geo_col, shp_loader_config::geography, shp_loader_config::idxtablespace, malloc(), shp_loader_state::num_fields, shp_loader_config::opt, shp_loader_state::pgdims, shp_loader_state::pgfieldtypes, shp_loader_state::pgtype, shp_loader_config::readshape, shp_loader_config::schema, SHPLOADEROK, SRID_UNKNOWN, stringbuffer_aprintf(), stringbuffer_clear(), stringbuffer_create(), stringbuffer_destroy(), stringbuffer_getstring(), shp_loader_config::table, shp_loader_config::tablespace, shp_loader_state::to_srid, shp_loader_state::types, shp_loader_config::usetransaction, and shp_loader_state::widths.

Referenced by pgui_action_import().

Here is the call graph for this function:
Here is the caller graph for this function: