OGRPGResultLayer::OGRPGResultLayer( OGRPGDataSource *poDSIn, const char * pszRawQueryIn, PGresult *hInitialResultIn ) { poDS = poDSIn; iNextShapeId = 0; pszRawStatement = CPLStrdup(pszRawQueryIn); osWHERE = ""; BuildFullQueryStatement(); ReadResultDefinition(hInitialResultIn); pszGeomTableName = NULL; pszGeomTableSchemaName = NULL; /* Find at which index the geometry column is */ int iGeomCol = -1; if (poFeatureDefn->GetGeomFieldCount() == 1) { int iRawField; for( iRawField = 0; iRawField < PQnfields(hInitialResultIn); iRawField++ ) { if( strcmp(PQfname(hInitialResultIn,iRawField), poFeatureDefn->GetGeomFieldDefn(0)->GetNameRef()) == 0 ) { iGeomCol = iRawField; break; } } } #ifndef PG_PRE74 /* Determine the table from which the geometry column is extracted */ if (iGeomCol != -1) { Oid tableOID = PQftable(hInitialResultIn, iGeomCol); if (tableOID != InvalidOid) { CPLString osGetTableName; osGetTableName.Printf("SELECT c.relname, n.nspname FROM pg_class c " "JOIN pg_namespace n ON c.relnamespace=n.oid WHERE c.oid = %d ", tableOID); PGresult* hTableNameResult = OGRPG_PQexec(poDS->GetPGConn(), osGetTableName ); if( hTableNameResult && PQresultStatus(hTableNameResult) == PGRES_TUPLES_OK) { if ( PQntuples(hTableNameResult) > 0 ) { pszGeomTableName = CPLStrdup(PQgetvalue(hTableNameResult,0,0)); pszGeomTableSchemaName = CPLStrdup(PQgetvalue(hTableNameResult,0,1)); } } OGRPGClearResult( hTableNameResult ); } } #endif }
/* * Read in field descriptions from a libpq result set. * If self is not null, then also store the information. * If self is null, then just read, don't store. */ BOOL CI_read_fields_from_pgres(ColumnInfoClass *self, PGresult *pgres) { CSTR func = "CI_read_fields"; Int2 lf; int new_num_fields; OID new_adtid, new_relid = 0, new_attid = 0; Int2 new_adtsize; Int4 new_atttypmod = -1; char *new_field_name; /* at first read in the number of fields that are in the query */ new_num_fields = PQnfields(pgres); mylog("num_fields = %d\n", new_num_fields); if (self) { /* according to that allocate memory */ CI_set_num_fields(self, new_num_fields); if (NULL == self->coli_array) return FALSE; } /* now read in the descriptions */ for (lf = 0; lf < new_num_fields; lf++) { new_field_name = PQfname(pgres, lf); new_relid = PQftable(pgres, lf); new_attid = PQftablecol(pgres, lf); new_adtid = PQftype(pgres, lf); new_adtsize = PQfsize(pgres, lf); mylog("READING ATTTYPMOD\n"); new_atttypmod = PQfmod(pgres, lf); /* Subtract the header length */ switch (new_adtid) { case PG_TYPE_DATETIME: case PG_TYPE_TIMESTAMP_NO_TMZONE: case PG_TYPE_TIME: case PG_TYPE_TIME_WITH_TMZONE: break; default: new_atttypmod -= 4; } if (new_atttypmod < 0) new_atttypmod = -1; mylog("%s: fieldname='%s', adtid=%d, adtsize=%d, atttypmod=%d (rel,att)=(%d,%d)\n", func, new_field_name, new_adtid, new_adtsize, new_atttypmod, new_relid, new_attid); if (self) CI_set_field_info(self, lf, new_field_name, new_adtid, new_adtsize, new_atttypmod, new_relid, new_attid); } return TRUE; }
/* * call-seq: * res.ftable( column_number ) -> Fixnum * * Returns the Oid of the table from which the column _column_number_ * was fetched. * * Raises ArgumentError if _column_number_ is out of range or if * the Oid is undefined for that column. */ static VALUE pgresult_ftable(VALUE self, VALUE column_number) { Oid n ; int col_number = NUM2INT(column_number); PGresult *pgresult = pgresult_get(self); if( col_number < 0 || col_number >= PQnfields(pgresult)) rb_raise(rb_eArgError,"Invalid column index: %d", col_number); n = PQftable(pgresult, col_number); return INT2FIX(n); }
RS_DBI_fields * RS_PostgreSQL_createDataMappings(Res_Handle * rsHandle) { PGresult *my_result; RS_DBI_connection *con; RS_DBI_resultSet *result; RS_DBI_fields *flds; int j, num_fields, internal_type; char errMsg[128]; result = RS_DBI_getResultSet(rsHandle); my_result = (PGresult *) result->drvResultSet; con = RS_DBI_getConnection(rsHandle); num_fields = PQnfields(my_result); flds = RS_DBI_allocFields(num_fields); /* this returns malloced data (not from R) */ char buff[1000]; /* Buffer to hold the sql query to check whether the given column is nullable */ PGconn *conn; PGresult *res; conn = (PGconn *) con->drvConnection; for (j = 0; j < num_fields; j++) { flds->name[j] = RS_DBI_copyString(PQfname(my_result, j)); flds->type[j] = (int) PQftype(my_result, j); flds->length[j] = (Sint) PQfsize(my_result, j); /* NOTE: PQfmod is -1 incase of no information */ flds->precision[j] = (Sint) PQfmod(my_result, j); flds->scale[j] = (Sint) - 1; /* PQftablecol returns the column number (within its table) of * the column making up the specified query result column.Zero * is returned if the column number is out of range, or if the * specified column is not a simple reference to a table * column, or when using pre-3.0 protocol. So * "if(PQftablecol(my_result,j) !=0)" checks whether the * particular colomn in the result set is column of table or * not. Or else there is no meaning in checking whether a * column is nullable or not if it does not belong to the * table. */ flds->nullOk[j] = (Sint) INT_MIN; /* This should translate to NA in R */ if (PQftablecol(my_result, j) != 0) { /* Code to find whether a row can be nullable or not */ /* we might better just store the table id and column number for lazy evaluation at dbColumnInfo call*/ /* although the database structure can change, we are not in transaction anyway and there is no guarantee in current code */ snprintf(buff, 1000, "select attnotnull from pg_attribute where attrelid=%d and attnum='%d'", PQftable(my_result, j), PQftablecol(my_result, j)); res = PQexec(conn, buff); if (res && (PQntuples(res) > 0)) { const char * attnotnull = PQgetvalue(res, 0, 0); if(strcmp(attnotnull, "f") == 0) { flds->nullOk[j] = (Sint) 1; /* nollOK is TRUE when attnotnull is f*/ } if(strcmp(attnotnull, "t") == 0) { flds->nullOk[j] = (Sint) 0; /* nollOK is FALSE when attnotnull is t*/ } } PQclear(res); } internal_type = (int) PQftype(my_result, j); switch (internal_type) { case BOOLOID: flds->Sclass[j] = LOGICAL_TYPE; break; case BPCHAROID: flds->Sclass[j] = CHARACTER_TYPE; flds->isVarLength[j] = (Sint) 0; break; case VARCHAROID: case TEXTOID: case BYTEAOID: case NAMEOID: case MACADDROID: case INETOID: flds->Sclass[j] = CHARACTER_TYPE; flds->isVarLength[j] = (Sint) 1; break; case INT2OID: case INT4OID: case OIDOID: flds->Sclass[j] = INTEGER_TYPE; break; case INT8OID: if (sizeof(Sint) >= 8) { flds->Sclass[j] = INTEGER_TYPE; } else { flds->Sclass[j] = NUMERIC_TYPE; } break; case NUMERICOID: case FLOAT8OID: case FLOAT4OID: flds->Sclass[j] = NUMERIC_TYPE; break; case DATEOID: case TIMEOID: case TIMETZOID: case TIMESTAMPOID: case TIMESTAMPTZOID: case INTERVALOID: flds->Sclass[j] = CHARACTER_TYPE; /*flds->isVarLength[j] = (Sint) 1; */ break; default: flds->Sclass[j] = CHARACTER_TYPE; flds->isVarLength[j] = (Sint) 1; snprintf(buff, 1000, "select typname, typcategory from pg_type where oid = %d", internal_type); res = PQexec(conn, buff); if (res) { char * typename; char * typecat; int ntuples; ntuples = PQntuples(res); if(ntuples == 1) { typename = PQgetvalue(res, 0, 0); typecat = PQgetvalue(res, 0, 1); if(*typecat == 'E') { /* This is enum, ok */ } else if(*typecat == 'A') { /*This is array, ok */ } else { snprintf(errMsg, 128, "unrecognized PostgreSQL field type %s (id:%d) in column %d", typename, internal_type, j); RS_DBI_errorMessage(errMsg, RS_DBI_WARNING); } } else { snprintf(errMsg, 128, "oid: %d, ntuples: %d", internal_type, ntuples); RS_DBI_errorMessage(errMsg, RS_DBI_WARNING); } PQclear(res); } else {
PGresult * pqt_copyresult(PGtypeArgs *args, int nattrs) { int i; PGresult *res; int tableid, columnid, format; PGresAttDesc *ad = (PGresAttDesc *) malloc(nattrs * sizeof(PGresAttDesc)); if (!ad) { PQseterror(args->err, PQT_OUTOFMEMORY); return NULL; } tableid = PQftable(args->get.result, args->get.field_num); columnid = PQftablecol(args->get.result, args->get.field_num); format = PQfformat(args->get.result, args->get.field_num); for (i=0; i < nattrs; i++) { ad[i].tableid = tableid; ad[i].columnid = columnid; ad[i].format = format; /* simple array */ if (args->typhandler->nattrs == 0) { ad[i].typid = args->typhandler->typoid; ad[i].typlen = args->typhandler->typlen; ad[i].name = NULL; ad[i].atttypmod = -1; } /* composite/record */ else { ad[i].typid = args->typhandler->attDescs[i].attoid; ad[i].typlen = args->typhandler->attDescs[i].attlen; ad[i].name = args->typhandler->attDescs[i].attname; ad[i].atttypmod = args->typhandler->attDescs[i].atttypmod; } } res = PQcopyResult(args->get.result, PG_COPYRES_EVENTS | PG_COPYRES_NOTICEHOOKS); if (!res) { free(ad); PQseterror(args->err, PQT_OUTOFMEMORY); return NULL; } if (!PQsetResultAttrs(res, nattrs, ad)) { PQclear(res); PQseterror(args->err, PQT_OUTOFMEMORY); res = NULL; } free(ad); return res; }
OGRPGResultLayer::OGRPGResultLayer( OGRPGDataSource *poDSIn, const char * pszRawQueryIn, PGresult *hInitialResultIn ) { poDS = poDSIn; iNextShapeId = 0; pszRawStatement = CPLStrdup(pszRawQueryIn); osWHERE = ""; BuildFullQueryStatement(); ReadResultDefinition(hInitialResultIn); pszGeomTableName = NULL; pszGeomTableSchemaName = NULL; /* Find at which index the geometry column is */ /* and prepare a request to identify not-nullable fields */ int iGeomCol = -1; CPLString osRequest; std::map< std::pair<int,int>, int> oMapAttributeToFieldIndex; int iRawField; for( iRawField = 0; iRawField < PQnfields(hInitialResultIn); iRawField++ ) { if( poFeatureDefn->GetGeomFieldCount() == 1 && strcmp(PQfname(hInitialResultIn,iRawField), poFeatureDefn->GetGeomFieldDefn(0)->GetNameRef()) == 0 ) { iGeomCol = iRawField; } Oid tableOID = PQftable(hInitialResultIn, iRawField); int tableCol = PQftablecol(hInitialResultIn, iRawField); if( tableOID != InvalidOid && tableCol > 0 ) { if( osRequest.size() ) osRequest += " OR "; osRequest += "(attrelid = "; osRequest += CPLSPrintf("%d", tableOID); osRequest += " AND attnum = "; osRequest += CPLSPrintf("%d)", tableCol); oMapAttributeToFieldIndex[std::pair<int,int>(tableOID,tableCol)] = iRawField; } } if( osRequest.size() ) { osRequest = "SELECT attnum, attrelid FROM pg_attribute WHERE attnotnull = 't' AND (" + osRequest + ")"; PGresult* hResult = OGRPG_PQexec(poDS->GetPGConn(), osRequest ); if( hResult && PQresultStatus(hResult) == PGRES_TUPLES_OK) { int iCol; for( iCol = 0; iCol < PQntuples(hResult); iCol++ ) { const char* pszAttNum = PQgetvalue(hResult,iCol,0); const char* pszAttRelid = PQgetvalue(hResult,iCol,1); int iRawField = oMapAttributeToFieldIndex[std::pair<int,int>(atoi(pszAttRelid),atoi(pszAttNum))]; const char* pszFieldname = PQfname(hInitialResultIn,iRawField); int iFieldIdx = poFeatureDefn->GetFieldIndex(pszFieldname); if( iFieldIdx >= 0 ) poFeatureDefn->GetFieldDefn(iFieldIdx)->SetNullable(FALSE); else { iFieldIdx = poFeatureDefn->GetGeomFieldIndex(pszFieldname); if( iFieldIdx >= 0 ) poFeatureDefn->GetGeomFieldDefn(iFieldIdx)->SetNullable(FALSE); } } } OGRPGClearResult( hResult ); } #ifndef PG_PRE74 /* Determine the table from which the geometry column is extracted */ if (iGeomCol != -1) { Oid tableOID = PQftable(hInitialResultIn, iGeomCol); if (tableOID != InvalidOid) { CPLString osGetTableName; osGetTableName.Printf("SELECT c.relname, n.nspname FROM pg_class c " "JOIN pg_namespace n ON c.relnamespace=n.oid WHERE c.oid = %d ", tableOID); PGresult* hTableNameResult = OGRPG_PQexec(poDS->GetPGConn(), osGetTableName ); if( hTableNameResult && PQresultStatus(hTableNameResult) == PGRES_TUPLES_OK) { if ( PQntuples(hTableNameResult) > 0 ) { pszGeomTableName = CPLStrdup(PQgetvalue(hTableNameResult,0,0)); pszGeomTableSchemaName = CPLStrdup(PQgetvalue(hTableNameResult,0,1)); } } OGRPGClearResult( hTableNameResult ); } } #endif }
static int pgsql_stmt_get_column_meta(pdo_stmt_t *stmt, zend_long colno, zval *return_value) { pdo_pgsql_stmt *S = (pdo_pgsql_stmt*)stmt->driver_data; PGresult *res; char *q=NULL; ExecStatusType status; Oid table_oid; char *table_name=NULL; if (!S->result) { return FAILURE; } if (colno >= stmt->column_count) { return FAILURE; } array_init(return_value); add_assoc_long(return_value, "pgsql:oid", S->cols[colno].pgsql_type); table_oid = PQftable(S->result, colno); add_assoc_long(return_value, "pgsql:table_oid", table_oid); table_name = pdo_pgsql_translate_oid_to_table(table_oid, S->H->server); if (table_name) { add_assoc_string(return_value, "table", table_name); } switch (S->cols[colno].pgsql_type) { case BOOLOID: add_assoc_string(return_value, "native_type", BOOLLABEL); break; case BYTEAOID: add_assoc_string(return_value, "native_type", BYTEALABEL); break; case INT8OID: add_assoc_string(return_value, "native_type", INT8LABEL); break; case INT2OID: add_assoc_string(return_value, "native_type", INT2LABEL); break; case INT4OID: add_assoc_string(return_value, "native_type", INT4LABEL); break; case TEXTOID: add_assoc_string(return_value, "native_type", TEXTLABEL); break; case VARCHAROID: add_assoc_string(return_value, "native_type", VARCHARLABEL); break; case DATEOID: add_assoc_string(return_value, "native_type", DATELABEL); break; case TIMESTAMPOID: add_assoc_string(return_value, "native_type", TIMESTAMPLABEL); break; default: /* Fetch metadata from Postgres system catalogue */ spprintf(&q, 0, "SELECT TYPNAME FROM PG_TYPE WHERE OID=%u", S->cols[colno].pgsql_type); res = PQexec(S->H->server, q); efree(q); status = PQresultStatus(res); if (status == PGRES_TUPLES_OK && 1 == PQntuples(res)) { add_assoc_string(return_value, "native_type", PQgetvalue(res, 0, 0)); } PQclear(res); } return 1; }