in src/odbc/rsodbc/rscatalog.cpp [4295:4589]
static void buildUniversalAllSchemaColumnsQuery(char *pszCatalogQuery,
RS_STMT_INFO *pStmt,
SQLCHAR *pCatalogName,
SQLSMALLINT cbCatalogName,
SQLCHAR *pSchemaName,
SQLSMALLINT cbSchemaName,
SQLCHAR *pTableName,
SQLSMALLINT cbTableName,
SQLCHAR *pColumnName,
SQLSMALLINT cbColumnName)
{
std::string result = "";
char catalogFilter[MAX_LARGE_TEMP_BUF_LEN];
char filterClause[MAX_CATALOG_QUERY_FILTER_LEN];
// std::string unknownColumnSize = "2147483647";
getCatalogFilterCondition(catalogFilter, MAX_LARGE_TEMP_BUF_LEN, pStmt, (char *)pCatalogName, cbCatalogName,
FALSE, NULL);
result.append("SELECT database_name AS TABLE_CAT, "
" schema_name AS TABLE_SCHEM, "
" table_name, "
" COLUMN_NAME, "
" CAST(CASE regexp_replace(data_type, '^_.', 'ARRAY') "
" WHEN 'text' THEN 12 "
" WHEN 'bit' THEN -7 "
" WHEN 'bool' THEN -7 "
" WHEN 'boolean' THEN -7 "
" WHEN 'varchar' THEN 12 "
" WHEN 'character varying' THEN 12 "
" WHEN 'char' THEN 1 "
" WHEN 'character' THEN 1 "
" WHEN 'nchar' THEN 1 "
" WHEN 'bpchar' THEN 1 "
" WHEN 'nvarchar' THEN 12 "
" WHEN '\"char\"' THEN 1 "
" WHEN 'date' THEN 91 "
" WHEN 'time' THEN 92 "
" WHEN 'time without time zone' THEN 92 "
" WHEN 'timetz' THEN 92 "
" WHEN 'time with time zone' THEN 92 "
" WHEN 'timestamp' THEN 93 "
" WHEN 'timestamp without time zone' THEN 93 "
" WHEN 'timestamptz' THEN 93 "
" WHEN 'timestamp with time zone' THEN 93 "
" WHEN 'smallint' THEN 5 "
" WHEN 'int2' THEN 5 "
" WHEN 'integer' THEN 4 "
" WHEN 'int' THEN 4 "
" WHEN 'int4' THEN 4 "
" WHEN 'bigint' THEN -5 "
" WHEN 'int8' THEN -5 "
" WHEN 'decimal' THEN 3 "
" WHEN 'real' THEN 7 "
" WHEN 'float4' THEN 7 "
" WHEN 'double precision' THEN 8 "
" WHEN 'float8' THEN 8 "
" WHEN 'float' THEN 6 "
" WHEN 'numeric' THEN 2 "
" WHEN 'bytea' THEN -2 "
" WHEN 'oid' THEN -5 "
" WHEN 'name' THEN 12 "
" WHEN 'ARRAY' THEN 2003 "
" WHEN 'geometry' THEN -4 "
" WHEN 'super' THEN -1 "
" WHEN 'varbyte' THEN -4 "
" WHEN 'geography' THEN -4 "
" WHEN 'intervaly2m' THEN 107 "
" WHEN 'intervald2s' THEN 110 "
" ELSE 0 END AS SMALLINT) AS DATA_TYPE, "
" CASE data_type "
" WHEN 'boolean' THEN 'bool' "
" WHEN 'character varying' THEN 'varchar' "
" WHEN '\"char\"' THEN 'char' "
" WHEN 'smallint' THEN 'int2' "
" WHEN 'integer' THEN 'int4' "
" WHEN 'bigint' THEN 'int8' "
" WHEN 'real' THEN 'float4' "
" WHEN 'double precision' THEN 'float8' "
" WHEN 'time without time zone' THEN 'time' "
" WHEN 'time with time zone' THEN 'timetz' "
" WHEN 'timestamp without time zone' THEN 'timestamp' "
" WHEN 'timestamp with time zone' THEN 'timestamptz' "
" ELSE data_type "
" END AS TYPE_NAME, "
" CASE data_type "
" WHEN 'int4' THEN 10 "
" WHEN 'bit' THEN 1 "
" WHEN 'bool' THEN 1 "
" WHEN 'boolean' THEN 1 "
" WHEN 'varchar' THEN character_maximum_length "
" WHEN 'character varying' THEN character_maximum_length "
" WHEN 'char' THEN character_maximum_length "
" WHEN 'character' THEN character_maximum_length "
" WHEN 'nchar' THEN character_maximum_length "
" WHEN 'bpchar' THEN character_maximum_length "
" WHEN 'nvarchar' THEN character_maximum_length "
" WHEN 'date' THEN 13 "
" WHEN 'time' THEN 15 "
" WHEN 'time without time zone' THEN 15 "
" WHEN 'timetz' THEN 21 "
" WHEN 'time with time zone' THEN 21 "
" WHEN 'timestamp' THEN 29 "
" WHEN 'timestamp without time zone' THEN 29 "
" WHEN 'timestamptz' THEN 35 "
" WHEN 'timestamp with time zone' THEN 35 "
" WHEN 'smallint' THEN 5 "
" WHEN 'int2' THEN 5 "
" WHEN 'integer' THEN 10 "
" WHEN 'int' THEN 10 "
" WHEN 'int4' THEN 10 "
" WHEN 'bigint' THEN 19 "
" WHEN 'int8' THEN 19 "
" WHEN 'decimal' THEN numeric_precision "
" WHEN 'real' THEN 8 "
" WHEN 'float4' THEN 8 "
" WHEN 'double precision' THEN 17 "
" WHEN 'float8' THEN 17 "
" WHEN 'float' THEN 17 "
" WHEN 'numeric' THEN numeric_precision "
" WHEN '_float4' THEN 8 "
" WHEN 'oid' THEN 10 "
" WHEN '_int4' THEN 10 "
" WHEN '_int2' THEN 5 "
" WHEN 'geometry' THEN NULL "
" WHEN 'super' THEN NULL "
" WHEN 'varbyte' THEN NULL "
" WHEN 'geography' THEN NULL "
" WHEN 'intervaly2m' THEN 32 "
" WHEN 'intervald2s' THEN 64 "
" ELSE 2147483647 "
" END AS COLUMN_SIZE, "
" CAST(NULL as INTEGER) AS BUFFER_LENGTH, "
" CAST(CASE data_type "
" WHEN 'real' THEN 8 "
" WHEN 'float4' THEN 8 "
" WHEN 'double precision' THEN 17 "
" WHEN 'float8' THEN 17 "
" WHEN 'numeric' THEN numeric_scale "
" WHEN 'time' THEN 6 "
" WHEN 'time without time zone' THEN 6 "
" WHEN 'timetz' THEN 6 "
" WHEN 'time with time zone' THEN 6 "
" WHEN 'timestamp' THEN 6 "
" WHEN 'timestamp without time zone' THEN 6 "
" WHEN 'timestamptz' THEN 6 "
" WHEN 'timestamp with time zone' THEN 6 "
" WHEN 'geometry' THEN NULL "
" WHEN 'super' THEN NULL "
" WHEN 'varbyte' THEN NULL "
" WHEN 'geography' THEN NULL "
" WHEN 'intervaly2m' THEN 0 "
" WHEN 'intervald2s' THEN 6 "
" ELSE 0 "
" END AS SMALLINT) AS DECIMAL_DIGITS, "
" CAST(CASE data_type "
" WHEN 'varbyte' THEN 2 "
" WHEN 'geography' THEN 2 "
" when 'varchar' THEN 0 "
" when 'character varying' THEN 0 "
" when 'char' THEN 0 "
" when 'character' THEN 0 "
" when 'nchar' THEN 0 "
" when 'bpchar' THEN 0 "
" when 'nvarchar' THEN 0 "
" ELSE 10 "
" END AS SMALLINT) AS NUM_PREC_RADIX, "
" CAST(CASE is_nullable WHEN 'YES' THEN 1 "
" WHEN 'NO' THEN 0 "
" ELSE 2 end AS SMALLINT) AS NULLABLE, "
" REMARKS, "
" column_default AS COLUMN_DEF, "
" CAST(CASE regexp_replace(data_type, '^_.', 'ARRAY') "
" WHEN 'text' THEN 12 "
" WHEN 'bit' THEN -7 "
" WHEN 'bool' THEN -7 "
" WHEN 'boolean' THEN -7 "
" WHEN 'varchar' THEN 12 "
" WHEN 'character varying' THEN 12 "
" WHEN 'char' THEN 1 "
" WHEN 'character' THEN 1 "
" WHEN 'nchar' THEN 1 "
" WHEN 'bpchar' THEN 1 "
" WHEN 'nvarchar' THEN 12 "
" WHEN '\"char\"' THEN 1 "
" WHEN 'date' THEN 9 "
" WHEN 'time' THEN 9 "
" WHEN 'time without time zone' THEN 9 "
" WHEN 'timetz' THEN 9 "
" WHEN 'time with time zone' THEN 9 "
" WHEN 'timestamp' THEN 9 "
" WHEN 'timestamp without time zone' THEN 9 "
" WHEN 'timestamptz' THEN 9 "
" WHEN 'timestamp with time zone' THEN 9 "
" WHEN 'smallint' THEN 5 "
" WHEN 'int2' THEN 5 "
" WHEN 'integer' THEN 4 "
" WHEN 'int' THEN 4 "
" WHEN 'int4' THEN 4 "
" WHEN 'bigint' THEN -5 "
" WHEN 'int8' THEN -5 "
" WHEN 'decimal' THEN 3 "
" WHEN 'real' THEN 7 "
" WHEN 'float4' THEN 7 "
" WHEN 'double precision' THEN 8 "
" WHEN 'float8' THEN 8 "
" WHEN 'float' THEN 6 "
" WHEN 'numeric' THEN 2 "
" WHEN 'bytea' THEN -2 "
" WHEN 'oid' THEN -5 "
" WHEN 'name' THEN 12 "
" WHEN 'ARRAY' THEN 2003 "
" WHEN 'geometry' THEN -4 "
" WHEN 'super' THEN -1 "
" WHEN 'varbyte' THEN -4 "
" WHEN 'geography' THEN -4 "
" WHEN 'intervaly2m' THEN 107 "
" WHEN 'intervald2s' THEN 110 "
" ELSE 0 END AS SMALLINT) AS SQL_DATA_TYPE, "
" CAST(CASE data_type "
" WHEN 'date' THEN 1 "
" WHEN 'time' THEN 2 "
" WHEN 'time without time zone' THEN 2 "
" WHEN 'timetz' THEN 2 "
" WHEN 'time with time zone' THEN 2 "
" WHEN 'timestamp with time zone' THEN 3 "
" WHEN 'timestamptz' THEN 3 "
" WHEN 'timestamp' THEN 3 "
" WHEN 'timestamp without time zone' THEN 3 "
" ELSE NULL END AS SMALLINT) as SQL_DATETIME_SUB, "
" CASE data_type "
" WHEN 'int4' THEN 10 "
" WHEN 'bit' THEN 1 "
" WHEN 'bool' THEN 1 "
" WHEN 'boolean' THEN 1 "
" WHEN 'varchar' THEN character_maximum_length "
" WHEN 'character varying' THEN character_maximum_length "
" WHEN 'char' THEN character_maximum_length "
" WHEN 'character' THEN character_maximum_length "
" WHEN 'nchar' THEN character_maximum_length "
" WHEN 'bpchar' THEN character_maximum_length "
" WHEN 'nvarchar' THEN character_maximum_length "
" WHEN 'date' THEN 13 "
" WHEN 'time' THEN 15 "
" WHEN 'time without time zone' THEN 15 "
" WHEN 'timetz' THEN 21 "
" WHEN 'time with time zone' THEN 21 "
" WHEN 'timestamp' THEN 29 "
" WHEN 'timestamp without time zone' THEN 29 "
" WHEN 'timestamptz' THEN 35 "
" WHEN 'timestamp with time zone' THEN 35 "
" WHEN 'smallint' THEN 5 "
" WHEN 'int2' THEN 5 "
" WHEN 'integer' THEN 10 "
" WHEN 'int' THEN 10 "
" WHEN 'int4' THEN 10 "
" WHEN 'bigint' THEN 19 "
" WHEN 'int8' THEN 19 "
" WHEN 'decimal' THEN numeric_precision "
" WHEN 'real' THEN 8 "
" WHEN 'float4' THEN 8 "
" WHEN 'double precision' THEN 17 "
" WHEN 'float8' THEN 17 "
" WHEN 'float' THEN 17 "
" WHEN 'numeric' THEN numeric_precision "
" WHEN '_float4' THEN 8 "
" WHEN 'oid' THEN 10 "
" WHEN '_int4' THEN 10 "
" WHEN '_int2' THEN 5 "
" WHEN 'geometry' THEN NULL "
" WHEN 'super' THEN NULL "
" WHEN 'varbyte' THEN NULL "
" WHEN 'geography' THEN NULL "
" WHEN 'intervaly2m' THEN 32 "
" WHEN 'intervald2s' THEN 64 "
" ELSE 2147483647 "
" END AS CHAR_OCTET_LENGTH, "
" ordinal_position AS ORDINAL_POSITION, "
" is_nullable AS IS_NULLABLE "
" FROM PG_CATALOG.svv_all_columns ");
result.append(" WHERE true ");
result.append(catalogFilter);
filterClause[0] = '\0';
addLikeOrEqualFilterCondition(pStmt, filterClause, "schema_name", (char *)pSchemaName, cbSchemaName);
addLikeOrEqualFilterCondition(pStmt, filterClause, "table_name", (char *)pTableName, cbTableName);
addLikeOrEqualFilterCondition(pStmt, filterClause, "COLUMN_NAME", (char *)pColumnName, cbColumnName);
result.append(filterClause);
result.append(" ORDER BY TABLE_CAT, TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION ");
rs_strncpy(pszCatalogQuery, result.c_str(), MAX_CATALOG_QUERY_LEN);
}