On Fri, 07 Sep 2001 01:34:46 -0400, Tom Lane wrote:
>there is still an unpatched reference to pg_description in
>getColumns(), in both jdbc1 and jdbc2.
This was introduced by Jeroen's patch (see
http://fts.postgresql.org/db/mw/msg.html?mid=1032468). Attached
is a patch that returns getColumns() to using "select
obj_description()" instead of direct access to pg_description,
as per the request by Tom.
I've incorporated Jeroen's fix to left outer join with
pg_attrdef instead of inner join, so getColumns() also returns
columns without a default value.
I have, however, not included Jeroen's attempt to combine
multiple queries into one huge multi-join query for better
performance, because:
1) I don't know how to do that using obj_description() instead
of direct access to pg_description
2) I don't think a performance improvement (if any) in this
method is very important
Because of the outer join, getColumns() will only work with a
backend >= 7.1. Since the conditional coding for 7.1/7.2 and
jdbc1/jdbc2 is already giving me headaches I didn't pursue a
pre-7.1 solution.
Regards,
René Pijlman <[EMAIL PROTECTED]>
Index: org/postgresql/jdbc1/DatabaseMetaData.java
===================================================================
RCS file:
/home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java,v
retrieving revision 1.31
diff -c -r1.31 DatabaseMetaData.java
*** org/postgresql/jdbc1/DatabaseMetaData.java 2001/09/06 12:53:15 1.31
--- org/postgresql/jdbc1/DatabaseMetaData.java 2001/09/08 21:37:44
***************
*** 1895,1913 ****
*/
public java.sql.ResultSet getColumns(String catalog, String schemaPattern, String
tableNamePattern, String columnNamePattern) throws SQLException
{
Vector v = new Vector(); // The new ResultSet tuple stuff
- Field f[] = new Field[18]; // The field descriptors for the new
ResultSet
! f[ 0] = new Field(connection, "TABLE_CAT", iVarcharOid, 32);
! f[ 1] = new Field(connection, "TABLE_SCHEM", iVarcharOid, 32);
! f[ 2] = new Field(connection, "TABLE_NAME", iVarcharOid, 32);
! f[ 3] = new Field(connection, "COLUMN_NAME", iVarcharOid, 32);
! f[ 4] = new Field(connection, "DATA_TYPE", iInt2Oid, 2);
! f[ 5] = new Field(connection, "TYPE_NAME", iVarcharOid, 32);
! f[ 6] = new Field(connection, "COLUMN_SIZE", iInt4Oid, 4);
! f[ 7] = new Field(connection, "BUFFER_LENGTH", iVarcharOid, 32);
! f[ 8] = new Field(connection, "DECIMAL_DIGITS", iInt4Oid, 4);
! f[ 9] = new Field(connection, "NUM_PREC_RADIX", iInt4Oid, 4);
f[10] = new Field(connection, "NULLABLE", iInt4Oid, 4);
f[11] = new Field(connection, "REMARKS", iVarcharOid, 32);
f[12] = new Field(connection, "COLUMN_DEF", iVarcharOid, 32);
--- 1895,1915 ----
*/
public java.sql.ResultSet getColumns(String catalog, String schemaPattern, String
tableNamePattern, String columnNamePattern) throws SQLException
{
+ // the field descriptors for the new ResultSet
+ Field f[] = new Field[18];
+ java.sql.ResultSet r; // ResultSet for the SQL query that we need to do
Vector v = new Vector(); // The new ResultSet tuple stuff
! f[0] = new Field(connection, "TABLE_CAT", iVarcharOid, 32);
! f[1] = new Field(connection, "TABLE_SCHEM", iVarcharOid, 32);
! f[2] = new Field(connection, "TABLE_NAME", iVarcharOid, 32);
! f[3] = new Field(connection, "COLUMN_NAME", iVarcharOid, 32);
! f[4] = new Field(connection, "DATA_TYPE", iInt2Oid, 2);
! f[5] = new Field(connection, "TYPE_NAME", iVarcharOid, 32);
! f[6] = new Field(connection, "COLUMN_SIZE", iInt4Oid, 4);
! f[7] = new Field(connection, "BUFFER_LENGTH", iVarcharOid, 32);
! f[8] = new Field(connection, "DECIMAL_DIGITS", iInt4Oid, 4);
! f[9] = new Field(connection, "NUM_PREC_RADIX", iInt4Oid, 4);
f[10] = new Field(connection, "NULLABLE", iInt4Oid, 4);
f[11] = new Field(connection, "REMARKS", iVarcharOid, 32);
f[12] = new Field(connection, "COLUMN_DEF", iVarcharOid, 32);
***************
*** 1917,2021 ****
f[16] = new Field(connection, "ORDINAL_POSITION", iInt4Oid,4);
f[17] = new Field(connection, "IS_NULLABLE", iVarcharOid, 32);
! StringBuffer sql = new StringBuffer(512);
!
! sql.append("select " +
! (connection.haveMinimumServerVersion("7.2") ? "a.attrelid, " : "a.oid,
") +
! " c.relname, " +
! " a.attname, " +
! " a.atttypid, " +
! " a.attnum, " +
! " a.attnotnull, " +
! " a.attlen, " +
! " a.atttypmod, " +
! " d.adsrc, " +
! " t.typname, " +
! " e.description " +
! "from" +
! " (" +
! " (pg_class c inner join pg_attribute a on" +
! " (" +
! " a.attrelid=c.oid");
!
! if ((tableNamePattern != null) && ! tableNamePattern.equals("%")) {
! sql.append(" and c.relname like \'" + tableNamePattern
+ "\'");
! }
!
! if ((columnNamePattern != null) && ! columnNamePattern.equals("%")) {
! sql.append(" and a.attname like \'" + columnNamePattern
+ "\'");
! }
!
! sql.append(
! " and a.attnum > 0" +
! " )" +
! " ) inner join pg_type t on" +
! " (" +
! " t.oid = a.atttypid" +
! " )" +
! " )" +
! " left outer join pg_attrdef d on" +
! " (" +
! " c.oid = d.adrelid" +
! " and a.attnum = d.adnum" +
! " )" +
! " left outer join pg_description e on" +
! " (" +
! " e.objoid = a.attrelid");
!
! if (connection.haveMinimumServerVersion("7.2")) {
! sql.append(
! " and e.objsubid = a.attnum" +
! " and e.classoid = (select oid from pg_class where
relname = \'pg_class\')");
! }
!
! sql.append(
! " ) " +
! "order by" +
! " c.relname, a.attnum");
!
! java.sql.ResultSet r = connection.ExecSQL(sql.toString());
! while (r.next()) {
! byte[][] tuple = new byte[18][0];
!
! String nullFlag = r.getString(6);
! String typname = r.getString(10);
!
! tuple[0] = "".getBytes(); // Catalog name
! tuple[1] = "".getBytes(); // Schema name
! tuple[2] = r.getBytes(2); // Table name
! tuple[3] = r.getBytes(3); // Column name
! tuple[4] =
Integer.toString(connection.getSQLType(typname)).getBytes(); // Data type
! tuple[5] = typname.getBytes(); // Type name
!
! // Column size
! // Looking at the psql source,
! // I think the length of a varchar as specified when the table was
created
! // should be extracted from atttypmod which contains this length +
sizeof(int32)
! if (typname.equals("bpchar") || typname.equals("varchar")) {
! int atttypmod = r.getInt(8);
! tuple[6] = Integer.toString(atttypmod != -1 ? atttypmod -
VARHDRSZ : 0).getBytes();
! } else {
! tuple[6] = r.getBytes(7);
! }
!
! tuple[7] = null; //
Buffer length
! tuple[8] = "0".getBytes(); // Decimal
Digits - how to get this?
! tuple[9] = "10".getBytes(); // Num Prec
Radix - assume decimal
! tuple[10] = Integer.toString(nullFlag.equals("f") ?
! java.sql.DatabaseMetaData.columnNullable :
! java.sql.DatabaseMetaData.columnNoNulls).getBytes(); //
Nullable
! tuple[11] = r.getBytes(11); // Description (if any)
! tuple[12] = r.getBytes(9); // Column
default
! tuple[13] = null; // sql
data type (unused)
! tuple[14] = null; // sql
datetime sub (unused)
! tuple[15] = tuple[6]; // char octet
length
! tuple[16] = r.getBytes(5); // ordinal
position
! tuple[17] = (nullFlag.equals("f") ? "YES" : "NO").getBytes(); // Is
nullable
!
! v.addElement(tuple);
! }
! r.close();
!
return new ResultSet(connection, f, v, "OK", 1);
}
--- 1919,2011 ----
f[16] = new Field(connection, "ORDINAL_POSITION", iInt4Oid,4);
f[17] = new Field(connection, "IS_NULLABLE", iVarcharOid, 32);
! // Added by Stefan Andreasen <[EMAIL PROTECTED]>
! // If the pattern are null then set them to %
! if (tableNamePattern == null) tableNamePattern="%";
! if (columnNamePattern == null) columnNamePattern="%";
!
! // Now form the query
! String query =
! "select " +
! (connection.haveMinimumServerVersion("7.2") ? "a.attrelid" : "a.oid") +
! ",c.relname,a.attname,a.atttypid," +
! "a.attnum,a.attnotnull,a.attlen,a.atttypmod,d.adsrc " +
! "from (pg_class c inner join pg_attribute a " +
! "on (c.oid=a.attrelid) ) " +
! "left outer join pg_attrdef d " +
! "on (c.oid=d.adrelid and d.adnum=a.attnum) " +
! "where " +
! "c.relname like '"+tableNamePattern.toLowerCase()+"' and " +
! "a.attname like '"+columnNamePattern.toLowerCase()+"' and " +
! "a.attnum>0 " +
! "order by c.relname,a.attnum";
!
! r = connection.ExecSQL(query);
!
! while(r.next()) {
! byte[][] tuple = new byte[18][0];
!
! // Fetch the description for the table (if any)
! String getDescriptionStatement =
! connection.haveMinimumServerVersion("7.2") ?
! "select col_description(" + r.getInt(1) + "," + r.getInt(5) + ")" :
! "select description from pg_description where objoid=" + r.getInt(1);
!
! java.sql.ResultSet dr = connection.ExecSQL(getDescriptionStatement);
!
! if(((org.postgresql.ResultSet)dr).getTupleCount()==1) {
! dr.next();
! tuple[11] = dr.getBytes(1);
! } else
! tuple[11] = null;
! dr.close();
!
! tuple[0] = "".getBytes(); // Catalog name
! tuple[1] = "".getBytes(); // Schema name
! tuple[2] = r.getBytes(2); // Table name
! tuple[3] = r.getBytes(3); // Column name
!
! dr = connection.ExecSQL("select typname from pg_type where oid =
"+r.getString(4));
! dr.next();
! String typname=dr.getString(1);
! dr.close();
! tuple[4] = Integer.toString(connection.getSQLType(typname)).getBytes();
// Data type
! tuple[5] = typname.getBytes(); // Type name
!
! // Column size
! // Looking at the psql source,
! // I think the length of a varchar as specified when the table was created
! // should be extracted from atttypmod which contains this length +
sizeof(int32)
! if (typname.equals("bpchar") || typname.equals("varchar")) {
! int atttypmod = r.getInt(8);
! tuple[6] = Integer.toString(atttypmod != -1 ? atttypmod - VARHDRSZ :
0).getBytes();
! } else
! tuple[6] = r.getBytes(7);
!
! tuple[7] = null; // Buffer length
!
! tuple[8] = "0".getBytes(); // Decimal Digits - how to get this?
! tuple[9] = "10".getBytes(); // Num Prec Radix - assume decimal
!
! // tuple[10] is below
! // tuple[11] is above
!
! tuple[12] = r.getBytes(9); // column default
!
! tuple[13] = null; // sql data type (unused)
! tuple[14] = null; // sql datetime sub (unused)
!
! tuple[15] = tuple[6]; // char octet length
!
! tuple[16] = r.getBytes(5); // ordinal position
!
! String nullFlag = r.getString(6);
! tuple[10] =
Integer.toString(nullFlag.equals("f")?java.sql.DatabaseMetaData.columnNullable:java.sql.DatabaseMetaData.columnNoNulls).getBytes();
// Nullable
! tuple[17] = (nullFlag.equals("f")?"YES":"NO").getBytes(); // is nullable
!
! v.addElement(tuple);
! }
! r.close();
return new ResultSet(connection, f, v, "OK", 1);
}
Index: org/postgresql/jdbc2/DatabaseMetaData.java
===================================================================
RCS file:
/home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java,v
retrieving revision 1.35
diff -c -r1.35 DatabaseMetaData.java
*** org/postgresql/jdbc2/DatabaseMetaData.java 2001/09/06 12:53:15 1.35
--- org/postgresql/jdbc2/DatabaseMetaData.java 2001/09/08 21:37:44
***************
*** 1895,1913 ****
*/
public java.sql.ResultSet getColumns(String catalog, String schemaPattern, String
tableNamePattern, String columnNamePattern) throws SQLException
{
Vector v = new Vector(); // The new ResultSet tuple stuff
- Field f[] = new Field[18]; // The field descriptors for the new
ResultSet
! f[ 0] = new Field(connection, "TABLE_CAT", iVarcharOid, 32);
! f[ 1] = new Field(connection, "TABLE_SCHEM", iVarcharOid, 32);
! f[ 2] = new Field(connection, "TABLE_NAME", iVarcharOid, 32);
! f[ 3] = new Field(connection, "COLUMN_NAME", iVarcharOid, 32);
! f[ 4] = new Field(connection, "DATA_TYPE", iInt2Oid, 2);
! f[ 5] = new Field(connection, "TYPE_NAME", iVarcharOid, 32);
! f[ 6] = new Field(connection, "COLUMN_SIZE", iInt4Oid, 4);
! f[ 7] = new Field(connection, "BUFFER_LENGTH", iVarcharOid, 32);
! f[ 8] = new Field(connection, "DECIMAL_DIGITS", iInt4Oid, 4);
! f[ 9] = new Field(connection, "NUM_PREC_RADIX", iInt4Oid, 4);
f[10] = new Field(connection, "NULLABLE", iInt4Oid, 4);
f[11] = new Field(connection, "REMARKS", iVarcharOid, 32);
f[12] = new Field(connection, "COLUMN_DEF", iVarcharOid, 32);
--- 1895,1915 ----
*/
public java.sql.ResultSet getColumns(String catalog, String schemaPattern, String
tableNamePattern, String columnNamePattern) throws SQLException
{
+ // the field descriptors for the new ResultSet
+ Field f[] = new Field[18];
+ java.sql.ResultSet r; // ResultSet for the SQL query that we need to do
Vector v = new Vector(); // The new ResultSet tuple stuff
! f[0] = new Field(connection, "TABLE_CAT", iVarcharOid, 32);
! f[1] = new Field(connection, "TABLE_SCHEM", iVarcharOid, 32);
! f[2] = new Field(connection, "TABLE_NAME", iVarcharOid, 32);
! f[3] = new Field(connection, "COLUMN_NAME", iVarcharOid, 32);
! f[4] = new Field(connection, "DATA_TYPE", iInt2Oid, 2);
! f[5] = new Field(connection, "TYPE_NAME", iVarcharOid, 32);
! f[6] = new Field(connection, "COLUMN_SIZE", iInt4Oid, 4);
! f[7] = new Field(connection, "BUFFER_LENGTH", iVarcharOid, 32);
! f[8] = new Field(connection, "DECIMAL_DIGITS", iInt4Oid, 4);
! f[9] = new Field(connection, "NUM_PREC_RADIX", iInt4Oid, 4);
f[10] = new Field(connection, "NULLABLE", iInt4Oid, 4);
f[11] = new Field(connection, "REMARKS", iVarcharOid, 32);
f[12] = new Field(connection, "COLUMN_DEF", iVarcharOid, 32);
***************
*** 1917,2021 ****
f[16] = new Field(connection, "ORDINAL_POSITION", iInt4Oid,4);
f[17] = new Field(connection, "IS_NULLABLE", iVarcharOid, 32);
! StringBuffer sql = new StringBuffer(512);
!
! sql.append("select " +
! (connection.haveMinimumServerVersion("7.2") ? "a.attrelid, " : "a.oid,
") +
! " c.relname, " +
! " a.attname, " +
! " a.atttypid, " +
! " a.attnum, " +
! " a.attnotnull, " +
! " a.attlen, " +
! " a.atttypmod, " +
! " d.adsrc, " +
! " t.typname, " +
! " e.description " +
! "from" +
! " (" +
! " (pg_class c inner join pg_attribute a on" +
! " (" +
! " a.attrelid=c.oid");
!
! if ((tableNamePattern != null) && ! tableNamePattern.equals("%")) {
! sql.append(" and c.relname like \'" + tableNamePattern
+ "\'");
! }
!
! if ((columnNamePattern != null) && ! columnNamePattern.equals("%")) {
! sql.append(" and a.attname like \'" + columnNamePattern
+ "\'");
! }
!
! sql.append(
! " and a.attnum > 0" +
! " )" +
! " ) inner join pg_type t on" +
! " (" +
! " t.oid = a.atttypid" +
! " )" +
! " )" +
! " left outer join pg_attrdef d on" +
! " (" +
! " c.oid = d.adrelid" +
! " and a.attnum = d.adnum" +
! " )" +
! " left outer join pg_description e on" +
! " (" +
! " e.objoid = a.attrelid");
!
! if (connection.haveMinimumServerVersion("7.2")) {
! sql.append(
! " and e.objsubid = a.attnum" +
! " and e.classoid = (select oid from pg_class where
relname = \'pg_class\')");
! }
!
! sql.append(
! " ) " +
! "order by" +
! " c.relname, a.attnum");
!
! java.sql.ResultSet r = connection.ExecSQL(sql.toString());
! while (r.next()) {
! byte[][] tuple = new byte[18][0];
!
! String nullFlag = r.getString(6);
! String typname = r.getString(10);
!
! tuple[0] = "".getBytes(); // Catalog name
! tuple[1] = "".getBytes(); // Schema name
! tuple[2] = r.getBytes(2); // Table name
! tuple[3] = r.getBytes(3); // Column name
! tuple[4] =
Integer.toString(connection.getSQLType(typname)).getBytes(); // Data type
! tuple[5] = typname.getBytes(); // Type name
!
! // Column size
! // Looking at the psql source,
! // I think the length of a varchar as specified when the table was
created
! // should be extracted from atttypmod which contains this length +
sizeof(int32)
! if (typname.equals("bpchar") || typname.equals("varchar")) {
! int atttypmod = r.getInt(8);
! tuple[6] = Integer.toString(atttypmod != -1 ? atttypmod -
VARHDRSZ : 0).getBytes();
! } else {
! tuple[6] = r.getBytes(7);
! }
!
! tuple[7] = null; //
Buffer length
! tuple[8] = "0".getBytes(); // Decimal
Digits - how to get this?
! tuple[9] = "10".getBytes(); // Num Prec
Radix - assume decimal
! tuple[10] = Integer.toString(nullFlag.equals("f") ?
! java.sql.DatabaseMetaData.columnNullable :
! java.sql.DatabaseMetaData.columnNoNulls).getBytes(); //
Nullable
! tuple[11] = r.getBytes(11); // Description (if any)
! tuple[12] = r.getBytes(9); // Column
default
! tuple[13] = null; // sql
data type (unused)
! tuple[14] = null; // sql
datetime sub (unused)
! tuple[15] = tuple[6]; // char octet
length
! tuple[16] = r.getBytes(5); // ordinal
position
! tuple[17] = (nullFlag.equals("f") ? "YES" : "NO").getBytes(); // Is
nullable
!
! v.addElement(tuple);
! }
! r.close();
!
return new ResultSet(connection, f, v, "OK", 1);
}
--- 1919,2011 ----
f[16] = new Field(connection, "ORDINAL_POSITION", iInt4Oid,4);
f[17] = new Field(connection, "IS_NULLABLE", iVarcharOid, 32);
! // Added by Stefan Andreasen <[EMAIL PROTECTED]>
! // If the pattern are null then set them to %
! if (tableNamePattern == null) tableNamePattern="%";
! if (columnNamePattern == null) columnNamePattern="%";
!
! // Now form the query
! String query =
! "select " +
! (connection.haveMinimumServerVersion("7.2") ? "a.attrelid" : "a.oid") +
! ",c.relname,a.attname,a.atttypid," +
! "a.attnum,a.attnotnull,a.attlen,a.atttypmod,d.adsrc " +
! "from (pg_class c inner join pg_attribute a " +
! "on (c.oid=a.attrelid) ) " +
! "left outer join pg_attrdef d " +
! "on (c.oid=d.adrelid and d.adnum=a.attnum) " +
! "where " +
! "c.relname like '"+tableNamePattern.toLowerCase()+"' and " +
! "a.attname like '"+columnNamePattern.toLowerCase()+"' and " +
! "a.attnum>0 " +
! "order by c.relname,a.attnum";
!
! r = connection.ExecSQL(query);
!
! while(r.next()) {
! byte[][] tuple = new byte[18][0];
!
! // Fetch the description for the table (if any)
! String getDescriptionStatement =
! connection.haveMinimumServerVersion("7.2") ?
! "select col_description(" + r.getInt(1) + "," + r.getInt(5) + ")" :
! "select description from pg_description where objoid=" + r.getInt(1);
!
! java.sql.ResultSet dr = connection.ExecSQL(getDescriptionStatement);
!
! if(((org.postgresql.ResultSet)dr).getTupleCount()==1) {
! dr.next();
! tuple[11] = dr.getBytes(1);
! } else
! tuple[11] = null;
! dr.close();
!
! tuple[0] = "".getBytes(); // Catalog name
! tuple[1] = "".getBytes(); // Schema name
! tuple[2] = r.getBytes(2); // Table name
! tuple[3] = r.getBytes(3); // Column name
!
! dr = connection.ExecSQL("select typname from pg_type where oid =
"+r.getString(4));
! dr.next();
! String typname=dr.getString(1);
! dr.close();
! tuple[4] = Integer.toString(connection.getSQLType(typname)).getBytes();
// Data type
! tuple[5] = typname.getBytes(); // Type name
!
! // Column size
! // Looking at the psql source,
! // I think the length of a varchar as specified when the table was created
! // should be extracted from atttypmod which contains this length +
sizeof(int32)
! if (typname.equals("bpchar") || typname.equals("varchar")) {
! int atttypmod = r.getInt(8);
! tuple[6] = Integer.toString(atttypmod != -1 ? atttypmod - VARHDRSZ :
0).getBytes();
! } else
! tuple[6] = r.getBytes(7);
!
! tuple[7] = null; // Buffer length
!
! tuple[8] = "0".getBytes(); // Decimal Digits - how to get this?
! tuple[9] = "10".getBytes(); // Num Prec Radix - assume decimal
!
! // tuple[10] is below
! // tuple[11] is above
!
! tuple[12] = r.getBytes(9); // column default
!
! tuple[13] = null; // sql data type (unused)
! tuple[14] = null; // sql datetime sub (unused)
!
! tuple[15] = tuple[6]; // char octet length
!
! tuple[16] = r.getBytes(5); // ordinal position
!
! String nullFlag = r.getString(6);
! tuple[10] =
Integer.toString(nullFlag.equals("f")?java.sql.DatabaseMetaData.columnNullable:java.sql.DatabaseMetaData.columnNoNulls).getBytes();
// Nullable
! tuple[17] = (nullFlag.equals("f")?"YES":"NO").getBytes(); // is nullable
!
! v.addElement(tuple);
! }
! r.close();
return new ResultSet(connection, f, v, "OK", 1);
}
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
