in src/main/java/com/amazon/redshift/jdbc/RedshiftDatabaseMetaData.java [1015:1558]
public ResultSet getProcedureColumns(String catalog, String schemaPattern,
String procedureNamePattern, String columnNamePattern) throws SQLException {
String sql;
final String unknownColumnSize = "2147483647";
StringBuilder procedureColQuery = new StringBuilder();
if (RedshiftLogger.isEnable())
connection.getLogger().logFunction(true, catalog, schemaPattern, procedureNamePattern, columnNamePattern);
procedureColQuery.append(
"SELECT PROCEDURE_CAT , PROCEDURE_SCHEM , PROCEDURE_NAME, COLUMN_NAME, "
+ " COLUMN_TYPE, DATA_TYPE, TYPE_NAME, COLUMN_SIZE AS PRECISION, LENGTH , DECIMAL_DIGITS AS SCALE, "
+ " NUM_PREC_RADIX AS RADIX, NULLABLE, REMARKS, COLUMN_DEF, SQL_DATA_TYPE, SQL_DATETIME_SUB, "
+ " CHAR_OCTET_LENGTH, ORDINAL_POSITION, IS_NULLABLE, SPECIFIC_NAME "
+ " FROM (");
procedureColQuery.append("SELECT current_database() AS PROCEDURE_CAT, "
+ " n.nspname as PROCEDURE_SCHEM, "
+ " p.proname AS PROCEDURE_NAME, "
+ " CAST(CASE ((array_upper(proargnames, 0) - array_lower(proargnames, 0)) > 0) "
+ " WHEN 't' THEN proargnames[array_upper(proargnames, 1)] "
+ " ELSE '' "
+ " END AS VARCHAR(256)) AS COLUMN_NAME, "
+ " CAST(CASE p.proretset "
+ " WHEN 't' THEN 3 "
+ " ELSE 0 "
+ " END AS SMALLINT) AS COLUMN_TYPE, "
+ " CAST(CASE pg_catalog.format_type(p.prorettype, NULL) "
+ " 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 'char' THEN 1 "
+ " WHEN 'character' THEN 1 "
+ " WHEN 'nchar' THEN 1 "
+ " WHEN 'bpchar' THEN 1 "
+ " WHEN 'nvarchar' THEN 12 "
+ " 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 'real' THEN 7 "
+ " WHEN 'float4' THEN 7 "
+ " WHEN 'double precision' THEN 6 "
+ " WHEN 'float8' THEN 6 "
+ " WHEN 'float' THEN 6 "
+ " WHEN 'decimal' THEN 3 "
+ " WHEN 'numeric' THEN 2 "
+ " WHEN '_float4' THEN 2003 "
+ " WHEN '_aclitem' THEN 2003 "
+ " WHEN '_text' THEN 2003 "
+ " WHEN 'bytea' THEN -2 "
+ " WHEN 'oid' THEN -5 "
+ " WHEN 'name' THEN 12 "
+ " WHEN '_int4' THEN 2003 "
+ " WHEN '_int2' THEN 2003 "
+ " WHEN 'ARRAY' THEN 2003 "
+ " WHEN 'geometry' THEN -4 "
+ " WHEN 'super' THEN -16 "
+ " WHEN 'varbyte' THEN -4 "
+ " ELSE 1111 "
+ " END AS SMALLINT) AS DATA_TYPE, "
+ " pg_catalog.format_type(p.prorettype, NULL) AS TYPE_NAME, "
+ " CASE pg_catalog.format_type(p.prorettype, NULL) "
+ " WHEN 'text' THEN NULL "
+ " WHEN 'varchar' THEN NULL "
+ " WHEN 'character varying' THEN NULL "
+ " WHEN '\"char\"' THEN NULL "
+ " WHEN 'character' THEN NULL "
+ " WHEN 'nchar' THEN NULL "
+ " WHEN 'bpchar' THEN NULL "
+ " WHEN 'nvarchar' THEN NULL "
+ " WHEN 'date' THEN 10 "
+ " 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 38 "
+ " WHEN 'real' THEN 24 "
+ " WHEN 'float4' THEN 53 "
+ " WHEN 'double precision' THEN 53 "
+ " WHEN 'float8' THEN 53 "
+ " WHEN 'float' THEN 53 "
+ " WHEN 'geometry' THEN NULL "
+ " WHEN 'super' THEN 4194304 "
+ " WHEN 'varbyte' THEN NULL "
+ " ELSE " + unknownColumnSize
+ " END AS COLUMN_SIZE, "
+ " CASE pg_catalog.format_type(p.prorettype, NULL) "
+ " WHEN 'text' THEN NULL "
+ " WHEN 'varchar' THEN NULL "
+ " WHEN 'character varying' THEN NULL "
+ " WHEN '\"char\"' THEN NULL "
+ " WHEN 'character' THEN NULL "
+ " WHEN 'nchar' THEN NULL "
+ " WHEN 'bpchar' THEN NULL "
+ " WHEN 'nvarchar' THEN NULL "
+ " WHEN 'date' THEN 6 "
+ " 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 6 "
+ " WHEN 'timestamp without time zone' THEN 6 "
+ " WHEN 'timestamptz' THEN 35 "
+ " WHEN 'timestamp with time zone' THEN 35 "
+ " WHEN 'smallint' THEN 2 "
+ " WHEN 'int2' THEN 2 "
+ " WHEN 'integer' THEN 4 "
+ " WHEN 'int' THEN 4 "
+ " WHEN 'int4' THEN 4 "
+ " WHEN 'bigint' THEN 20 "
+ " WHEN 'int8' THEN 20 "
+ " WHEN 'decimal' THEN 8 "
+ " WHEN 'real' THEN 4 "
+ " WHEN 'float4' THEN 8 "
+ " WHEN 'double precision' THEN 8 "
+ " WHEN 'float8' THEN 8 "
+ " WHEN 'float' THEN 8 "
+ " WHEN 'geometry' THEN NULL "
+ " WHEN 'super' THEN 4194304 "
+ " WHEN 'varbyte' THEN NULL "
+ " END AS LENGTH, "
+ " CAST(CASE pg_catalog.format_type(p.prorettype, NULL) "
+ " WHEN 'smallint' THEN 0 "
+ " WHEN 'int2' THEN 0 "
+ " WHEN 'integer' THEN 0 "
+ " WHEN 'int' THEN 0 "
+ " WHEN 'int4' THEN 0 "
+ " WHEN 'bigint' THEN 0 "
+ " WHEN 'int8' THEN 0 "
+ " WHEN 'decimal' THEN 0 "
+ " WHEN 'real' THEN 8 "
+ " WHEN 'float4' THEN 8 "
+ " WHEN 'double precision' THEN 17 "
+ " WHEN 'float' THEN 17 "
+ " WHEN 'float8' THEN 17 "
+ " 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 "
+ " ELSE NULL END AS SMALLINT) AS DECIMAL_DIGITS, "
+ " 10 AS NUM_PREC_RADIX, "
+ " CAST(2 AS SMALLINT) AS NULLABLE, "
+ " CAST('' AS VARCHAR(256)) AS REMARKS, "
+ " NULL AS COLUMN_DEF, "
+ " CAST(CASE pg_catalog.format_type(p.prorettype, NULL)"
+ " 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 'char' THEN 1 "
+ " WHEN 'character' THEN 1 "
+ " WHEN 'nchar' THEN 1 "
+ " WHEN 'bpchar' THEN 1 "
+ " WHEN 'nvarchar' THEN 12 "
+ " 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 'real' THEN 7 "
+ " WHEN 'float4' THEN 7 "
+ " WHEN 'double precision' THEN 6 "
+ " WHEN 'float8' THEN 6 "
+ " WHEN 'float' THEN 6 "
+ " WHEN 'decimal' THEN 3 "
+ " 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 "
+ " END AS SMALLINT) AS SQL_DATA_TYPE, "
+ " CAST(NULL AS SMALLINT) AS SQL_DATETIME_SUB, "
+ " CAST(NULL AS SMALLINT) AS CHAR_OCTET_LENGTH, "
+ " CAST(0 AS SMALLINT) AS ORDINAL_POSITION, "
+ " CAST('' AS VARCHAR(256)) AS IS_NULLABLE, "
+ " p.proname || '_' || p.prooid AS SPECIFIC_NAME, "
+ " p.prooid as PROOID, "
+ " -1 AS PROARGINDEX "
+ " FROM pg_catalog.pg_proc_info p LEFT JOIN pg_namespace n ON n.oid = p.pronamespace "
+ " WHERE pg_catalog.format_type(p.prorettype, NULL) != 'void' ");
procedureColQuery.append(getCatalogFilterCondition(catalog));
if (schemaPattern != null && !schemaPattern.isEmpty()) {
procedureColQuery.append(" AND n.nspname LIKE " + escapeQuotes(schemaPattern));
}
if (procedureNamePattern != null && !procedureNamePattern.isEmpty()) {
procedureColQuery.append(" AND proname LIKE " + escapeQuotes(procedureNamePattern));
}
if (columnNamePattern != null && !columnNamePattern.isEmpty()) {
procedureColQuery.append(" AND COLUMN_NAME LIKE " + escapeQuotes(columnNamePattern));
}
procedureColQuery.append(" UNION ALL ");
procedureColQuery.append(" SELECT DISTINCT current_database() AS PROCEDURE_CAT, "
+ " PROCEDURE_SCHEM, "
+ " PROCEDURE_NAME, "
+ "CAST(CASE (char_length(COLUMN_NAME) > 0) WHEN 't' THEN COLUMN_NAME "
+ "ELSE '' "
+ "END AS VARCHAR(256)) AS COLUMN_NAME, "
+ " CAST( CASE COLUMN_TYPE "
+ " WHEN 105 THEN 1 "
+ " WHEN 98 THEN 2 "
+ " WHEN 111 THEN 4 "
+ " ELSE 0 END AS SMALLINT) AS COLUMN_TYPE, "
+ " CAST(CASE DATA_TYPE "
+ " 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 'char' THEN 1 "
+ " WHEN 'character' THEN 1 "
+ " WHEN 'nchar' THEN 1 "
+ " WHEN 'bpchar' THEN 1 "
+ " WHEN 'nvarchar' THEN 12 "
+ " 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 'real' THEN 7 "
+ " WHEN 'float4' THEN 7 "
+ " WHEN 'double precision' THEN 6 "
+ " WHEN 'float8' THEN 6 "
+ " WHEN 'float' THEN 6 "
+ " WHEN 'decimal' THEN 3 "
+ " 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, "
+ " TYPE_NAME, "
+ " CASE COLUMN_SIZE "
+ " WHEN 'text' THEN COLUMN_BYTES "
+ " WHEN 'varchar' THEN COLUMN_BYTES "
+ " WHEN 'character varying' THEN COLUMN_BYTES "
+ " WHEN '\"char\"' THEN COLUMN_BYTES "
+ " WHEN 'character' THEN COLUMN_BYTES "
+ " WHEN 'nchar' THEN COLUMN_BYTES "
+ " WHEN 'bpchar' THEN COLUMN_BYTES "
+ " WHEN 'nvarchar' THEN COLUMN_BYTES "
+ " WHEN 'date' THEN 10 "
+ " 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 6 "
+ " WHEN 'timestamp without time zone' THEN 6 "
+ " 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 38 "
+ " WHEN 'real' THEN 24 "
+ " WHEN 'float4' THEN 53 "
+ " WHEN 'double precision' THEN 53 "
+ " WHEN 'float8' THEN 53 "
+ " WHEN 'float' THEN 53 "
+ " WHEN 'numeric' THEN NUMERIC_PRECISION "
+ " WHEN 'geometry' THEN NULL "
+ " WHEN 'super' THEN 4194304 "
+ " WHEN 'varbyte' THEN NULL "
+ " ELSE " + unknownColumnSize
+ " END AS COLUMN_SIZE, "
+ " CASE LENGTH "
+ " WHEN 'text' THEN COLUMN_BYTES "
+ " WHEN 'varchar' THEN COLUMN_BYTES "
+ " WHEN 'character varying' THEN COLUMN_BYTES "
+ " WHEN '\"char\"' THEN COLUMN_BYTES "
+ " WHEN 'character' THEN COLUMN_BYTES "
+ " WHEN 'nchar' THEN COLUMN_BYTES "
+ " WHEN 'bpchar' THEN COLUMN_BYTES "
+ " WHEN 'nvarchar' THEN COLUMN_BYTES "
+ " WHEN 'date' THEN 6 "
+ " 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 'smallint' THEN 2 "
+ " WHEN 'int2' THEN 2 "
+ " WHEN 'integer' THEN 4 "
+ " WHEN 'int' THEN 4 "
+ " WHEN 'int4' THEN 4 "
+ " WHEN 'bigint' THEN 20 "
+ " WHEN 'int8' THEN 20 "
+ " WHEN 'decimal' THEN 8 "
+ " WHEN 'real' THEN 4 "
+ " WHEN 'float4' THEN 8 "
+ " WHEN 'double precision' THEN 8 "
+ " WHEN 'float8' THEN 8 "
+ " WHEN 'float' THEN 8 "
+ " WHEN 'geometry' THEN NULL "
+ " WHEN 'super' THEN 4194304 "
+ " WHEN 'varbyte' THEN NULL "
+ " END AS LENGTH, "
+ " CAST(CASE DECIMAL_DIGITS "
+ " WHEN 'smallint' THEN 0 "
+ " WHEN 'int2' THEN 0 "
+ " WHEN 'integer' THEN 0 "
+ " WHEN 'int' THEN 0 "
+ " WHEN 'int4' THEN 0 "
+ " WHEN 'bigint' THEN 0 "
+ " WHEN 'int8' THEN 0 "
+ " WHEN 'decimal' THEN 0 "
+ " WHEN 'real' THEN 8 "
+ " WHEN 'float4' THEN 8 "
+ " WHEN 'double precision' THEN 17 "
+ " WHEN 'float' 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 "
+ " ELSE NULL END AS SMALLINT) AS DECIMAL_DIGITS, "
+ " 10 AS NUM_PREC_RADIX, "
+ " CAST(2 AS SMALLINT) AS NULLABLE, "
+ " CAST(''AS VARCHAR(256)) AS REMARKS, "
+ " NULL AS COLUMN_DEF,"
+ " CAST( CASE SQL_DATA_TYPE"
+ " 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 'char' THEN 1 "
+ " WHEN 'character' THEN 1 "
+ " WHEN 'nchar' THEN 1 "
+ " WHEN 'bpchar' THEN 1 "
+ " WHEN 'nvarchar' THEN 12 "
+ " 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 'real' THEN 7 "
+ " WHEN 'float4' THEN 7 "
+ " WHEN 'double precision' THEN 6 "
+ " WHEN 'float8' THEN 6 "
+ " WHEN 'float' THEN 6 "
+ " WHEN 'decimal' THEN 3 "
+ " 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 "
+ " END AS SMALLINT) AS SQL_DATA_TYPE, "
+ " CAST(NULL AS SMALLINT) AS SQL_DATETIME_SUB, "
+ " CAST(NULL AS SMALLINT) AS CHAR_OCTET_LENGTH, "
+ " PROARGINDEX AS ORDINAL_POSITION, "
+ " CAST(''AS VARCHAR(256)) AS IS_NULLABLE, "
+ " SPECIFIC_NAME, PROOID, PROARGINDEX "
+ " FROM ( "
+ " SELECT current_database() AS PROCEDURE_CAT,"
+ " n.nspname AS PROCEDURE_SCHEM, "
+ " proname AS PROCEDURE_NAME, "
+ " CASE WHEN (proallargtypes is NULL) THEN proargnames[pos+1] "
+ " ELSE proargnames[pos] END AS COLUMN_NAME,"
+ " CASE WHEN proargmodes is NULL THEN 105 "
+ " ELSE CAST(proargmodes[pos] AS INT) END AS COLUMN_TYPE, "
+ " CASE WHEN proallargtypes is NULL THEN pg_catalog.format_type(proargtypes[pos], NULL)"
+ " ELSE pg_catalog.format_type(proallargtypes[pos], NULL) END AS DATA_TYPE,"
+ " CASE WHEN proallargtypes is NULL THEN pg_catalog.format_type(proargtypes[pos], NULL) "
+ " ELSE pg_catalog.format_type(proallargtypes[pos], NULL) END AS TYPE_NAME,"
+ " CASE WHEN proallargtypes is NULL THEN pg_catalog.format_type(proargtypes[pos], NULL)"
+ " ELSE pg_catalog.format_type(proallargtypes[pos], NULL) END AS COLUMN_SIZE,"
+ " CASE WHEN (proallargtypes IS NOT NULL) and prokind='p' AND proallargtypes[pos] IN (1042, 1700, 1043) "
+ " THEN (string_to_array(textin(byteaout(substring(probin from 1 for length(probin)-3))),','))[pos]::integer "
+ " WHEN (proallargtypes IS NULL) AND prokind='p' AND proargtypes[pos] IN (1042,1700,1043) "
+ " THEN (string_to_array(textin(byteaout(substring(probin FROM 1 FOR length(probin)-3))), ',')) [pos+1]::integer "
+ " END AS PROBIN_BYTES, "
+ " CASE "
+ " WHEN (PROBIN_BYTES IS NOT NULL) "
+ " AND (proallargtypes[pos] IN (1042, 1043) or proargtypes[pos] in (1042,1043)) "
+ " THEN PROBIN_BYTES-4 "
+ " END AS COLUMN_BYTES, "
+ " CASE WHEN proallargtypes is NULL THEN pg_catalog.format_type(proargtypes[pos], NULL)"
+ " ELSE pg_catalog.format_type(proallargtypes[pos], NULL) END AS LENGTH,"
+ " CASE WHEN proallargtypes is NULL THEN pg_catalog.format_type(proargtypes[pos], NULL)"
+ " ELSE pg_catalog.format_type(proallargtypes[pos], NULL) END AS DECIMAL_DIGITS,"
+ " CASE WHEN proallargtypes is NULL THEN pg_catalog.format_type(proargtypes[pos], NULL)"
+ " ELSE pg_catalog.format_type(proallargtypes[pos], NULL) END AS RADIX,"
+ " CAST(2 AS SMALLINT) AS NULLABLE,"
+ " CAST(''AS VARCHAR(256)) AS REMARKS,"
+ " CAST(NULL AS SMALLINT) AS COLUMN_DEF,"
+ " pg_catalog.format_type(proargtypes[pos], NULL) AS SQL_DATA_TYPE,"
+ " CAST(NULL AS SMALLINT) AS SQL_DATETIME_SUB,"
+ " pg_catalog.format_type(proargtypes[pos], NULL) AS CHAR_OCTET_LENGTH,"
+ " CASE WHEN (proallargtypes is NULL) THEN pos+1"
+ " WHEN pos = array_upper(proallargtypes, 1) THEN 0"
+ " ELSE pos END AS ORDINAL_POSITION,"
+ " CAST('' AS VARCHAR(256)) AS IS_NULLABLE,"
+ " p.prooid AS PROOID,"
+ " CASE WHEN (proallargtypes is NULL) THEN pos+1"
+ " WHEN prokind = 'f' AND pos = array_upper(proallargtypes, 1) THEN 0"
+ " ELSE pos END AS PROARGINDEX, "
+ " CASE WHEN (proallargtypes IS NULL AND proargtypes[pos] = 1700 AND prokind='p') OR (proallargtypes IS NOT NULL AND proallargtypes[pos] = 1700 AND prokind='p' AND proallargtypes[pos] = 1700) THEN (PROBIN_BYTES-4)/65536 END as NUMERIC_PRECISION, "
+ " CASE WHEN (proallargtypes IS NULL AND proargtypes[pos] = 1700 AND prokind='p') OR (proallargtypes IS NOT NULL AND proallargtypes[pos] = 1700 AND prokind='p' AND proallargtypes[pos] = 1700) THEN (((PROBIN_BYTES::numeric-4)/65536 - (PROBIN_BYTES-4)/65536) * 65536)::INT END as NUMERIC_SCALE, "
+ " p.proname || '_' || p.prooid AS SPECIFIC_NAME "
+ " FROM (pg_catalog.pg_proc_info p LEFT JOIN pg_namespace n"
+ " ON n.oid = p.pronamespace)"
+ " LEFT JOIN (SELECT "
+ " CASE WHEN (proallargtypes IS NULL) "
+ " THEN generate_series(array_lower(proargnames, 1), array_upper(proargnames, 1))-1"
+ " ELSE generate_series(array_lower(proargnames, 1), array_upper(proargnames, 1)+1)-1 "
+ " END AS pos"
+ " FROM pg_catalog.pg_proc_info p ) AS s ON (pos >= 0)");
procedureColQuery.append(" WHERE true ");
procedureColQuery.append(getCatalogFilterCondition(catalog));
if (schemaPattern != null && !schemaPattern.isEmpty()) {
procedureColQuery.append(" AND n.nspname LIKE " + escapeQuotes(schemaPattern));
}
if (procedureNamePattern != null && !procedureNamePattern.isEmpty()) {
procedureColQuery.append(" AND proname LIKE " + escapeQuotes(procedureNamePattern));
}
if (columnNamePattern != null && !columnNamePattern.isEmpty()) {
procedureColQuery.append(" AND COLUMN_NAME LIKE " + escapeQuotes(columnNamePattern));
}
procedureColQuery.append(" ) AS INPUT_PARAM_TABLE"
+ " WHERE ORDINAL_POSITION IS NOT NULL"
+ " ) AS RESULT_SET WHERE (DATA_TYPE != 1111 OR (TYPE_NAME IS NOT NULL AND TYPE_NAME != '-'))"
+ " ORDER BY PROCEDURE_CAT ,PROCEDURE_SCHEM,"
+ " PROCEDURE_NAME, PROOID, PROARGINDEX, COLUMN_TYPE DESC");
sql = procedureColQuery.toString();
ResultSet rs = createMetaDataStatement().executeQuery(sql);
if (RedshiftLogger.isEnable())
connection.getLogger().logFunction(false, rs);
return rs;
}