}
@Override
public ResultSet getProcedureColumns(String catalog, String schemaPattern, String procedureNamePattern, String columnNamePattern) throws SQLException {
Registry reg = connection.getRegistry();
ResultField[] resultFields = new ResultField[20];
List<Object[]> results = new ArrayList<>();
resultFields[0] = new ResultField("PROCEDURE_CAT", 0, (short)0, reg.loadType("text"), (short)0, 0, Format.Binary);
resultFields[1] = new ResultField("PROCEDURE_SCHEM", 0, (short)0, reg.loadType("text"), (short)0, 0, Format.Binary);
resultFields[2] = new ResultField("PROCEDURE_NAME", 0, (short)0, reg.loadType("text"), (short)0, 0, Format.Binary);
resultFields[3] = new ResultField("COLUMN_NAME", 0, (short)0, reg.loadType("text"), (short)0, 0, Format.Binary);
resultFields[4] = new ResultField("COLUMN_TYPE", 0, (short)0, reg.loadType("int2"), (short)0, 0, Format.Binary);
resultFields[5] = new ResultField("DATA_TYPE", 0, (short)0, reg.loadType("int2"), (short)0, 0, Format.Binary);
resultFields[6] = new ResultField("TYPE_NAME", 0, (short)0, reg.loadType("text"), (short)0, 0, Format.Binary);
resultFields[7] = new ResultField("PRECISION", 0, (short)0, reg.loadType("int4"), (short)0, 0, Format.Binary);
resultFields[8] = new ResultField("LENGTH", 0, (short)0, reg.loadType("int4"), (short)0, 0, Format.Binary);
resultFields[9] = new ResultField("SCALE", 0, (short)0, reg.loadType("int2"), (short)0, 0, Format.Binary);
resultFields[10] = new ResultField("RADIX", 0, (short)0, reg.loadType("int2"), (short)0, 0, Format.Binary);
resultFields[11] = new ResultField("NULLABLE", 0, (short)0, reg.loadType("int2"), (short)0, 0, Format.Binary);
resultFields[12] = new ResultField("REMARKS", 0, (short)0, reg.loadType("text"), (short)0, 0, Format.Binary);
resultFields[13] = new ResultField("COLUMN_DEF", 0, (short)0, reg.loadType("text"), (short)0, 0, Format.Binary);
resultFields[14] = new ResultField("SQL_DATA_TYPE", 0, (short)0, reg.loadType("int4"), (short)0, 0, Format.Binary);
resultFields[15] = new ResultField("SQL_DATETIME_SUB", 0, (short)0, reg.loadType("int4"), (short)0, 0, Format.Binary);
resultFields[16] = new ResultField("CHAR_OCTECT_LENGTH", 0, (short)0, reg.loadType("int4"), (short)0, 0, Format.Binary);
resultFields[17] = new ResultField("ORDINAL_POSITION", 0, (short)0, reg.loadType("int4"), (short)0, 0, Format.Binary);
resultFields[18] = new ResultField("IS_NULLABLE", 0, (short)0, reg.loadType("text"), (short)0, 0, Format.Binary);
resultFields[19] = new ResultField("SPECIFIC_NAME", 0, (short)0, reg.loadType("text"), (short)0, 0, Format.Binary);
StringBuilder sql = new StringBuilder();
List<Object> params = new ArrayList<>();
sql.append(
"SELECT n.nspname,p.proname,p.prorettype,p.proargtypes, t.typtype,t.typrelid, p.proargnames, " +
" p.proargmodes, p.proallargtypes, p.oid " +
" FROM pg_catalog.pg_proc p, pg_catalog.pg_namespace n, pg_catalog.pg_type t " +
" WHERE p.pronamespace=n.oid AND p.prorettype=t.oid ");
if (schemaPattern != null) {
sql.append(" AND n.nspname LIKE ?");
params.add(schemaPattern);
}
if (!isNullOrEmpty(procedureNamePattern)) {
sql.append(" AND p.proname LIKE ?");
params.add(procedureNamePattern);
}
sql.append(" ORDER BY n.nspname, p.proname, p.oid::text ");
try (PGResultSet rs = execForResultSet(sql.toString(), params)) {
while (rs.next()) {
String schema = rs.getString("nspname");
String procedureName = rs.getString("proname");
String specificName = rs.getString("proname") + "_" + rs.getString("oid");
Type returnType = reg.loadType(rs.getInt("prorettype"));
String returnTypeType = rs.getString("typtype");
int returnTypeRelId = rs.getInt("typrelid");
Integer[] argTypeIds = rs.getObject("proargtypes", Integer[].class);
String[] argNames = rs.getObject("proargnames", String[].class);
String[] argModes = rs.getObject("proargmodes", String[].class);
Integer[] allArgTypeIds = rs.getObject("proallargtypes", Integer[].class);
int numArgs = allArgTypeIds != null ? allArgTypeIds.length : argTypeIds.length;
// decide if we are returning a single column result.
if (returnTypeType.equals("b") ||
returnTypeType.equals("d") ||
(returnTypeType.equals("p") && argModes == null)) {
Object[] row = new Object[resultFields.length];
row[0] = null;
row[1] = schema;
row[2] = procedureName;
row[3] = "returnValue";
row[4] = DatabaseMetaData.procedureColumnReturn;
row[5] = SQLTypeMetaData.getSQLType(returnType);
row[6] = SQLTypeMetaData.getTypeName(returnType, null, 0);
row[7] = null;
row[8] = null;
row[9] = null;
row[10] = null;
row[11] = DatabaseMetaData.procedureNullableUnknown;
row[12] = null;
row[17] = 0;
row[18] = "";
row[19] = specificName;
results.add(row);
}
// Add a row for each argument.
for (int i = 0; i < numArgs; i++) {
Object[] row = new Object[resultFields.length];
row[0] = null;
row[1] = schema;
row[2] = procedureName;
if (argNames != null) {
row[3] = argNames[i];
}
else {
row[3] = "$" + (i + 1);
}
int columnMode = DatabaseMetaData.procedureColumnIn;
if (argModes != null) {
if (argModes[i].equals("o")) {
columnMode = DatabaseMetaData.procedureColumnOut;
}
else if (argModes[i].equals("b")) {
columnMode = DatabaseMetaData.procedureColumnInOut;
}
}
row[4] = columnMode;
Type argType;
if (allArgTypeIds != null) {
argType = reg.loadType(allArgTypeIds[i].intValue());
}
else {
argType = reg.loadType(argTypeIds[i].intValue());
}
row[5] = SQLTypeMetaData.getSQLType(argType);
row[6] = argType.getJavaType(argType.getPreferredFormat(), connection.getTypeMap()).getName();
row[7] = null;
row[8] = null;
row[9] = null;
row[10] = null;
row[11] = DatabaseMetaData.procedureNullableUnknown;
row[12] = null;
row[17] = i + 1;
row[18] = "";
row[19] = specificName;
results.add(row);
}
// if we are returning a multi-column result.
if (returnTypeType.equals("c") ||
(returnTypeType.equals("p") && argModes != null && returnTypeRelId != 0)) {
String columnsql = "SELECT a.attname,a.atttypid FROM pg_catalog.pg_attribute a WHERE a.attrelid = " + returnTypeRelId + " AND a.attnum > 0 ORDER BY a.attnum ";
try (ResultSet columnrs = connection.createStatement().executeQuery(columnsql)) {
while (columnrs.next()) {
Type columnType = reg.loadType(columnrs.getInt("atttypid"));
Object[] row = new Object[resultFields.length];
row[0] = null;
row[1] = schema;
row[2] = procedureName;