in src/main/java/com/amazon/redshift/jdbc/RedshiftDatabaseMetaData.java [2557:2819]
private String buildUniversalAllSchemaColumnsQuery(String catalog,
String schemaPattern,
String tableNamePattern,
String columnNamePattern) throws SQLException {
final String unknownColumnSize = "2147483647";
StringBuilder result = new StringBuilder(8192);
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 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, "
+ " 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 2147483647 "
+ " 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 2147483647 "
+ " 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, "
+ " data_type 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 PG_CATALOG.svv_all_columns ");
result.append( " WHERE true ");
result.append(getCatalogFilterCondition(catalog, false, null));
if (schemaPattern != null && !schemaPattern.isEmpty()) {
result.append(" AND schema_name 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_CAT, TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION ");
return result.toString();
}