in src/main/java/com/amazon/redshift/jdbc/RedshiftDatabaseMetaData.java [2821:3089]
private String buildUniversalSchemaColumnsQuery(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,"
+ " table_schema 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 2013 "
+ " WHEN 'time with time zone' THEN 2013 "
+ " WHEN 'timestamp' THEN 93"
+ " WHEN 'timestamp without time zone' THEN 93"
+ " WHEN 'timestamptz' THEN 2014"
+ " WHEN 'timestamp with time zone' THEN 2014"
+ " 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 -16 "
+ " WHEN 'varbyte' THEN -4 "
+ " ELSE 1111 END AS SMALLINT) AS DATA_TYPE,"
+ " COALESCE("
+ " domain_name,"
+ " 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"
+ " ELSE " + unknownColumnSize
+ " END AS COLUMN_SIZE,"
+ " NULL AS BUFFER_LENGTH,"
+ " 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"
+ " ELSE 0"
+ " END AS DECIMAL_DIGITS,"
+ " CASE data_type"
+ " WHEN 'varbyte' THEN 2"
+ " ELSE 10"
+ " END AS NUM_PREC_RADIX,"
+ " CASE is_nullable WHEN 'YES' THEN 1"
+ " WHEN 'NO' THEN 0"
+ " ELSE 2 end 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 91"
+ " WHEN 'time' THEN 92 "
+ " WHEN 'time without time zone' THEN 92 "
+ " WHEN 'timetz' THEN 2013 "
+ " WHEN 'time with time zone' THEN 2013 "
+ " WHEN 'timestamp' THEN 93"
+ " WHEN 'timestamp without time zone' THEN 93"
+ " WHEN 'timestamptz' THEN 2014"
+ " WHEN 'timestamp with time zone' THEN 2014"
+ " 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 -16"
+ " WHEN 'varbyte' THEN -4"
+ " ELSE 1111 END AS SMALLINT) AS SQL_DATA_TYPE,"
+ " CAST(NULL 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"
+ " ELSE " + unknownColumnSize
+ " END AS CHAR_OCTET_LENGTH,"
+ " ordinal_position AS ORDINAL_POSITION,"
+ " is_nullable AS IS_NULLABLE,"
+ " NULL AS SCOPE_CATALOG,"
+ " NULL AS SCOPE_SCHEMA,"
+ " NULL AS SCOPE_TABLE,"
+ " CASE"
+ " WHEN domain_name is not null THEN data_type"
+ " END AS SOURCE_DATA_TYPE,"
+ " CASE WHEN left(column_default, 10) = '\\\"identity\\\"' THEN 'YES'"
+ " WHEN left(column_default, 16) = 'default_identity' THEN 'YES' "
+ " ELSE 'NO' END AS IS_AUTOINCREMENT,"
+ " IS_AUTOINCREMENT AS IS_GENERATEDCOLUMN"
+ " FROM svv_columns");
result.append( " WHERE true ");
result.append(getCatalogFilterCondition(catalog));
if (schemaPattern != null && !schemaPattern.isEmpty()) {
result.append(" AND table_schema LIKE " + escapeQuotes(schemaPattern));
}
if (tableNamePattern != null && !tableNamePattern.isEmpty()) {
result.append(" AND table_name LIKE " + escapeQuotes(tableNamePattern));
}
if (columnNamePattern != null && !columnNamePattern.isEmpty()) {
result.append(" AND COLUMN_NAME LIKE " + escapeQuotes(columnNamePattern));
}
result.append(" ORDER BY table_schem,table_name,ORDINAL_POSITION ");
return result.toString();
}