in src/main/java/com/amazon/redshift/jdbc/RedshiftDatabaseMetaData.java [3091:3376]
private String buildExternalSchemaColumnsQuery(String catalog,
String schemaPattern,
String tableNamePattern,
String columnNamePattern) throws SQLException {
final String unknownColumnSize = "2147483647";
StringBuilder result = new StringBuilder(8192);
// NOTE: Explicit cast on current_database() prevents bug where data returned from server
// has incorrect length and displays random characters. [JDBC-529]
result.append("SELECT current_database()::varchar(128) AS TABLE_CAT,"
+ " schemaname AS TABLE_SCHEM,"
+ " tablename AS TABLE_NAME,"
+ " columnname AS COLUMN_NAME,"
+ " CAST(CASE WHEN external_type = 'text' THEN 12"
+ " WHEN external_type = 'bit' THEN -7"
+ " WHEN external_type = 'bool' THEN -7"
+ " WHEN external_type = 'boolean' THEN -7"
+ " WHEN left(external_type, 7) = 'varchar' THEN 12"
+ " WHEN left(external_type, 17) = 'character varying' THEN 12"
+ " WHEN left(external_type, 4) = 'char' THEN 1"
+ " WHEN left(external_type, 9) = 'character' THEN 1"
+ " WHEN left(external_type, 5) = 'nchar' THEN 1"
+ " WHEN left(external_type, 6) = 'bpchar' THEN 1"
+ " WHEN left(external_type, 8) = 'nvarchar' THEN 12"
+ " WHEN external_type = '\"char\"' THEN 1"
+ " WHEN external_type = 'date' THEN 91"
+ " WHEN external_type = 'time' THEN 92 "
+ " WHEN external_type = 'time without time zone' THEN 92 "
+ " WHEN external_type = 'timetz' THEN 2013 "
+ " WHEN external_type = 'time with time zone' THEN 2013 "
+ " WHEN external_type = 'timestamp' THEN 93"
+ " WHEN external_type = 'timestamp without time zone' THEN 93"
+ " WHEN external_type = 'timestamptz' THEN 2014"
+ " WHEN external_type = 'timestamp with time zone' THEN 2014"
+ " WHEN external_type = 'smallint' THEN 5"
+ " WHEN external_type = 'int2' THEN 5"
+ " WHEN external_type = '_int2' THEN 5"
+ " WHEN external_type = 'integer' THEN 4"
+ " WHEN external_type = 'int' THEN 4"
+ " WHEN external_type = 'int4' THEN 4"
+ " WHEN external_type = '_int4' THEN 4"
+ " WHEN external_type = 'bigint' THEN -5"
+ " WHEN external_type = 'int8' THEN -5"
+ " WHEN left(external_type, 7) = 'decimal' THEN 2"
+ " WHEN external_type = 'real' THEN 7"
+ " WHEN external_type = 'float4' THEN 7"
+ " WHEN external_type = '_float4' THEN 7"
+ " WHEN external_type = 'double' THEN 8"
+ " WHEN external_type = 'double precision' THEN 8"
+ " WHEN external_type = 'float8' THEN 8"
+ " WHEN external_type = '_float8' THEN 8"
+ " WHEN external_type = 'float' THEN 6"
+ " WHEN left(external_type, 7) = 'numeric' THEN 2"
+ " WHEN external_type = 'bytea' THEN -2"
+ " WHEN external_type = 'oid' THEN -5"
+ " WHEN external_type = 'name' THEN 12"
+ " WHEN external_type = 'ARRAY' THEN 2003"
+ " WHEN external_type = 'geometry' THEN -4"
+ " WHEN external_type = 'super' THEN -16"
+ " WHEN external_type = 'varbyte' THEN -4"
+ " ELSE 1111 END AS SMALLINT) AS DATA_TYPE,"
+ " CASE WHEN left(external_type, 17) = 'character varying' THEN 'varchar'"
+ " WHEN left(external_type, 7) = 'varchar' THEN 'varchar'"
+ " WHEN left(external_type, 4) = 'char' THEN 'char'"
+ " WHEN left(external_type, 7) = 'decimal' THEN 'numeric'"
+ " WHEN left(external_type, 7) = 'numeric' THEN 'numeric'"
+ " WHEN external_type = 'double' THEN 'double precision'"
+ " WHEN external_type = 'time without time zone' THEN 'time'"
+ " WHEN external_type = 'time with time zone' THEN 'timetz'"
+ " WHEN external_type = 'timestamp without time zone' THEN 'timestamp'"
+ " WHEN external_type = 'timestamp with time zone' THEN 'timestamptz'"
+ " ELSE external_type END AS TYPE_NAME,"
+ " CASE WHEN external_type = 'int4' THEN 10"
+ " WHEN external_type = 'bit' THEN 1"
+ " WHEN external_type = 'bool' THEN 1"
+ " WHEN external_type = 'boolean' THEN 1"
+ " WHEN left(external_type, 7) = 'varchar' "
+ " THEN CASE "
+ " WHEN regexp_instr(external_type, '\\\\(', 7) = 0 THEN '0' "
+ " ELSE regexp_substr(external_type, '[0-9]+', 7) "
+ " END::integer "
+ " WHEN left(external_type, 17) = 'character varying' "
+ " THEN CASE "
+ " WHEN regexp_instr(external_type, '\\\\(', 17) = 0 THEN '0' "
+ " ELSE regexp_substr(external_type, '[0-9]+', 17) "
+ " END::integer "
+ " WHEN left(external_type, 4) = 'char' "
+ " THEN CASE "
+ " WHEN regexp_instr(external_type, '\\\\(', 4) = 0 THEN '0' "
+ " ELSE regexp_substr(external_type, '[0-9]+', 4) "
+ " END::integer "
+ " WHEN left(external_type, 9) = 'character' "
+ " THEN CASE "
+ " WHEN regexp_instr(external_type, '\\\\(', 9) = 0 THEN '0' "
+ " ELSE regexp_substr(external_type, '[0-9]+', 9) "
+ " END::integer "
+ " WHEN left(external_type, 5) = 'nchar' "
+ " THEN CASE "
+ " WHEN regexp_instr(external_type, '\\\\(', 5) = 0 THEN '0' "
+ " ELSE regexp_substr(external_type, '[0-9]+', 5) "
+ " END::integer "
+ " WHEN left(external_type, 6) = 'bpchar' "
+ " THEN CASE "
+ " WHEN regexp_instr(external_type, '\\\\(', 6) = 0 THEN '0' "
+ " ELSE regexp_substr(external_type, '[0-9]+', 6) "
+ " END::integer "
+ " WHEN left(external_type, 8) = 'nvarchar' "
+ " THEN CASE "
+ " WHEN regexp_instr(external_type, '\\\\(', 8) = 0 THEN '0' "
+ " ELSE regexp_substr(external_type, '[0-9]+', 8) "
+ " END::integer "
+ " WHEN external_type = 'date' THEN 13 "
+ " WHEN external_type = 'time' THEN 15 "
+ " WHEN external_type = 'time without time zone' THEN 15 "
+ " WHEN external_type = 'timetz' THEN 21 "
+ " WHEN external_type = 'time with time zone' THEN 21 "
+ " WHEN external_type = 'timestamp' THEN 29 "
+ " WHEN external_type = 'timestamp without time zone' THEN 29"
+ " WHEN external_type = 'timestamptz' THEN 35"
+ " WHEN external_type = 'timestamp with time zone' THEN 35"
+ " WHEN external_type = 'smallint' THEN 5"
+ " WHEN external_type = 'int2' THEN 5"
+ " WHEN external_type = 'integer' THEN 10"
+ " WHEN external_type = 'int' THEN 10"
+ " WHEN external_type = 'int4' THEN 10"
+ " WHEN external_type = 'bigint' THEN 19"
+ " WHEN external_type = 'int8' THEN 19"
+ " WHEN left(external_type, 7) = 'decimal' THEN regexp_substr(external_type, '[0-9]+', 7)::integer"
+ " WHEN external_type = 'real' THEN 8"
+ " WHEN external_type = 'float4' THEN 8"
+ " WHEN external_type = '_float4' THEN 8"
+ " WHEN external_type = 'double' THEN 17"
+ " WHEN external_type = 'double precision' THEN 17"
+ " WHEN external_type = 'float8' THEN 17"
+ " WHEN external_type = '_float8' THEN 17"
+ " WHEN external_type = 'float' THEN 17"
+ " WHEN left(external_type, 7) = 'numeric' THEN regexp_substr(external_type, '[0-9]+', 7)::integer"
+ " WHEN external_type = '_float4' THEN 8"
+ " WHEN external_type = 'oid' THEN 10"
+ " WHEN external_type = '_int4' THEN 10"
+ " WHEN external_type = '_int2' THEN 5"
+ " WHEN external_type = 'geometry' THEN NULL"
+ " WHEN external_type = 'super' THEN NULL"
+ " WHEN external_type = 'varbyte' THEN NULL"
+ " ELSE 2147483647 END AS COLUMN_SIZE,"
+ " NULL AS BUFFER_LENGTH,"
+ " CASE WHEN external_type = 'real'THEN 8"
+ " WHEN external_type = 'float4' THEN 8"
+ " WHEN external_type = 'double' THEN 17"
+ " WHEN external_type = 'double precision' THEN 17"
+ " WHEN external_type = 'float8' THEN 17"
+ " WHEN left(external_type, 7) = 'numeric' THEN regexp_substr(external_type, '[0-9]+', 10)::integer"
+ " WHEN left(external_type, 7) = 'decimal' THEN regexp_substr(external_type, '[0-9]+', 10)::integer"
+ " WHEN external_type = 'time' THEN 6 "
+ " WHEN external_type = 'time without time zone' THEN 6 "
+ " WHEN external_type = 'timetz' THEN 6 "
+ " WHEN external_type = 'time with time zone' THEN 6 "
+ " WHEN external_type = 'timestamp' THEN 6"
+ " WHEN external_type = 'timestamp without time zone' THEN 6"
+ " WHEN external_type = 'timestamptz' THEN 6"
+ " WHEN external_type = 'timestamp with time zone' THEN 6"
+ " WHEN external_type = 'geometry' THEN NULL"
+ " WHEN external_type = 'super' THEN NULL"
+ " WHEN external_type = 'varbyte' THEN NULL"
+ " ELSE 0 END AS DECIMAL_DIGITS,"
+ " CASE WHEN external_type = 'varbyte' THEN 2"
+ " ELSE 10"
+ " END AS NUM_PREC_RADIX,"
+ " NULL AS NULLABLE,"
+ " NULL AS REMARKS,"
+ " NULL AS COLUMN_DEF,"
+ " CAST(CASE WHEN external_type = 'text' THEN 12"
+ " WHEN external_type = 'bit' THEN -7"
+ " WHEN external_type = 'bool' THEN -7"
+ " WHEN external_type = 'boolean' THEN -7"
+ " WHEN left(external_type, 7) = 'varchar' THEN 12"
+ " WHEN left(external_type, 17) = 'character varying' THEN 12"
+ " WHEN left(external_type, 4) = 'char' THEN 1"
+ " WHEN left(external_type, 9) = 'character' THEN 1"
+ " WHEN left(external_type, 5) = 'nchar' THEN 1"
+ " WHEN left(external_type, 6) = 'bpchar' THEN 1"
+ " WHEN left(external_type, 8) = 'nvarchar' THEN 12"
+ " WHEN external_type = '\"char\"' THEN 1"
+ " WHEN external_type = 'date' THEN 91"
+ " WHEN external_type = 'time' THEN 92 "
+ " WHEN external_type = 'time without time zone' THEN 92 "
+ " WHEN external_type = 'timetz' THEN 2013 "
+ " WHEN external_type = 'time with time zone' THEN 2013 "
+ " WHEN external_type = 'timestamp' THEN 93"
+ " WHEN external_type = 'timestamp without time zone' THEN 93"
+ " WHEN external_type = 'timestamptz' THEN 2014"
+ " WHEN external_type = 'timestamp with time zone' THEN 2014"
+ " WHEN external_type = 'smallint' THEN 5"
+ " WHEN external_type = 'int2' THEN 5"
+ " WHEN external_type = '_int2' THEN 5"
+ " WHEN external_type = 'integer' THEN 4"
+ " WHEN external_type = 'int' THEN 4"
+ " WHEN external_type = 'int4' THEN 4"
+ " WHEN external_type = '_int4' THEN 4"
+ " WHEN external_type = 'bigint' THEN -5"
+ " WHEN external_type = 'int8' THEN -5"
+ " WHEN left(external_type, 7) = 'decimal' THEN 3"
+ " WHEN external_type = 'real' THEN 7"
+ " WHEN external_type = 'float4' THEN 7"
+ " WHEN external_type = '_float4' THEN 7"
+ " WHEN external_type = 'double' THEN 8"
+ " WHEN external_type = 'double precision' THEN 8"
+ " WHEN external_type = 'float8' THEN 8"
+ " WHEN external_type = '_float8' THEN 8"
+ " WHEN external_type = 'float' THEN 6"
+ " WHEN left(external_type, 7) = 'numeric' THEN 2"
+ " WHEN external_type = 'bytea' THEN -2"
+ " WHEN external_type = 'oid' THEN -5"
+ " WHEN external_type = 'name' THEN 12"
+ " WHEN external_type = 'ARRAY' THEN 2003"
+ " WHEN external_type = 'geometry' THEN -4"
+ " WHEN external_type = 'super' THEN -16"
+ " WHEN external_type = 'varbyte' THEN -4"
+ " ELSE 1111 END AS SMALLINT) AS SQL_DATA_TYPE,"
+ " CAST(NULL AS SMALLINT) AS SQL_DATETIME_SUB,"
+ " CASE WHEN left(external_type, 7) = 'varchar' "
+ " THEN CASE "
+ " WHEN regexp_instr(external_type, '\\\\(', 7) = 0 THEN '0' "
+ " ELSE regexp_substr(external_type, '[0-9]+', 7) "
+ " END::integer "
+ " WHEN left(external_type, 17) = 'character varying' "
+ " THEN CASE "
+ " WHEN regexp_instr(external_type, '\\\\(', 17) = 0 THEN '0' "
+ " ELSE regexp_substr(external_type, '[0-9]+', 17) "
+ " END::integer "
+ " WHEN left(external_type, 4) = 'char' "
+ " THEN CASE "
+ " WHEN regexp_instr(external_type, '\\\\(', 4) = 0 THEN '0' "
+ " ELSE regexp_substr(external_type, '[0-9]+', 4) "
+ " END::integer "
+ " WHEN left(external_type, 9) = 'character' "
+ " THEN CASE "
+ " WHEN regexp_instr(external_type, '\\\\(', 9) = 0 THEN '0' "
+ " ELSE regexp_substr(external_type, '[0-9]+', 9) "
+ " END::integer "
+ " WHEN left(external_type, 5) = 'nchar' "
+ " THEN CASE "
+ " WHEN regexp_instr(external_type, '\\\\(', 5) = 0 THEN '0' "
+ " ELSE regexp_substr(external_type, '[0-9]+', 5) "
+ " END::integer "
+ " WHEN left(external_type, 6) = 'bpchar' "
+ " THEN CASE "
+ " WHEN regexp_instr(external_type, '\\\\(', 6) = 0 THEN '0' "
+ " ELSE regexp_substr(external_type, '[0-9]+', 6) "
+ " END::integer "
+ " WHEN left(external_type, 8) = 'nvarchar' "
+ " THEN CASE "
+ " WHEN regexp_instr(external_type, '\\\\(', 8) = 0 THEN '0' "
+ " ELSE regexp_substr(external_type, '[0-9]+', 8) "
+ " END::integer "
+ " WHEN external_type = 'string' THEN 16383"
+ " ELSE NULL END AS CHAR_OCTET_LENGTH,"
+ " columnnum AS ORDINAL_POSITION,"
+ " NULL AS IS_NULLABLE,"
+ " NULL AS SCOPE_CATALOG,"
+ " NULL AS SCOPE_SCHEMA,"
+ " NULL AS SCOPE_TABLE,"
+ " NULL AS SOURCE_DATA_TYPE,"
+ " 'NO' AS IS_AUTOINCREMENT,"
+ " 'NO' AS IS_GENERATEDCOLUMN"
+ " FROM svv_external_columns");
result.append( " WHERE true ");
result.append(getCatalogFilterCondition(catalog));
if (schemaPattern != null && !schemaPattern.isEmpty()) {
result.append(" AND schemaname LIKE " + escapeQuotes(schemaPattern));
}
if (tableNamePattern != null && !tableNamePattern.isEmpty()) {
result.append(" AND tablename LIKE " + escapeQuotes(tableNamePattern));
}
if (columnNamePattern != null && !columnNamePattern.isEmpty()) {
result.append(" AND columnname LIKE " + escapeQuotes(columnNamePattern));
}
result.append(" ORDER BY table_schem,table_name,ORDINAL_POSITION ");
return result.toString();
}