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 1275 of file shp2pgsql-core.c.

{
        stringbuffer_t *sb;
        char *ret;
        int j;
 
        /* Create the stringbuffer containing the header; we use this API as it's easier
           for handling string resizing during append */
        sb = stringbuffer_create();
        stringbuffer_clear(sb);
 
        /* Set the client encoding if required */
        if (state->config->encoding)
        {
                stringbuffer_aprintf(sb, "SET CLIENT_ENCODING TO UTF8;\n");
        }
 
        /* Use SQL-standard string escaping rather than PostgreSQL standard */
        stringbuffer_aprintf(sb, "SET STANDARD_CONFORMING_STRINGS TO ON;\n");
 
        /* Drop table if requested */
        if (state->config->opt == 'd')
        {
                if (state->config->schema)
                {
                        if (state->config->readshape == 1 && (! state->config->geography) )
                        {
                                stringbuffer_aprintf(sb, "SELECT DropGeometryColumn('%s','%s','%s');\n",
                                                     state->config->schema, state->config->table, state->geo_col);
                        }
 
                        stringbuffer_aprintf(sb, "DROP TABLE IF EXISTS \"%s\".\"%s\";\n", state->config->schema,
                                             state->config->table);
                }
                else
                {
                        if (state->config->readshape == 1  && (! state->config->geography) )
                        {
                                stringbuffer_aprintf(sb, "SELECT DropGeometryColumn('','%s','%s');\n",
                                                     state->config->table, state->geo_col);
                        }
 
                        stringbuffer_aprintf(sb, "DROP TABLE IF EXISTS \"%s\";\n", state->config->table);
                }
        }
 
        /* Start of transaction if we are using one */
        if (state->config->usetransaction)
        {
                stringbuffer_aprintf(sb, "BEGIN;\n");
        }
 
        /* If not in 'append' mode create the spatial table */
        if (state->config->opt != 'a')
        {
                /*
                * Create a table for inserting the shapes into with appropriate
                * columns and types
                */
                if (state->config->schema)
                {
                        stringbuffer_aprintf(sb, "CREATE TABLE \"%s\".\"%s\" (gid serial",
                                             state->config->schema, state->config->table);
                }
                else
                {
                        stringbuffer_aprintf(sb, "CREATE TABLE \"%s\" (gid serial", state->config->table);
                }
 
                /* Generate the field types based upon the shapefile information */
                for (j = 0; j < state->num_fields; j++)
                {
                        stringbuffer_aprintf(sb, ",\n\"%s\" ", state->field_names[j]);
 
                        /* First output the raw field type string */
                        stringbuffer_aprintf(sb, "%s", state->pgfieldtypes[j]);
 
                        /* Some types do have typmods */
                        /* Apply width typmod for varchar if there is positive width **/
                        if (!strcmp("varchar", state->pgfieldtypes[j]) && state->widths[j] > 0)
                                        stringbuffer_aprintf(sb, "(%d)", state->widths[j]);
 
                        if (!strcmp("numeric", state->pgfieldtypes[j]))
                        {
                                /* Doubles we just allow PostgreSQL to auto-detect the size */
                                if (state->types[j] != FTDouble)
                                        stringbuffer_aprintf(sb, "(%d,0)", state->widths[j]);
                        }
                }
 
                /* Add the geography column directly to the table definition, we don't
                   need to do an AddGeometryColumn() call. */
                if (state->config->readshape == 1 && state->config->geography)
                {
                        char *dimschar;
 
                        if (state->pgdims == 4)
                                dimschar = "ZM";
                        else
                                dimschar = "";
 
                        if (state->to_srid == SRID_UNKNOWN ){
                                state->to_srid = 4326;
                        }
 
                        stringbuffer_aprintf(sb, ",\n\"%s\" geography(%s%s,%d)", state->geo_col, state->pgtype, dimschar, state->to_srid);
                }
                stringbuffer_aprintf(sb, ")");
 
                /* Tablespace is optional. */
                if (state->config->tablespace != NULL)
                {
                        stringbuffer_aprintf(sb, " TABLESPACE \"%s\"", state->config->tablespace);
                }
                stringbuffer_aprintf(sb, ";\n");
 
                /* Create the primary key.  This is done separately because the index for the PK needs
                 * to be in the correct tablespace. */
 
                /* TODO: Currently PostgreSQL does not allow specifying an index to use for a PK (so you get
                 *       a default one called table_pkey) and it does not provide a way to create a PK index
                 *       in a specific tablespace.  So as a hacky solution we create the PK, then move the
                 *       index to the correct tablespace.  Eventually this should be:
                 *           CREATE INDEX table_pkey on table(gid) TABLESPACE tblspc;
                 *           ALTER TABLE table ADD PRIMARY KEY (gid) USING INDEX table_pkey;
                 *       A patch has apparently been submitted to PostgreSQL to enable this syntax, see this thread:
                 *           http://archives.postgresql.org/pgsql-hackers/2011-01/msg01405.php */
                stringbuffer_aprintf(sb, "ALTER TABLE ");
 
                /* Schema is optional, include if present. */
                if (state->config->schema)
                {
                        stringbuffer_aprintf(sb, "\"%s\".",state->config->schema);
                }
                stringbuffer_aprintf(sb, "\"%s\" ADD PRIMARY KEY (gid);\n", state->config->table);
 
                /* Tablespace is optional for the index. */
                if (state->config->idxtablespace != NULL)
                {
                        stringbuffer_aprintf(sb, "ALTER INDEX ");
                        if (state->config->schema)
                        {
                                stringbuffer_aprintf(sb, "\"%s\".",state->config->schema);
                        }
 
                        /* WARNING: We're assuming the default "table_pkey" name for the primary
                         *          key index.  PostgreSQL may use "table_pkey1" or similar in the
                         *          case of a name conflict, so you may need to edit the produced
                         *          SQL in this rare case. */
                        stringbuffer_aprintf(sb, "\"%s_pkey\" SET TABLESPACE \"%s\";\n",
                                                state->config->table, state->config->idxtablespace);
                }
 
                /* Create the geometry column with an addgeometry call */
                if (state->config->readshape == 1 && (!state->config->geography))
                {
                        /* If they didn't specify a target SRID, see if they specified a source SRID. */
                        int32_t srid = state->to_srid;
                        if (state->config->schema)
                        {
                                stringbuffer_aprintf(sb, "SELECT AddGeometryColumn('%s','%s','%s','%d',",
                                                     state->config->schema, state->config->table, state->geo_col, srid);
                        }
                        else
                        {
                                stringbuffer_aprintf(sb, "SELECT AddGeometryColumn('','%s','%s','%d',",
                                                     state->config->table, state->geo_col, srid);
                        }
 
                        stringbuffer_aprintf(sb, "'%s',%d);\n", state->pgtype, state->pgdims);
                }
        }
 
        if (state->config->dump_format && state->to_srid != state->from_srid){
                stringbuffer_aprintf(sb, "CREATE TEMP TABLE \"pgis_tmp_%s\" AS SELECT * FROM ", state->config->table);
                /* Schema is optional, include if present. */
                if (state->config->schema)
                {
                        stringbuffer_aprintf(sb, "\"%s\".",state->config->schema);
                }
                stringbuffer_aprintf(sb, "\"%s\" WHERE false;\n", state->config->table, state->geo_col);
                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);
        }
 
        /* Copy the string buffer into a new string, destroying the string buffer */
        ret = (char *)malloc(strlen((char *)stringbuffer_getstring(sb)) + 1);
        strcpy(ret, (char *)stringbuffer_getstring(sb));
        stringbuffer_destroy(sb);
 
        *strheader = ret;
 
        return SHPLOADEROK;
}

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: