Thread: DatabaseMetaData.getTables() problem
Hi,
on testing our auto-configuration persistence framework ACP against
PostgreSQL we found the following problem:
There is a table A625431658_Person1, created as quoted mixed case
identifier.
I do:
databaseMetaData.getTables(null, null, "%", new String[] {"TABLE"})
--> A625431658_Person1 is in the ResultSet. OK.
I do:
databaseMetaData.getTables(null, null, "A625431658_Person1", new String[]
{"TABLE"})
--> A625431658_Person1 is NOT in the ResultSet.
I do:
databaseMetaData.getTables(null, null, "%erson1", new String[] {"TABLE"})
--> A625431658_Person1 is in the ResultSet. OK.
Seems there is a problem with mixed case ?
Wolfgang
___________________
Dr. Wolfgang Winter
LogiTags Systems
www.logitags.com
Wolfgang,
Can you try this test using the 7.3beta3 build of the driver from
jdbc.postgresql.org? I think this should be fixed in the latest build.
thanks,
--Barry
w.winter wrote:
> Hi,
>
> on testing our auto-configuration persistence framework ACP against
> PostgreSQL we found the following problem:
>
> There is a table A625431658_Person1, created as quoted mixed case
> identifier.
>
> I do:
> databaseMetaData.getTables(null, null, "%", new String[] {"TABLE"})
> --> A625431658_Person1 is in the ResultSet. OK.
>
> I do:
> databaseMetaData.getTables(null, null, "A625431658_Person1", new String[]
> {"TABLE"})
> --> A625431658_Person1 is NOT in the ResultSet.
>
> I do:
> databaseMetaData.getTables(null, null, "%erson1", new String[] {"TABLE"})
> --> A625431658_Person1 is in the ResultSet. OK.
>
> Seems there is a problem with mixed case ?
>
>
> Wolfgang
>
> ___________________
> Dr. Wolfgang Winter
> LogiTags Systems
> www.logitags.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
This has not been fixed. Currently the driver is doing something along
the lines of
" WHERE c.relname LIKE " + tableNamePattern.toLowerCase();
This masks the problem of a user supplying an uppercase version of a
tablename that was case folded to lower, but does not work when they
really want an uppercase table name.
I believe that adding a LOWER() to c.relname is the best temporary
solution until we figure out a better way to handle case sensitivity.
Barry, If you believe this is the way to go I will work up a patch for
this an similar places in the driver.
Kris Jurka
On Tue, 29 Oct 2002, Barry Lind wrote:
> Wolfgang,
>
> Can you try this test using the 7.3beta3 build of the driver from
> jdbc.postgresql.org? I think this should be fixed in the latest build.
>
> thanks,
> --Barry
>
>
> w.winter wrote:
> > Hi,
> >
> > on testing our auto-configuration persistence framework ACP against
> > PostgreSQL we found the following problem:
> >
> > There is a table A625431658_Person1, created as quoted mixed case
> > identifier.
> >
> > I do:
> > databaseMetaData.getTables(null, null, "%", new String[] {"TABLE"})
> > --> A625431658_Person1 is in the ResultSet. OK.
> >
> > I do:
> > databaseMetaData.getTables(null, null, "A625431658_Person1", new String[]
> > {"TABLE"})
> > --> A625431658_Person1 is NOT in the ResultSet.
> >
> > I do:
> > databaseMetaData.getTables(null, null, "%erson1", new String[] {"TABLE"})
> > --> A625431658_Person1 is in the ResultSet. OK.
> >
> > Seems there is a problem with mixed case ?
> >
> >
> > Wolfgang
> >
> > ___________________
> > Dr. Wolfgang Winter
> > LogiTags Systems
> > www.logitags.com
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
Kris,
I can't think of anything better. So yes please submit a patch for this.
thanks,
--Barry
Kris Jurka wrote:
> This has not been fixed. Currently the driver is doing something along
> the lines of
>
> " WHERE c.relname LIKE " + tableNamePattern.toLowerCase();
>
> This masks the problem of a user supplying an uppercase version of a
> tablename that was case folded to lower, but does not work when they
> really want an uppercase table name.
>
> I believe that adding a LOWER() to c.relname is the best temporary
> solution until we figure out a better way to handle case sensitivity.
>
> Barry, If you believe this is the way to go I will work up a patch for
> this an similar places in the driver.
>
> Kris Jurka
>
>
> On Tue, 29 Oct 2002, Barry Lind wrote:
>
>
>>Wolfgang,
>>
>>Can you try this test using the 7.3beta3 build of the driver from
>>jdbc.postgresql.org? I think this should be fixed in the latest build.
>>
>>thanks,
>>--Barry
>>
>>
>>w.winter wrote:
>>
>>>Hi,
>>>
>>>on testing our auto-configuration persistence framework ACP against
>>>PostgreSQL we found the following problem:
>>>
>>>There is a table A625431658_Person1, created as quoted mixed case
>>>identifier.
>>>
>>>I do:
>>>databaseMetaData.getTables(null, null, "%", new String[] {"TABLE"})
>>>--> A625431658_Person1 is in the ResultSet. OK.
>>>
>>>I do:
>>>databaseMetaData.getTables(null, null, "A625431658_Person1", new String[]
>>>{"TABLE"})
>>>--> A625431658_Person1 is NOT in the ResultSet.
>>>
>>>I do:
>>>databaseMetaData.getTables(null, null, "%erson1", new String[] {"TABLE"})
>>>--> A625431658_Person1 is in the ResultSet. OK.
>>>
>>>Seems there is a problem with mixed case ?
>>>
>>>
>>>Wolfgang
>>>
>>>___________________
>>>Dr. Wolfgang Winter
>>>LogiTags Systems
>>>www.logitags.com
>>>
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 6: Have you searched our list archives?
>>>
>>>http://archives.postgresql.org
>>>
>>
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
I have given this some further thought and believe that we should not do
any case folding in the driver for DatabaseMetaData calls. We cannot
guess what the caller's intention is regarding case handling, so instead
the driver provides the necessary methods to let the user know what the
database does with regard to case. The following methods allow the
caller to do their own case folding if necessary:
storesLowerCaseIdentifiers
storesLowerCaseQuotedIdentifiers
storesMixedCaseIdentifiers
storesMixedCaseQuotedIdentifiers
storesUpperCaseIdentifiers
storesUpperCaseQuotedIdentifiers
supportsMixedCaseIdentifiers
supportsMixedCaseQuotedIdentifiers
The attached patch removes the existing case folding in the driver.
Barry Lind wrote:
>
> Kris,
>
> I can't think of anything better. So yes please submit a patch for this.
>
> thanks,
> --Barry
>
> Kris Jurka wrote:
> > This has not been fixed. Currently the driver is doing something along
> > the lines of
> >
> > " WHERE c.relname LIKE " + tableNamePattern.toLowerCase();
> >
> > This masks the problem of a user supplying an uppercase version of a
> > tablename that was case folded to lower, but does not work when they
> > really want an uppercase table name.
> >
> > I believe that adding a LOWER() to c.relname is the best temporary
> > solution until we figure out a better way to handle case sensitivity.
> >
> > Barry, If you believe this is the way to go I will work up a patch for
> > this an similar places in the driver.
> >
> > Kris Jurka
> >
> >
> > On Tue, 29 Oct 2002, Barry Lind wrote:
> >
> >
> >>Wolfgang,
> >>
> >>Can you try this test using the 7.3beta3 build of the driver from
> >>jdbc.postgresql.org? I think this should be fixed in the latest build.
> >>
> >>thanks,
> >>--Barry
> >>
> >>
> >>w.winter wrote:
> >>
> >>>Hi,
> >>>
> >>>on testing our auto-configuration persistence framework ACP against
> >>>PostgreSQL we found the following problem:
> >>>
> >>>There is a table A625431658_Person1, created as quoted mixed case
> >>>identifier.
> >>>
> >>>I do:
> >>>databaseMetaData.getTables(null, null, "%", new String[] {"TABLE"})
> >>>--> A625431658_Person1 is in the ResultSet. OK.
> >>>
> >>>I do:
> >>>databaseMetaData.getTables(null, null, "A625431658_Person1", new String[]
> >>>{"TABLE"})
> >>>--> A625431658_Person1 is NOT in the ResultSet.
> >>>
> >>>I do:
> >>>databaseMetaData.getTables(null, null, "%erson1", new String[] {"TABLE"})
> >>>--> A625431658_Person1 is in the ResultSet. OK.
> >>>
> >>>Seems there is a problem with mixed case ?
> >>>
> >>>
> >>>Wolfgang
> >>>
> >>>___________________
> >>>Dr. Wolfgang Winter
> >>>LogiTags Systems
> >>>www.logitags.com? src/interfaces/jdbc/org/postgresql/Driver.java
Index: src/interfaces/jdbc/org/postgresql//jdbc1/AbstractJdbc1DatabaseMetaData.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java,v
retrieving revision 1.9
diff -c -r1.9 AbstractJdbc1DatabaseMetaData.java
*** src/interfaces/jdbc/org/postgresql//jdbc1/AbstractJdbc1DatabaseMetaData.java 2002/10/25 02:54:37 1.9
--- src/interfaces/jdbc/org/postgresql//jdbc1/AbstractJdbc1DatabaseMetaData.java 2002/11/02 22:26:58
***************
*** 314,323 ****
* as case sensitive and as a result store them in mixed case?
* A JDBC-Compliant driver will always return false.
*
- * <p>Predicament - what do they mean by "SQL identifiers" - if it
- * means the names of the tables and columns, then the answers
- * given below are correct - otherwise I don't know.
- *
* @return true if so
* @exception SQLException if a database access error occurs
*/
--- 314,319 ----
***************
*** 1725,1734 ****
" LEFT JOIN pg_catalog.pg_namespace pn ON (c.relnamespace=pn.oid AND pn.nspname='pg_catalog') "+
" WHERE p.pronamespace=n.oid ";
if (schemaPattern != null && !"".equals(schemaPattern)) {
! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
}
if (procedureNamePattern != null) {
! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
}
sql += " ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME ";
} else if (connection.haveMinimumServerVersion("7.1")) {
--- 1721,1730 ----
" LEFT JOIN pg_catalog.pg_namespace pn ON (c.relnamespace=pn.oid AND pn.nspname='pg_catalog') "+
" WHERE p.pronamespace=n.oid ";
if (schemaPattern != null && !"".equals(schemaPattern)) {
! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
}
if (procedureNamePattern != null) {
! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
}
sql += " ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME ";
} else if (connection.haveMinimumServerVersion("7.1")) {
***************
*** 1737,1750 ****
" LEFT JOIN pg_description d ON (p.oid=d.objoid) "+
" LEFT JOIN pg_class c ON (d.classoid=c.oid AND c.relname='pg_proc') ";
if (procedureNamePattern != null) {
! sql += " WHERE p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
}
sql += " ORDER BY PROCEDURE_NAME ";
} else {
sql = "SELECT NULL AS PROCEDURE_CAT, NULL AS PROCEDURE_SCHEM, p.proname AS PROCEDURE_NAME, NULL, NULL,
NULL,NULL AS REMARKS, "+java.sql.DatabaseMetaData.procedureReturnsResult+" AS PROCEDURE_TYPE "+
" FROM pg_proc p ";
if (procedureNamePattern != null) {
! sql += " WHERE p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
}
sql += " ORDER BY PROCEDURE_NAME ";
}
--- 1733,1746 ----
" LEFT JOIN pg_description d ON (p.oid=d.objoid) "+
" LEFT JOIN pg_class c ON (d.classoid=c.oid AND c.relname='pg_proc') ";
if (procedureNamePattern != null) {
! sql += " WHERE p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
}
sql += " ORDER BY PROCEDURE_NAME ";
} else {
sql = "SELECT NULL AS PROCEDURE_CAT, NULL AS PROCEDURE_SCHEM, p.proname AS PROCEDURE_NAME, NULL, NULL,
NULL,NULL AS REMARKS, "+java.sql.DatabaseMetaData.procedureReturnsResult+" AS PROCEDURE_TYPE "+
" FROM pg_proc p ";
if (procedureNamePattern != null) {
! sql += " WHERE p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
}
sql += " ORDER BY PROCEDURE_NAME ";
}
***************
*** 1822,1831 ****
" 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 && !"".equals(schemaPattern)) {
! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
}
if (procedureNamePattern != null) {
! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
}
sql += " ORDER BY n.nspname, p.proname ";
} else {
--- 1818,1827 ----
" 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 && !"".equals(schemaPattern)) {
! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
}
if (procedureNamePattern != null) {
! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
}
sql += " ORDER BY n.nspname, p.proname ";
} else {
***************
*** 1833,1839 ****
" FROM pg_proc p,pg_type t "+
" WHERE p.prorettype=t.oid ";
if (procedureNamePattern != null) {
! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
}
sql += " ORDER BY p.proname ";
}
--- 1829,1835 ----
" FROM pg_proc p,pg_type t "+
" WHERE p.prorettype=t.oid ";
if (procedureNamePattern != null) {
! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
}
sql += " ORDER BY p.proname ";
}
***************
*** 1997,2006 ****
" LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog') "+
" WHERE c.relnamespace = n.oid ";
if (schemaPattern != null && !"".equals(schemaPattern)) {
! select += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
}
if (tableNamePattern != null) {
! select += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern.toLowerCase())+"' ";
}
orderby = " ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME ";
} else {
--- 1993,2002 ----
" LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog') "+
" WHERE c.relnamespace = n.oid ";
if (schemaPattern != null && !"".equals(schemaPattern)) {
! select += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
}
if (tableNamePattern != null) {
! select += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern)+"' ";
}
orderby = " ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME ";
} else {
***************
*** 2290,2296 ****
" LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog') "+
" WHERE a.attnum > 0 AND NOT a.attisdropped ";
if (schemaPattern != null && !"".equals(schemaPattern)) {
! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
}
} else if (connection.haveMinimumServerVersion("7.1")) {
sql = "SELECT NULL::text AS
nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description"+
--- 2286,2292 ----
" LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog') "+
" WHERE a.attnum > 0 AND NOT a.attisdropped ";
if (schemaPattern != null && !"".equals(schemaPattern)) {
! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
}
} else if (connection.haveMinimumServerVersion("7.1")) {
sql = "SELECT NULL::text AS
nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description"+
***************
*** 2308,2317 ****
}
if (tableNamePattern != null && !"".equals(tableNamePattern)) {
! sql += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern.toLowerCase())+"' ";
}
if (columnNamePattern != null && !"".equals(columnNamePattern)) {
! sql += " AND a.attname LIKE '"+escapeQuotes(columnNamePattern.toLowerCase())+"' ";
}
sql += " ORDER BY nspname,relname,attname ";
--- 2304,2313 ----
}
if (tableNamePattern != null && !"".equals(tableNamePattern)) {
! sql += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern)+"' ";
}
if (columnNamePattern != null && !"".equals(columnNamePattern)) {
! sql += " AND a.attname LIKE '"+escapeQuotes(columnNamePattern)+"' ";
}
sql += " ORDER BY nspname,relname,attname ";
***************
*** 2410,2417 ****
if (columnNamePattern == null)
columnNamePattern = "%";
- else
- columnNamePattern = columnNamePattern.toLowerCase();
f[0] = new Field(connection, "TABLE_CAT", iVarcharOid, getMaxNameLength());
f[1] = new Field(connection, "TABLE_SCHEM", iVarcharOid, getMaxNameLength());
--- 2406,2411 ----
***************
*** 2432,2438 ****
" AND c.relkind = 'r' "+
" AND a.attnum > 0 AND NOT a.attisdropped ";
if (schema != null && !"".equals(schema)) {
! sql += " AND n.nspname = '"+escapeQuotes(schema.toLowerCase())+"' ";
}
} else {
sql = "SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl,a.attname "+
--- 2426,2432 ----
" AND c.relkind = 'r' "+
" AND a.attnum > 0 AND NOT a.attisdropped ";
if (schema != null && !"".equals(schema)) {
! sql += " AND n.nspname = '"+escapeQuotes(schema)+"' ";
}
} else {
sql = "SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl,a.attname "+
***************
*** 2443,2451 ****
" AND c.relkind = 'r' ";
}
! sql += " AND c.relname = '"+escapeQuotes(table.toLowerCase())+"' ";
if (columnNamePattern != null && !"".equals(columnNamePattern)) {
! sql += " AND a.attname LIKE '"+escapeQuotes(columnNamePattern.toLowerCase())+"' ";
}
sql += " ORDER BY attname ";
--- 2437,2445 ----
" AND c.relkind = 'r' ";
}
! sql += " AND c.relname = '"+escapeQuotes(table)+"' ";
if (columnNamePattern != null && !"".equals(columnNamePattern)) {
! sql += " AND a.attname LIKE '"+escapeQuotes(columnNamePattern)+"' ";
}
sql += " ORDER BY attname ";
***************
*** 2538,2544 ****
" AND u.usesysid = c.relowner "+
" AND c.relkind = 'r' ";
if (schemaPattern != null && !"".equals(schemaPattern)) {
! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
}
} else {
sql = "SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl "+
--- 2532,2538 ----
" AND u.usesysid = c.relowner "+
" AND c.relkind = 'r' ";
if (schemaPattern != null && !"".equals(schemaPattern)) {
! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
}
} else {
sql = "SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl "+
***************
*** 2548,2554 ****
}
if (tableNamePattern != null && !"".equals(tableNamePattern)) {
! sql += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern.toLowerCase())+"' ";
}
sql += " ORDER BY nspname, relname ";
--- 2542,2548 ----
}
if (tableNamePattern != null && !"".equals(tableNamePattern)) {
! sql += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern)+"' ";
}
sql += " ORDER BY nspname, relname ";
***************
*** 2752,2758 ****
from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_attributea, pg_catalog.pg_index i ";
where = " AND ct.relnamespace = n.oid ";
if (schema != null && !"".equals(schema)) {
! where += " AND n.nspname = '"+escapeQuotes(schema.toLowerCase())+"' ";
}
} else {
from = " FROM pg_class ct, pg_class ci, pg_attribute a, pg_index i ";
--- 2746,2752 ----
from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_attributea, pg_catalog.pg_index i ";
where = " AND ct.relnamespace = n.oid ";
if (schema != null && !"".equals(schema)) {
! where += " AND n.nspname = '"+escapeQuotes(schema)+"' ";
}
} else {
from = " FROM pg_class ct, pg_class ci, pg_attribute a, pg_index i ";
***************
*** 2761,2767 ****
from+
" WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid "+
" AND a.attrelid=ci.oid AND i.indisprimary "+
! " AND ct.relname = '"+escapeQuotes(table.toLowerCase())+"' "+
where+
" ORDER BY a.attnum ";
--- 2755,2761 ----
from+
" WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid "+
" AND a.attrelid=ci.oid AND i.indisprimary "+
! " AND ct.relname = '"+escapeQuotes(table)+"' "+
where+
" ORDER BY a.attnum ";
***************
*** 2882,2888 ****
from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_attributea, pg_catalog.pg_index i ";
where = " AND ct.relnamespace = n.oid ";
if (schema != null && !"".equals(schema)) {
! where += " AND n.nspname = '"+escapeQuotes(schema.toLowerCase())+"' ";
}
} else {
select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, ";
--- 2876,2882 ----
from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_attributea, pg_catalog.pg_index i ";
where = " AND ct.relnamespace = n.oid ";
if (schema != null && !"".equals(schema)) {
! where += " AND n.nspname = '"+escapeQuotes(schema)+"' ";
}
} else {
select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, ";
***************
*** 2896,2902 ****
from+
" WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid "+
" AND a.attrelid=ci.oid AND i.indisprimary "+
! " AND ct.relname = '"+escapeQuotes(table.toLowerCase())+"' "+
where+
" ORDER BY table_name, pk_name, key_seq";
return connection.createStatement().executeQuery(sql);
--- 2890,2896 ----
from+
" WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid "+
" AND a.attrelid=ci.oid AND i.indisprimary "+
! " AND ct.relname = '"+escapeQuotes(table)+"' "+
where+
" ORDER BY table_name, pk_name, key_seq";
return connection.createStatement().executeQuery(sql);
***************
*** 3008,3017 ****
from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_namespace n2, pg_catalog.pg_trigger t,
pg_catalog.pg_triggert1, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_class ic, pg_catalog.pg_proc p1,
pg_catalog.pg_procp2, pg_catalog.pg_index i, pg_catalog.pg_attribute a ";
where = " AND c.relnamespace = n.oid AND c2.relnamespace=n2.oid ";
if (primarySchema != null && !"".equals(primarySchema)) {
! where += " AND n.nspname = '"+escapeQuotes(primarySchema.toLowerCase())+"' ";
}
if (foreignSchema != null && !"".equals(foreignSchema)) {
! where += " AND n2.nspname = '"+escapeQuotes(foreignSchema.toLowerCase())+"' ";
}
} else {
select = "SELECT DISTINCT NULL::text as pnspname, NULL::text as fnspname, ";
--- 3002,3011 ----
from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_namespace n2, pg_catalog.pg_trigger t,
pg_catalog.pg_triggert1, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_class ic, pg_catalog.pg_proc p1,
pg_catalog.pg_procp2, pg_catalog.pg_index i, pg_catalog.pg_attribute a ";
where = " AND c.relnamespace = n.oid AND c2.relnamespace=n2.oid ";
if (primarySchema != null && !"".equals(primarySchema)) {
! where += " AND n.nspname = '"+escapeQuotes(primarySchema)+"' ";
}
if (foreignSchema != null && !"".equals(foreignSchema)) {
! where += " AND n2.nspname = '"+escapeQuotes(foreignSchema)+"' ";
}
} else {
select = "SELECT DISTINCT NULL::text as pnspname, NULL::text as fnspname, ";
***************
*** 3052,3061 ****
+ where;
if (primaryTable != null) {
! sql += "AND c.relname='" + escapeQuotes(primaryTable.toLowerCase()) + "' ";
}
if (foreignTable != null) {
! sql += "AND c2.relname='" + escapeQuotes(foreignTable.toLowerCase()) + "' ";
}
sql += "ORDER BY ";
--- 3046,3055 ----
+ where;
if (primaryTable != null) {
! sql += "AND c.relname='" + escapeQuotes(primaryTable) + "' ";
}
if (foreignTable != null) {
! sql += "AND c2.relname='" + escapeQuotes(foreignTable) + "' ";
}
sql += "ORDER BY ";
***************
*** 3548,3554 ****
from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_indexi, pg_catalog.pg_attribute a, pg_catalog.pg_am am ";
where = " AND n.oid = ct.relnamespace ";
if (schema != null && ! "".equals(schema)) {
! where += " AND n.nspname = '"+escapeQuotes(schema.toLowerCase())+"' ";
}
} else {
select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, ";
--- 3542,3548 ----
from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_indexi, pg_catalog.pg_attribute a, pg_catalog.pg_am am ";
where = " AND n.oid = ct.relnamespace ";
if (schema != null && ! "".equals(schema)) {
! where += " AND n.nspname = '"+escapeQuotes(schema)+"' ";
}
} else {
select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, ";
***************
*** 3573,3579 ****
from+
" WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid AND a.attrelid=ci.oid AND ci.relam=am.oid "+
where+
! " AND ct.relname = '"+escapeQuotes(tableName.toLowerCase())+"' ";
if (unique) {
sql += " AND i.indisunique ";
--- 3567,3573 ----
from+
" WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid AND a.attrelid=ci.oid AND ci.relam=am.oid "+
where+
! " AND ct.relname = '"+escapeQuotes(tableName)+"' ";
if (unique) {
sql += " AND i.indisunique ";
Patch applied.
Kris Jurka wrote:
> I have given this some further thought and believe that we should not do
> any case folding in the driver for DatabaseMetaData calls. We cannot
> guess what the caller's intention is regarding case handling, so instead
> the driver provides the necessary methods to let the user know what the
> database does with regard to case. The following methods allow the
> caller to do their own case folding if necessary:
>
> storesLowerCaseIdentifiers
> storesLowerCaseQuotedIdentifiers
> storesMixedCaseIdentifiers
> storesMixedCaseQuotedIdentifiers
> storesUpperCaseIdentifiers
> storesUpperCaseQuotedIdentifiers
> supportsMixedCaseIdentifiers
> supportsMixedCaseQuotedIdentifiers
>
> The attached patch removes the existing case folding in the driver.
>
>
> Barry Lind wrote:
>
>>Kris,
>>
>>I can't think of anything better. So yes please submit a patch for this.
>>
>>thanks,
>>--Barry
>>
>>Kris Jurka wrote:
>>
>>>This has not been fixed. Currently the driver is doing something along
>>>the lines of
>>>
>>>" WHERE c.relname LIKE " + tableNamePattern.toLowerCase();
>>>
>>>This masks the problem of a user supplying an uppercase version of a
>>>tablename that was case folded to lower, but does not work when they
>>>really want an uppercase table name.
>>>
>>>I believe that adding a LOWER() to c.relname is the best temporary
>>>solution until we figure out a better way to handle case sensitivity.
>>>
>>>Barry, If you believe this is the way to go I will work up a patch for
>>>this an similar places in the driver.
>>>
>>>Kris Jurka
>>>
>>>
>>>On Tue, 29 Oct 2002, Barry Lind wrote:
>>>
>>>
>>>
>>>>Wolfgang,
>>>>
>>>>Can you try this test using the 7.3beta3 build of the driver from
>>>>jdbc.postgresql.org? I think this should be fixed in the latest build.
>>>>
>>>>thanks,
>>>>--Barry
>>>>
>>>>
>>>>w.winter wrote:
>>>>
>>>>
>>>>>Hi,
>>>>>
>>>>>on testing our auto-configuration persistence framework ACP against
>>>>>PostgreSQL we found the following problem:
>>>>>
>>>>>There is a table A625431658_Person1, created as quoted mixed case
>>>>>identifier.
>>>>>
>>>>>I do:
>>>>>databaseMetaData.getTables(null, null, "%", new String[] {"TABLE"})
>>>>>--> A625431658_Person1 is in the ResultSet. OK.
>>>>>
>>>>>I do:
>>>>>databaseMetaData.getTables(null, null, "A625431658_Person1", new String[]
>>>>>{"TABLE"})
>>>>>--> A625431658_Person1 is NOT in the ResultSet.
>>>>>
>>>>>I do:
>>>>>databaseMetaData.getTables(null, null, "%erson1", new String[] {"TABLE"})
>>>>>--> A625431658_Person1 is in the ResultSet. OK.
>>>>>
>>>>>Seems there is a problem with mixed case ?
>>>>>
>>>>>
>>>>>Wolfgang
>>>>>
>>>>>___________________
>>>>>Dr. Wolfgang Winter
>>>>>LogiTags Systems
>>>>>www.logitags.com
>>>>>
>>>>>
>>>>>------------------------------------------------------------------------
>>>>>
>>>>>? src/interfaces/jdbc/org/postgresql/Driver.java
>>>>>Index: src/interfaces/jdbc/org/postgresql//jdbc1/AbstractJdbc1DatabaseMetaData.java
>>>>>===================================================================
>>>>>RCS file:
/projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java,v
>>>>>retrieving revision 1.9
>>>>>diff -c -r1.9 AbstractJdbc1DatabaseMetaData.java
>>>>>*** src/interfaces/jdbc/org/postgresql//jdbc1/AbstractJdbc1DatabaseMetaData.java 2002/10/25 02:54:37 1.9
>>>>>--- src/interfaces/jdbc/org/postgresql//jdbc1/AbstractJdbc1DatabaseMetaData.java 2002/11/02 22:26:58
>>>>>***************
>>>>>*** 314,323 ****
>>>>> * as case sensitive and as a result store them in mixed case?
>>>>> * A JDBC-Compliant driver will always return false.
>>>>> *
>>>>>- * <p>Predicament - what do they mean by "SQL identifiers" - if it
>>>>>- * means the names of the tables and columns, then the answers
>>>>>- * given below are correct - otherwise I don't know.
>>>>>- *
>>>>> * @return true if so
>>>>> * @exception SQLException if a database access error occurs
>>>>> */
>>>>>--- 314,319 ----
>>>>>***************
>>>>>*** 1725,1734 ****
>>>>> " LEFT JOIN pg_catalog.pg_namespace pn ON (c.relnamespace=pn.oid AND pn.nspname='pg_catalog') "+
>>>>> " WHERE p.pronamespace=n.oid ";
>>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
>>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
>>>>> }
>>>>> if (procedureNamePattern != null) {
>>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
>>>>> }
>>>>> sql += " ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME ";
>>>>> } else if (connection.haveMinimumServerVersion("7.1")) {
>>>>>--- 1721,1730 ----
>>>>> " LEFT JOIN pg_catalog.pg_namespace pn ON (c.relnamespace=pn.oid AND pn.nspname='pg_catalog') "+
>>>>> " WHERE p.pronamespace=n.oid ";
>>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
>>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
>>>>> }
>>>>> if (procedureNamePattern != null) {
>>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
>>>>> }
>>>>> sql += " ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME ";
>>>>> } else if (connection.haveMinimumServerVersion("7.1")) {
>>>>>***************
>>>>>*** 1737,1750 ****
>>>>> " LEFT JOIN pg_description d ON (p.oid=d.objoid) "+
>>>>> " LEFT JOIN pg_class c ON (d.classoid=c.oid AND c.relname='pg_proc') ";
>>>>> if (procedureNamePattern != null) {
>>>>>! sql += " WHERE p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
>>>>> }
>>>>> sql += " ORDER BY PROCEDURE_NAME ";
>>>>> } else {
>>>>> sql = "SELECT NULL AS PROCEDURE_CAT, NULL AS PROCEDURE_SCHEM, p.proname AS PROCEDURE_NAME, NULL,
NULL,NULL, NULL AS REMARKS, "+java.sql.DatabaseMetaData.procedureReturnsResult+" AS PROCEDURE_TYPE "+
>>>>> " FROM pg_proc p ";
>>>>> if (procedureNamePattern != null) {
>>>>>! sql += " WHERE p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
>>>>> }
>>>>> sql += " ORDER BY PROCEDURE_NAME ";
>>>>> }
>>>>>--- 1733,1746 ----
>>>>> " LEFT JOIN pg_description d ON (p.oid=d.objoid) "+
>>>>> " LEFT JOIN pg_class c ON (d.classoid=c.oid AND c.relname='pg_proc') ";
>>>>> if (procedureNamePattern != null) {
>>>>>! sql += " WHERE p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
>>>>> }
>>>>> sql += " ORDER BY PROCEDURE_NAME ";
>>>>> } else {
>>>>> sql = "SELECT NULL AS PROCEDURE_CAT, NULL AS PROCEDURE_SCHEM, p.proname AS PROCEDURE_NAME, NULL,
NULL,NULL, NULL AS REMARKS, "+java.sql.DatabaseMetaData.procedureReturnsResult+" AS PROCEDURE_TYPE "+
>>>>> " FROM pg_proc p ";
>>>>> if (procedureNamePattern != null) {
>>>>>! sql += " WHERE p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
>>>>> }
>>>>> sql += " ORDER BY PROCEDURE_NAME ";
>>>>> }
>>>>>***************
>>>>>*** 1822,1831 ****
>>>>> " 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 && !"".equals(schemaPattern)) {
>>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
>>>>> }
>>>>> if (procedureNamePattern != null) {
>>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
>>>>> }
>>>>> sql += " ORDER BY n.nspname, p.proname ";
>>>>> } else {
>>>>>--- 1818,1827 ----
>>>>> " 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 && !"".equals(schemaPattern)) {
>>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
>>>>> }
>>>>> if (procedureNamePattern != null) {
>>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
>>>>> }
>>>>> sql += " ORDER BY n.nspname, p.proname ";
>>>>> } else {
>>>>>***************
>>>>>*** 1833,1839 ****
>>>>> " FROM pg_proc p,pg_type t "+
>>>>> " WHERE p.prorettype=t.oid ";
>>>>> if (procedureNamePattern != null) {
>>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
>>>>> }
>>>>> sql += " ORDER BY p.proname ";
>>>>> }
>>>>>--- 1829,1835 ----
>>>>> " FROM pg_proc p,pg_type t "+
>>>>> " WHERE p.prorettype=t.oid ";
>>>>> if (procedureNamePattern != null) {
>>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
>>>>> }
>>>>> sql += " ORDER BY p.proname ";
>>>>> }
>>>>>***************
>>>>>*** 1997,2006 ****
>>>>> " LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog') "+
>>>>> " WHERE c.relnamespace = n.oid ";
>>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
>>>>>! select += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
>>>>> }
>>>>> if (tableNamePattern != null) {
>>>>>! select += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern.toLowerCase())+"' ";
>>>>> }
>>>>> orderby = " ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME ";
>>>>> } else {
>>>>>--- 1993,2002 ----
>>>>> " LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog') "+
>>>>> " WHERE c.relnamespace = n.oid ";
>>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
>>>>>! select += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
>>>>> }
>>>>> if (tableNamePattern != null) {
>>>>>! select += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern)+"' ";
>>>>> }
>>>>> orderby = " ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME ";
>>>>> } else {
>>>>>***************
>>>>>*** 2290,2296 ****
>>>>> " LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog')
"+
>>>>> " WHERE a.attnum > 0 AND NOT a.attisdropped ";
>>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
>>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
>>>>> }
>>>>> } else if (connection.haveMinimumServerVersion("7.1")) {
>>>>> sql = "SELECT NULL::text AS
nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description"+
>>>>>--- 2286,2292 ----
>>>>> " LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog')
"+
>>>>> " WHERE a.attnum > 0 AND NOT a.attisdropped ";
>>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
>>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
>>>>> }
>>>>> } else if (connection.haveMinimumServerVersion("7.1")) {
>>>>> sql = "SELECT NULL::text AS
nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description"+
>>>>>***************
>>>>>*** 2308,2317 ****
>>>>> }
>>>>>
>>>>> if (tableNamePattern != null && !"".equals(tableNamePattern)) {
>>>>>! sql += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern.toLowerCase())+"' ";
>>>>> }
>>>>> if (columnNamePattern != null && !"".equals(columnNamePattern)) {
>>>>>! sql += " AND a.attname LIKE '"+escapeQuotes(columnNamePattern.toLowerCase())+"' ";
>>>>> }
>>>>> sql += " ORDER BY nspname,relname,attname ";
>>>>>
>>>>>--- 2304,2313 ----
>>>>> }
>>>>>
>>>>> if (tableNamePattern != null && !"".equals(tableNamePattern)) {
>>>>>! sql += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern)+"' ";
>>>>> }
>>>>> if (columnNamePattern != null && !"".equals(columnNamePattern)) {
>>>>>! sql += " AND a.attname LIKE '"+escapeQuotes(columnNamePattern)+"' ";
>>>>> }
>>>>> sql += " ORDER BY nspname,relname,attname ";
>>>>>
>>>>>***************
>>>>>*** 2410,2417 ****
>>>>>
>>>>> if (columnNamePattern == null)
>>>>> columnNamePattern = "%";
>>>>>- else
>>>>>- columnNamePattern = columnNamePattern.toLowerCase();
>>>>>
>>>>> f[0] = new Field(connection, "TABLE_CAT", iVarcharOid, getMaxNameLength());
>>>>> f[1] = new Field(connection, "TABLE_SCHEM", iVarcharOid, getMaxNameLength());
>>>>>--- 2406,2411 ----
>>>>>***************
>>>>>*** 2432,2438 ****
>>>>> " AND c.relkind = 'r' "+
>>>>> " AND a.attnum > 0 AND NOT a.attisdropped ";
>>>>> if (schema != null && !"".equals(schema)) {
>>>>>! sql += " AND n.nspname = '"+escapeQuotes(schema.toLowerCase())+"' ";
>>>>> }
>>>>> } else {
>>>>> sql = "SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl,a.attname "+
>>>>>--- 2426,2432 ----
>>>>> " AND c.relkind = 'r' "+
>>>>> " AND a.attnum > 0 AND NOT a.attisdropped ";
>>>>> if (schema != null && !"".equals(schema)) {
>>>>>! sql += " AND n.nspname = '"+escapeQuotes(schema)+"' ";
>>>>> }
>>>>> } else {
>>>>> sql = "SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl,a.attname "+
>>>>>***************
>>>>>*** 2443,2451 ****
>>>>> " AND c.relkind = 'r' ";
>>>>> }
>>>>>
>>>>>! sql += " AND c.relname = '"+escapeQuotes(table.toLowerCase())+"' ";
>>>>> if (columnNamePattern != null && !"".equals(columnNamePattern)) {
>>>>>! sql += " AND a.attname LIKE '"+escapeQuotes(columnNamePattern.toLowerCase())+"' ";
>>>>> }
>>>>> sql += " ORDER BY attname ";
>>>>>
>>>>>--- 2437,2445 ----
>>>>> " AND c.relkind = 'r' ";
>>>>> }
>>>>>
>>>>>! sql += " AND c.relname = '"+escapeQuotes(table)+"' ";
>>>>> if (columnNamePattern != null && !"".equals(columnNamePattern)) {
>>>>>! sql += " AND a.attname LIKE '"+escapeQuotes(columnNamePattern)+"' ";
>>>>> }
>>>>> sql += " ORDER BY attname ";
>>>>>
>>>>>***************
>>>>>*** 2538,2544 ****
>>>>> " AND u.usesysid = c.relowner "+
>>>>> " AND c.relkind = 'r' ";
>>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
>>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
>>>>> }
>>>>> } else {
>>>>> sql = "SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl "+
>>>>>--- 2532,2538 ----
>>>>> " AND u.usesysid = c.relowner "+
>>>>> " AND c.relkind = 'r' ";
>>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
>>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
>>>>> }
>>>>> } else {
>>>>> sql = "SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl "+
>>>>>***************
>>>>>*** 2548,2554 ****
>>>>> }
>>>>>
>>>>> if (tableNamePattern != null && !"".equals(tableNamePattern)) {
>>>>>! sql += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern.toLowerCase())+"' ";
>>>>> }
>>>>> sql += " ORDER BY nspname, relname ";
>>>>>
>>>>>--- 2542,2548 ----
>>>>> }
>>>>>
>>>>> if (tableNamePattern != null && !"".equals(tableNamePattern)) {
>>>>>! sql += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern)+"' ";
>>>>> }
>>>>> sql += " ORDER BY nspname, relname ";
>>>>>
>>>>>***************
>>>>>*** 2752,2758 ****
>>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_attributea, pg_catalog.pg_index i ";
>>>>> where = " AND ct.relnamespace = n.oid ";
>>>>> if (schema != null && !"".equals(schema)) {
>>>>>! where += " AND n.nspname = '"+escapeQuotes(schema.toLowerCase())+"' ";
>>>>> }
>>>>> } else {
>>>>> from = " FROM pg_class ct, pg_class ci, pg_attribute a, pg_index i ";
>>>>>--- 2746,2752 ----
>>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_attributea, pg_catalog.pg_index i ";
>>>>> where = " AND ct.relnamespace = n.oid ";
>>>>> if (schema != null && !"".equals(schema)) {
>>>>>! where += " AND n.nspname = '"+escapeQuotes(schema)+"' ";
>>>>> }
>>>>> } else {
>>>>> from = " FROM pg_class ct, pg_class ci, pg_attribute a, pg_index i ";
>>>>>***************
>>>>>*** 2761,2767 ****
>>>>> from+
>>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid "+
>>>>> " AND a.attrelid=ci.oid AND i.indisprimary "+
>>>>>! " AND ct.relname = '"+escapeQuotes(table.toLowerCase())+"' "+
>>>>> where+
>>>>> " ORDER BY a.attnum ";
>>>>>
>>>>>--- 2755,2761 ----
>>>>> from+
>>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid "+
>>>>> " AND a.attrelid=ci.oid AND i.indisprimary "+
>>>>>! " AND ct.relname = '"+escapeQuotes(table)+"' "+
>>>>> where+
>>>>> " ORDER BY a.attnum ";
>>>>>
>>>>>***************
>>>>>*** 2882,2888 ****
>>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_attributea, pg_catalog.pg_index i ";
>>>>> where = " AND ct.relnamespace = n.oid ";
>>>>> if (schema != null && !"".equals(schema)) {
>>>>>! where += " AND n.nspname = '"+escapeQuotes(schema.toLowerCase())+"' ";
>>>>> }
>>>>> } else {
>>>>> select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, ";
>>>>>--- 2876,2882 ----
>>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_attributea, pg_catalog.pg_index i ";
>>>>> where = " AND ct.relnamespace = n.oid ";
>>>>> if (schema != null && !"".equals(schema)) {
>>>>>! where += " AND n.nspname = '"+escapeQuotes(schema)+"' ";
>>>>> }
>>>>> } else {
>>>>> select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, ";
>>>>>***************
>>>>>*** 2896,2902 ****
>>>>> from+
>>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid "+
>>>>> " AND a.attrelid=ci.oid AND i.indisprimary "+
>>>>>! " AND ct.relname = '"+escapeQuotes(table.toLowerCase())+"' "+
>>>>> where+
>>>>> " ORDER BY table_name, pk_name, key_seq";
>>>>> return connection.createStatement().executeQuery(sql);
>>>>>--- 2890,2896 ----
>>>>> from+
>>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid "+
>>>>> " AND a.attrelid=ci.oid AND i.indisprimary "+
>>>>>! " AND ct.relname = '"+escapeQuotes(table)+"' "+
>>>>> where+
>>>>> " ORDER BY table_name, pk_name, key_seq";
>>>>> return connection.createStatement().executeQuery(sql);
>>>>>***************
>>>>>*** 3008,3017 ****
>>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_namespace n2, pg_catalog.pg_trigger t,
pg_catalog.pg_triggert1, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_class ic, pg_catalog.pg_proc p1,
pg_catalog.pg_procp2, pg_catalog.pg_index i, pg_catalog.pg_attribute a ";
>>>>> where = " AND c.relnamespace = n.oid AND c2.relnamespace=n2.oid ";
>>>>> if (primarySchema != null && !"".equals(primarySchema)) {
>>>>>! where += " AND n.nspname = '"+escapeQuotes(primarySchema.toLowerCase())+"' ";
>>>>> }
>>>>> if (foreignSchema != null && !"".equals(foreignSchema)) {
>>>>>! where += " AND n2.nspname = '"+escapeQuotes(foreignSchema.toLowerCase())+"' ";
>>>>> }
>>>>> } else {
>>>>> select = "SELECT DISTINCT NULL::text as pnspname, NULL::text as fnspname, ";
>>>>>--- 3002,3011 ----
>>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_namespace n2, pg_catalog.pg_trigger t,
pg_catalog.pg_triggert1, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_class ic, pg_catalog.pg_proc p1,
pg_catalog.pg_procp2, pg_catalog.pg_index i, pg_catalog.pg_attribute a ";
>>>>> where = " AND c.relnamespace = n.oid AND c2.relnamespace=n2.oid ";
>>>>> if (primarySchema != null && !"".equals(primarySchema)) {
>>>>>! where += " AND n.nspname = '"+escapeQuotes(primarySchema)+"' ";
>>>>> }
>>>>> if (foreignSchema != null && !"".equals(foreignSchema)) {
>>>>>! where += " AND n2.nspname = '"+escapeQuotes(foreignSchema)+"' ";
>>>>> }
>>>>> } else {
>>>>> select = "SELECT DISTINCT NULL::text as pnspname, NULL::text as fnspname, ";
>>>>>***************
>>>>>*** 3052,3061 ****
>>>>> + where;
>>>>>
>>>>> if (primaryTable != null) {
>>>>>! sql += "AND c.relname='" + escapeQuotes(primaryTable.toLowerCase()) + "' ";
>>>>> }
>>>>> if (foreignTable != null) {
>>>>>! sql += "AND c2.relname='" + escapeQuotes(foreignTable.toLowerCase()) + "' ";
>>>>> }
>>>>>
>>>>> sql += "ORDER BY ";
>>>>>--- 3046,3055 ----
>>>>> + where;
>>>>>
>>>>> if (primaryTable != null) {
>>>>>! sql += "AND c.relname='" + escapeQuotes(primaryTable) + "' ";
>>>>> }
>>>>> if (foreignTable != null) {
>>>>>! sql += "AND c2.relname='" + escapeQuotes(foreignTable) + "' ";
>>>>> }
>>>>>
>>>>> sql += "ORDER BY ";
>>>>>***************
>>>>>*** 3548,3554 ****
>>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_indexi, pg_catalog.pg_attribute a, pg_catalog.pg_am am ";
>>>>> where = " AND n.oid = ct.relnamespace ";
>>>>> if (schema != null && ! "".equals(schema)) {
>>>>>! where += " AND n.nspname = '"+escapeQuotes(schema.toLowerCase())+"' ";
>>>>> }
>>>>> } else {
>>>>> select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, ";
>>>>>--- 3542,3548 ----
>>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_indexi, pg_catalog.pg_attribute a, pg_catalog.pg_am am ";
>>>>> where = " AND n.oid = ct.relnamespace ";
>>>>> if (schema != null && ! "".equals(schema)) {
>>>>>! where += " AND n.nspname = '"+escapeQuotes(schema)+"' ";
>>>>> }
>>>>> } else {
>>>>> select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, ";
>>>>>***************
>>>>>*** 3573,3579 ****
>>>>> from+
>>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid AND a.attrelid=ci.oid AND ci.relam=am.oid "+
>>>>> where+
>>>>>! " AND ct.relname = '"+escapeQuotes(tableName.toLowerCase())+"' ";
>>>>>
>>>>> if (unique) {
>>>>> sql += " AND i.indisunique ";
>>>>>--- 3567,3573 ----
>>>>> from+
>>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid AND a.attrelid=ci.oid AND ci.relam=am.oid "+
>>>>> where+
>>>>>! " AND ct.relname = '"+escapeQuotes(tableName)+"' ";
>>>>>
>>>>> if (unique) {
>>>>> sql += " AND i.indisunique ";
>>>>>
>>>>>
>>>>>------------------------------------------------------------------------
>>>>>
>>>>>
>>>>>---------------------------(end of broadcast)---------------------------
>>>>>TIP 4: Don't 'kill -9' the postmaster
>>>>
While I fully endorse this patch, I'm willing to bet this is going to
break some code out there.
Regards,
Dave
On Mon, 2002-11-04 at 02:44, Barry Lind wrote:
> Patch applied.
>
>
> Kris Jurka wrote:
> > I have given this some further thought and believe that we should not do
> > any case folding in the driver for DatabaseMetaData calls. We cannot
> > guess what the caller's intention is regarding case handling, so instead
> > the driver provides the necessary methods to let the user know what the
> > database does with regard to case. The following methods allow the
> > caller to do their own case folding if necessary:
> >
> > storesLowerCaseIdentifiers
> > storesLowerCaseQuotedIdentifiers
> > storesMixedCaseIdentifiers
> > storesMixedCaseQuotedIdentifiers
> > storesUpperCaseIdentifiers
> > storesUpperCaseQuotedIdentifiers
> > supportsMixedCaseIdentifiers
> > supportsMixedCaseQuotedIdentifiers
> >
> > The attached patch removes the existing case folding in the driver.
> >
> >
> > Barry Lind wrote:
> >
> >>Kris,
> >>
> >>I can't think of anything better. So yes please submit a patch for this.
> >>
> >>thanks,
> >>--Barry
> >>
> >>Kris Jurka wrote:
> >>
> >>>This has not been fixed. Currently the driver is doing something along
> >>>the lines of
> >>>
> >>>" WHERE c.relname LIKE " + tableNamePattern.toLowerCase();
> >>>
> >>>This masks the problem of a user supplying an uppercase version of a
> >>>tablename that was case folded to lower, but does not work when they
> >>>really want an uppercase table name.
> >>>
> >>>I believe that adding a LOWER() to c.relname is the best temporary
> >>>solution until we figure out a better way to handle case sensitivity.
> >>>
> >>>Barry, If you believe this is the way to go I will work up a patch for
> >>>this an similar places in the driver.
> >>>
> >>>Kris Jurka
> >>>
> >>>
> >>>On Tue, 29 Oct 2002, Barry Lind wrote:
> >>>
> >>>
> >>>
> >>>>Wolfgang,
> >>>>
> >>>>Can you try this test using the 7.3beta3 build of the driver from
> >>>>jdbc.postgresql.org? I think this should be fixed in the latest build.
> >>>>
> >>>>thanks,
> >>>>--Barry
> >>>>
> >>>>
> >>>>w.winter wrote:
> >>>>
> >>>>
> >>>>>Hi,
> >>>>>
> >>>>>on testing our auto-configuration persistence framework ACP against
> >>>>>PostgreSQL we found the following problem:
> >>>>>
> >>>>>There is a table A625431658_Person1, created as quoted mixed case
> >>>>>identifier.
> >>>>>
> >>>>>I do:
> >>>>>databaseMetaData.getTables(null, null, "%", new String[] {"TABLE"})
> >>>>>--> A625431658_Person1 is in the ResultSet. OK.
> >>>>>
> >>>>>I do:
> >>>>>databaseMetaData.getTables(null, null, "A625431658_Person1", new String[]
> >>>>>{"TABLE"})
> >>>>>--> A625431658_Person1 is NOT in the ResultSet.
> >>>>>
> >>>>>I do:
> >>>>>databaseMetaData.getTables(null, null, "%erson1", new String[] {"TABLE"})
> >>>>>--> A625431658_Person1 is in the ResultSet. OK.
> >>>>>
> >>>>>Seems there is a problem with mixed case ?
> >>>>>
> >>>>>
> >>>>>Wolfgang
> >>>>>
> >>>>>___________________
> >>>>>Dr. Wolfgang Winter
> >>>>>LogiTags Systems
> >>>>>www.logitags.com
> >>>>>
> >>>>>
> >>>>>------------------------------------------------------------------------
> >>>>>
> >>>>>? src/interfaces/jdbc/org/postgresql/Driver.java
> >>>>>Index: src/interfaces/jdbc/org/postgresql//jdbc1/AbstractJdbc1DatabaseMetaData.java
> >>>>>===================================================================
> >>>>>RCS file:
/projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java,v
> >>>>>retrieving revision 1.9
> >>>>>diff -c -r1.9 AbstractJdbc1DatabaseMetaData.java
> >>>>>*** src/interfaces/jdbc/org/postgresql//jdbc1/AbstractJdbc1DatabaseMetaData.java 2002/10/25 02:54:37 1.9
> >>>>>--- src/interfaces/jdbc/org/postgresql//jdbc1/AbstractJdbc1DatabaseMetaData.java 2002/11/02 22:26:58
> >>>>>***************
> >>>>>*** 314,323 ****
> >>>>> * as case sensitive and as a result store them in mixed case?
> >>>>> * A JDBC-Compliant driver will always return false.
> >>>>> *
> >>>>>- * <p>Predicament - what do they mean by "SQL identifiers" - if it
> >>>>>- * means the names of the tables and columns, then the answers
> >>>>>- * given below are correct - otherwise I don't know.
> >>>>>- *
> >>>>> * @return true if so
> >>>>> * @exception SQLException if a database access error occurs
> >>>>> */
> >>>>>--- 314,319 ----
> >>>>>***************
> >>>>>*** 1725,1734 ****
> >>>>> " LEFT JOIN pg_catalog.pg_namespace pn ON (c.relnamespace=pn.oid AND pn.nspname='pg_catalog')
"+
> >>>>> " WHERE p.pronamespace=n.oid ";
> >>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
> >>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> if (procedureNamePattern != null) {
> >>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME ";
> >>>>> } else if (connection.haveMinimumServerVersion("7.1")) {
> >>>>>--- 1721,1730 ----
> >>>>> " LEFT JOIN pg_catalog.pg_namespace pn ON (c.relnamespace=pn.oid AND pn.nspname='pg_catalog')
"+
> >>>>> " WHERE p.pronamespace=n.oid ";
> >>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
> >>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
> >>>>> }
> >>>>> if (procedureNamePattern != null) {
> >>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME ";
> >>>>> } else if (connection.haveMinimumServerVersion("7.1")) {
> >>>>>***************
> >>>>>*** 1737,1750 ****
> >>>>> " LEFT JOIN pg_description d ON (p.oid=d.objoid) "+
> >>>>> " LEFT JOIN pg_class c ON (d.classoid=c.oid AND c.relname='pg_proc') ";
> >>>>> if (procedureNamePattern != null) {
> >>>>>! sql += " WHERE p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY PROCEDURE_NAME ";
> >>>>> } else {
> >>>>> sql = "SELECT NULL AS PROCEDURE_CAT, NULL AS PROCEDURE_SCHEM, p.proname AS PROCEDURE_NAME, NULL,
NULL,NULL, NULL AS REMARKS, "+java.sql.DatabaseMetaData.procedureReturnsResult+" AS PROCEDURE_TYPE "+
> >>>>> " FROM pg_proc p ";
> >>>>> if (procedureNamePattern != null) {
> >>>>>! sql += " WHERE p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY PROCEDURE_NAME ";
> >>>>> }
> >>>>>--- 1733,1746 ----
> >>>>> " LEFT JOIN pg_description d ON (p.oid=d.objoid) "+
> >>>>> " LEFT JOIN pg_class c ON (d.classoid=c.oid AND c.relname='pg_proc') ";
> >>>>> if (procedureNamePattern != null) {
> >>>>>! sql += " WHERE p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY PROCEDURE_NAME ";
> >>>>> } else {
> >>>>> sql = "SELECT NULL AS PROCEDURE_CAT, NULL AS PROCEDURE_SCHEM, p.proname AS PROCEDURE_NAME, NULL,
NULL,NULL, NULL AS REMARKS, "+java.sql.DatabaseMetaData.procedureReturnsResult+" AS PROCEDURE_TYPE "+
> >>>>> " FROM pg_proc p ";
> >>>>> if (procedureNamePattern != null) {
> >>>>>! sql += " WHERE p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY PROCEDURE_NAME ";
> >>>>> }
> >>>>>***************
> >>>>>*** 1822,1831 ****
> >>>>> " 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 && !"".equals(schemaPattern)) {
> >>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> if (procedureNamePattern != null) {
> >>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY n.nspname, p.proname ";
> >>>>> } else {
> >>>>>--- 1818,1827 ----
> >>>>> " 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 && !"".equals(schemaPattern)) {
> >>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
> >>>>> }
> >>>>> if (procedureNamePattern != null) {
> >>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY n.nspname, p.proname ";
> >>>>> } else {
> >>>>>***************
> >>>>>*** 1833,1839 ****
> >>>>> " FROM pg_proc p,pg_type t "+
> >>>>> " WHERE p.prorettype=t.oid ";
> >>>>> if (procedureNamePattern != null) {
> >>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY p.proname ";
> >>>>> }
> >>>>>--- 1829,1835 ----
> >>>>> " FROM pg_proc p,pg_type t "+
> >>>>> " WHERE p.prorettype=t.oid ";
> >>>>> if (procedureNamePattern != null) {
> >>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY p.proname ";
> >>>>> }
> >>>>>***************
> >>>>>*** 1997,2006 ****
> >>>>> " LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog') "+
> >>>>> " WHERE c.relnamespace = n.oid ";
> >>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
> >>>>>! select += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> if (tableNamePattern != null) {
> >>>>>! select += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> orderby = " ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME ";
> >>>>> } else {
> >>>>>--- 1993,2002 ----
> >>>>> " LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog') "+
> >>>>> " WHERE c.relnamespace = n.oid ";
> >>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
> >>>>>! select += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
> >>>>> }
> >>>>> if (tableNamePattern != null) {
> >>>>>! select += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern)+"' ";
> >>>>> }
> >>>>> orderby = " ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME ";
> >>>>> } else {
> >>>>>***************
> >>>>>*** 2290,2296 ****
> >>>>> " LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog')
"+
> >>>>> " WHERE a.attnum > 0 AND NOT a.attisdropped ";
> >>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
> >>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> } else if (connection.haveMinimumServerVersion("7.1")) {
> >>>>> sql = "SELECT NULL::text AS
nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description"+
> >>>>>--- 2286,2292 ----
> >>>>> " LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog')
"+
> >>>>> " WHERE a.attnum > 0 AND NOT a.attisdropped ";
> >>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
> >>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
> >>>>> }
> >>>>> } else if (connection.haveMinimumServerVersion("7.1")) {
> >>>>> sql = "SELECT NULL::text AS
nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description"+
> >>>>>***************
> >>>>>*** 2308,2317 ****
> >>>>> }
> >>>>>
> >>>>> if (tableNamePattern != null && !"".equals(tableNamePattern)) {
> >>>>>! sql += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> if (columnNamePattern != null && !"".equals(columnNamePattern)) {
> >>>>>! sql += " AND a.attname LIKE '"+escapeQuotes(columnNamePattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY nspname,relname,attname ";
> >>>>>
> >>>>>--- 2304,2313 ----
> >>>>> }
> >>>>>
> >>>>> if (tableNamePattern != null && !"".equals(tableNamePattern)) {
> >>>>>! sql += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern)+"' ";
> >>>>> }
> >>>>> if (columnNamePattern != null && !"".equals(columnNamePattern)) {
> >>>>>! sql += " AND a.attname LIKE '"+escapeQuotes(columnNamePattern)+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY nspname,relname,attname ";
> >>>>>
> >>>>>***************
> >>>>>*** 2410,2417 ****
> >>>>>
> >>>>> if (columnNamePattern == null)
> >>>>> columnNamePattern = "%";
> >>>>>- else
> >>>>>- columnNamePattern = columnNamePattern.toLowerCase();
> >>>>>
> >>>>> f[0] = new Field(connection, "TABLE_CAT", iVarcharOid, getMaxNameLength());
> >>>>> f[1] = new Field(connection, "TABLE_SCHEM", iVarcharOid, getMaxNameLength());
> >>>>>--- 2406,2411 ----
> >>>>>***************
> >>>>>*** 2432,2438 ****
> >>>>> " AND c.relkind = 'r' "+
> >>>>> " AND a.attnum > 0 AND NOT a.attisdropped ";
> >>>>> if (schema != null && !"".equals(schema)) {
> >>>>>! sql += " AND n.nspname = '"+escapeQuotes(schema.toLowerCase())+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> sql = "SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl,a.attname "+
> >>>>>--- 2426,2432 ----
> >>>>> " AND c.relkind = 'r' "+
> >>>>> " AND a.attnum > 0 AND NOT a.attisdropped ";
> >>>>> if (schema != null && !"".equals(schema)) {
> >>>>>! sql += " AND n.nspname = '"+escapeQuotes(schema)+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> sql = "SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl,a.attname "+
> >>>>>***************
> >>>>>*** 2443,2451 ****
> >>>>> " AND c.relkind = 'r' ";
> >>>>> }
> >>>>>
> >>>>>! sql += " AND c.relname = '"+escapeQuotes(table.toLowerCase())+"' ";
> >>>>> if (columnNamePattern != null && !"".equals(columnNamePattern)) {
> >>>>>! sql += " AND a.attname LIKE '"+escapeQuotes(columnNamePattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY attname ";
> >>>>>
> >>>>>--- 2437,2445 ----
> >>>>> " AND c.relkind = 'r' ";
> >>>>> }
> >>>>>
> >>>>>! sql += " AND c.relname = '"+escapeQuotes(table)+"' ";
> >>>>> if (columnNamePattern != null && !"".equals(columnNamePattern)) {
> >>>>>! sql += " AND a.attname LIKE '"+escapeQuotes(columnNamePattern)+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY attname ";
> >>>>>
> >>>>>***************
> >>>>>*** 2538,2544 ****
> >>>>> " AND u.usesysid = c.relowner "+
> >>>>> " AND c.relkind = 'r' ";
> >>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
> >>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> sql = "SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl "+
> >>>>>--- 2532,2538 ----
> >>>>> " AND u.usesysid = c.relowner "+
> >>>>> " AND c.relkind = 'r' ";
> >>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
> >>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> sql = "SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl "+
> >>>>>***************
> >>>>>*** 2548,2554 ****
> >>>>> }
> >>>>>
> >>>>> if (tableNamePattern != null && !"".equals(tableNamePattern)) {
> >>>>>! sql += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern.toLowerCase())+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY nspname, relname ";
> >>>>>
> >>>>>--- 2542,2548 ----
> >>>>> }
> >>>>>
> >>>>> if (tableNamePattern != null && !"".equals(tableNamePattern)) {
> >>>>>! sql += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern)+"' ";
> >>>>> }
> >>>>> sql += " ORDER BY nspname, relname ";
> >>>>>
> >>>>>***************
> >>>>>*** 2752,2758 ****
> >>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_attributea, pg_catalog.pg_index i ";
> >>>>> where = " AND ct.relnamespace = n.oid ";
> >>>>> if (schema != null && !"".equals(schema)) {
> >>>>>! where += " AND n.nspname = '"+escapeQuotes(schema.toLowerCase())+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> from = " FROM pg_class ct, pg_class ci, pg_attribute a, pg_index i ";
> >>>>>--- 2746,2752 ----
> >>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_attributea, pg_catalog.pg_index i ";
> >>>>> where = " AND ct.relnamespace = n.oid ";
> >>>>> if (schema != null && !"".equals(schema)) {
> >>>>>! where += " AND n.nspname = '"+escapeQuotes(schema)+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> from = " FROM pg_class ct, pg_class ci, pg_attribute a, pg_index i ";
> >>>>>***************
> >>>>>*** 2761,2767 ****
> >>>>> from+
> >>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid "+
> >>>>> " AND a.attrelid=ci.oid AND i.indisprimary "+
> >>>>>! " AND ct.relname = '"+escapeQuotes(table.toLowerCase())+"' "+
> >>>>> where+
> >>>>> " ORDER BY a.attnum ";
> >>>>>
> >>>>>--- 2755,2761 ----
> >>>>> from+
> >>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid "+
> >>>>> " AND a.attrelid=ci.oid AND i.indisprimary "+
> >>>>>! " AND ct.relname = '"+escapeQuotes(table)+"' "+
> >>>>> where+
> >>>>> " ORDER BY a.attnum ";
> >>>>>
> >>>>>***************
> >>>>>*** 2882,2888 ****
> >>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_attributea, pg_catalog.pg_index i ";
> >>>>> where = " AND ct.relnamespace = n.oid ";
> >>>>> if (schema != null && !"".equals(schema)) {
> >>>>>! where += " AND n.nspname = '"+escapeQuotes(schema.toLowerCase())+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, ";
> >>>>>--- 2876,2882 ----
> >>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_attributea, pg_catalog.pg_index i ";
> >>>>> where = " AND ct.relnamespace = n.oid ";
> >>>>> if (schema != null && !"".equals(schema)) {
> >>>>>! where += " AND n.nspname = '"+escapeQuotes(schema)+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, ";
> >>>>>***************
> >>>>>*** 2896,2902 ****
> >>>>> from+
> >>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid "+
> >>>>> " AND a.attrelid=ci.oid AND i.indisprimary "+
> >>>>>! " AND ct.relname = '"+escapeQuotes(table.toLowerCase())+"' "+
> >>>>> where+
> >>>>> " ORDER BY table_name, pk_name, key_seq";
> >>>>> return connection.createStatement().executeQuery(sql);
> >>>>>--- 2890,2896 ----
> >>>>> from+
> >>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid "+
> >>>>> " AND a.attrelid=ci.oid AND i.indisprimary "+
> >>>>>! " AND ct.relname = '"+escapeQuotes(table)+"' "+
> >>>>> where+
> >>>>> " ORDER BY table_name, pk_name, key_seq";
> >>>>> return connection.createStatement().executeQuery(sql);
> >>>>>***************
> >>>>>*** 3008,3017 ****
> >>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_namespace n2, pg_catalog.pg_trigger t,
pg_catalog.pg_triggert1, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_class ic, pg_catalog.pg_proc p1,
pg_catalog.pg_procp2, pg_catalog.pg_index i, pg_catalog.pg_attribute a ";
> >>>>> where = " AND c.relnamespace = n.oid AND c2.relnamespace=n2.oid ";
> >>>>> if (primarySchema != null && !"".equals(primarySchema)) {
> >>>>>! where += " AND n.nspname = '"+escapeQuotes(primarySchema.toLowerCase())+"' ";
> >>>>> }
> >>>>> if (foreignSchema != null && !"".equals(foreignSchema)) {
> >>>>>! where += " AND n2.nspname = '"+escapeQuotes(foreignSchema.toLowerCase())+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> select = "SELECT DISTINCT NULL::text as pnspname, NULL::text as fnspname, ";
> >>>>>--- 3002,3011 ----
> >>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_namespace n2, pg_catalog.pg_trigger t,
pg_catalog.pg_triggert1, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_class ic, pg_catalog.pg_proc p1,
pg_catalog.pg_procp2, pg_catalog.pg_index i, pg_catalog.pg_attribute a ";
> >>>>> where = " AND c.relnamespace = n.oid AND c2.relnamespace=n2.oid ";
> >>>>> if (primarySchema != null && !"".equals(primarySchema)) {
> >>>>>! where += " AND n.nspname = '"+escapeQuotes(primarySchema)+"' ";
> >>>>> }
> >>>>> if (foreignSchema != null && !"".equals(foreignSchema)) {
> >>>>>! where += " AND n2.nspname = '"+escapeQuotes(foreignSchema)+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> select = "SELECT DISTINCT NULL::text as pnspname, NULL::text as fnspname, ";
> >>>>>***************
> >>>>>*** 3052,3061 ****
> >>>>> + where;
> >>>>>
> >>>>> if (primaryTable != null) {
> >>>>>! sql += "AND c.relname='" + escapeQuotes(primaryTable.toLowerCase()) + "' ";
> >>>>> }
> >>>>> if (foreignTable != null) {
> >>>>>! sql += "AND c2.relname='" + escapeQuotes(foreignTable.toLowerCase()) + "' ";
> >>>>> }
> >>>>>
> >>>>> sql += "ORDER BY ";
> >>>>>--- 3046,3055 ----
> >>>>> + where;
> >>>>>
> >>>>> if (primaryTable != null) {
> >>>>>! sql += "AND c.relname='" + escapeQuotes(primaryTable) + "' ";
> >>>>> }
> >>>>> if (foreignTable != null) {
> >>>>>! sql += "AND c2.relname='" + escapeQuotes(foreignTable) + "' ";
> >>>>> }
> >>>>>
> >>>>> sql += "ORDER BY ";
> >>>>>***************
> >>>>>*** 3548,3554 ****
> >>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_indexi, pg_catalog.pg_attribute a, pg_catalog.pg_am am ";
> >>>>> where = " AND n.oid = ct.relnamespace ";
> >>>>> if (schema != null && ! "".equals(schema)) {
> >>>>>! where += " AND n.nspname = '"+escapeQuotes(schema.toLowerCase())+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, ";
> >>>>>--- 3542,3548 ----
> >>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_indexi, pg_catalog.pg_attribute a, pg_catalog.pg_am am ";
> >>>>> where = " AND n.oid = ct.relnamespace ";
> >>>>> if (schema != null && ! "".equals(schema)) {
> >>>>>! where += " AND n.nspname = '"+escapeQuotes(schema)+"' ";
> >>>>> }
> >>>>> } else {
> >>>>> select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, ";
> >>>>>***************
> >>>>>*** 3573,3579 ****
> >>>>> from+
> >>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid AND a.attrelid=ci.oid AND ci.relam=am.oid "+
> >>>>> where+
> >>>>>! " AND ct.relname = '"+escapeQuotes(tableName.toLowerCase())+"' ";
> >>>>>
> >>>>> if (unique) {
> >>>>> sql += " AND i.indisunique ";
> >>>>>--- 3567,3573 ----
> >>>>> from+
> >>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid AND a.attrelid=ci.oid AND ci.relam=am.oid "+
> >>>>> where+
> >>>>>! " AND ct.relname = '"+escapeQuotes(tableName)+"' ";
> >>>>>
> >>>>> if (unique) {
> >>>>> sql += " AND i.indisunique ";
> >>>>>
> >>>>>
> >>>>>------------------------------------------------------------------------
> >>>>>
> >>>>>
> >>>>>---------------------------(end of broadcast)---------------------------
> >>>>>TIP 4: Don't 'kill -9' the postmaster
> >>>>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
Dave,
I understand, but at least now we can explain why we do it the way we
do. We have been changing the behavior in this area slightly every
release without an overall plan as to where this was going. Now we at
least can explain and justify our implementation as being complient with
the spec and consistent. (even though it may not be what you expect)
thanks,
--Barry
Dave Cramer wrote:
> While I fully endorse this patch, I'm willing to bet this is going to
> break some code out there.
>
> Regards,
>
> Dave
> On Mon, 2002-11-04 at 02:44, Barry Lind wrote:
>
>>Patch applied.
>>
>>
>>Kris Jurka wrote:
>>
>>>I have given this some further thought and believe that we should not do
>>>any case folding in the driver for DatabaseMetaData calls. We cannot
>>>guess what the caller's intention is regarding case handling, so instead
>>>the driver provides the necessary methods to let the user know what the
>>>database does with regard to case. The following methods allow the
>>>caller to do their own case folding if necessary:
>>>
>>>storesLowerCaseIdentifiers
>>>storesLowerCaseQuotedIdentifiers
>>>storesMixedCaseIdentifiers
>>>storesMixedCaseQuotedIdentifiers
>>>storesUpperCaseIdentifiers
>>>storesUpperCaseQuotedIdentifiers
>>>supportsMixedCaseIdentifiers
>>>supportsMixedCaseQuotedIdentifiers
>>>
>>>The attached patch removes the existing case folding in the driver.
>>>
>>>
>>>Barry Lind wrote:
>>>
>>>
>>>>Kris,
>>>>
>>>>I can't think of anything better. So yes please submit a patch for this.
>>>>
>>>>thanks,
>>>>--Barry
>>>>
>>>>Kris Jurka wrote:
>>>>
>>>>
>>>>>This has not been fixed. Currently the driver is doing something along
>>>>>the lines of
>>>>>
>>>>>" WHERE c.relname LIKE " + tableNamePattern.toLowerCase();
>>>>>
>>>>>This masks the problem of a user supplying an uppercase version of a
>>>>>tablename that was case folded to lower, but does not work when they
>>>>>really want an uppercase table name.
>>>>>
>>>>>I believe that adding a LOWER() to c.relname is the best temporary
>>>>>solution until we figure out a better way to handle case sensitivity.
>>>>>
>>>>>Barry, If you believe this is the way to go I will work up a patch for
>>>>>this an similar places in the driver.
>>>>>
>>>>>Kris Jurka
>>>>>
>>>>>
>>>>>On Tue, 29 Oct 2002, Barry Lind wrote:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>Wolfgang,
>>>>>>
>>>>>>Can you try this test using the 7.3beta3 build of the driver from
>>>>>>jdbc.postgresql.org? I think this should be fixed in the latest build.
>>>>>>
>>>>>>thanks,
>>>>>>--Barry
>>>>>>
>>>>>>
>>>>>>w.winter wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>>Hi,
>>>>>>>
>>>>>>>on testing our auto-configuration persistence framework ACP against
>>>>>>>PostgreSQL we found the following problem:
>>>>>>>
>>>>>>>There is a table A625431658_Person1, created as quoted mixed case
>>>>>>>identifier.
>>>>>>>
>>>>>>>I do:
>>>>>>>databaseMetaData.getTables(null, null, "%", new String[] {"TABLE"})
>>>>>>>--> A625431658_Person1 is in the ResultSet. OK.
>>>>>>>
>>>>>>>I do:
>>>>>>>databaseMetaData.getTables(null, null, "A625431658_Person1", new String[]
>>>>>>>{"TABLE"})
>>>>>>>--> A625431658_Person1 is NOT in the ResultSet.
>>>>>>>
>>>>>>>I do:
>>>>>>>databaseMetaData.getTables(null, null, "%erson1", new String[] {"TABLE"})
>>>>>>>--> A625431658_Person1 is in the ResultSet. OK.
>>>>>>>
>>>>>>>Seems there is a problem with mixed case ?
>>>>>>>
>>>>>>>
>>>>>>>Wolfgang
>>>>>>>
>>>>>>>___________________
>>>>>>>Dr. Wolfgang Winter
>>>>>>>LogiTags Systems
>>>>>>>www.logitags.com
>>>>>>>
>>>>>>>
>>>>>>>------------------------------------------------------------------------
>>>>>>>
>>>>>>>? src/interfaces/jdbc/org/postgresql/Driver.java
>>>>>>>Index: src/interfaces/jdbc/org/postgresql//jdbc1/AbstractJdbc1DatabaseMetaData.java
>>>>>>>===================================================================
>>>>>>>RCS file:
/projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java,v
>>>>>>>retrieving revision 1.9
>>>>>>>diff -c -r1.9 AbstractJdbc1DatabaseMetaData.java
>>>>>>>*** src/interfaces/jdbc/org/postgresql//jdbc1/AbstractJdbc1DatabaseMetaData.java 2002/10/25 02:54:37 1.9
>>>>>>>--- src/interfaces/jdbc/org/postgresql//jdbc1/AbstractJdbc1DatabaseMetaData.java 2002/11/02 22:26:58
>>>>>>>***************
>>>>>>>*** 314,323 ****
>>>>>>> * as case sensitive and as a result store them in mixed case?
>>>>>>> * A JDBC-Compliant driver will always return false.
>>>>>>> *
>>>>>>>- * <p>Predicament - what do they mean by "SQL identifiers" - if it
>>>>>>>- * means the names of the tables and columns, then the answers
>>>>>>>- * given below are correct - otherwise I don't know.
>>>>>>>- *
>>>>>>> * @return true if so
>>>>>>> * @exception SQLException if a database access error occurs
>>>>>>> */
>>>>>>>--- 314,319 ----
>>>>>>>***************
>>>>>>>*** 1725,1734 ****
>>>>>>> " LEFT JOIN pg_catalog.pg_namespace pn ON (c.relnamespace=pn.oid AND pn.nspname='pg_catalog')
"+
>>>>>>> " WHERE p.pronamespace=n.oid ";
>>>>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
>>>>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
>>>>>>> }
>>>>>>> if (procedureNamePattern != null) {
>>>>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
>>>>>>> }
>>>>>>> sql += " ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME ";
>>>>>>> } else if (connection.haveMinimumServerVersion("7.1")) {
>>>>>>>--- 1721,1730 ----
>>>>>>> " LEFT JOIN pg_catalog.pg_namespace pn ON (c.relnamespace=pn.oid AND pn.nspname='pg_catalog')
"+
>>>>>>> " WHERE p.pronamespace=n.oid ";
>>>>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
>>>>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
>>>>>>> }
>>>>>>> if (procedureNamePattern != null) {
>>>>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
>>>>>>> }
>>>>>>> sql += " ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME ";
>>>>>>> } else if (connection.haveMinimumServerVersion("7.1")) {
>>>>>>>***************
>>>>>>>*** 1737,1750 ****
>>>>>>> " LEFT JOIN pg_description d ON (p.oid=d.objoid) "+
>>>>>>> " LEFT JOIN pg_class c ON (d.classoid=c.oid AND c.relname='pg_proc') ";
>>>>>>> if (procedureNamePattern != null) {
>>>>>>>! sql += " WHERE p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
>>>>>>> }
>>>>>>> sql += " ORDER BY PROCEDURE_NAME ";
>>>>>>> } else {
>>>>>>> sql = "SELECT NULL AS PROCEDURE_CAT, NULL AS PROCEDURE_SCHEM, p.proname AS PROCEDURE_NAME, NULL,
NULL,NULL, NULL AS REMARKS, "+java.sql.DatabaseMetaData.procedureReturnsResult+" AS PROCEDURE_TYPE "+
>>>>>>> " FROM pg_proc p ";
>>>>>>> if (procedureNamePattern != null) {
>>>>>>>! sql += " WHERE p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
>>>>>>> }
>>>>>>> sql += " ORDER BY PROCEDURE_NAME ";
>>>>>>> }
>>>>>>>--- 1733,1746 ----
>>>>>>> " LEFT JOIN pg_description d ON (p.oid=d.objoid) "+
>>>>>>> " LEFT JOIN pg_class c ON (d.classoid=c.oid AND c.relname='pg_proc') ";
>>>>>>> if (procedureNamePattern != null) {
>>>>>>>! sql += " WHERE p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
>>>>>>> }
>>>>>>> sql += " ORDER BY PROCEDURE_NAME ";
>>>>>>> } else {
>>>>>>> sql = "SELECT NULL AS PROCEDURE_CAT, NULL AS PROCEDURE_SCHEM, p.proname AS PROCEDURE_NAME, NULL,
NULL,NULL, NULL AS REMARKS, "+java.sql.DatabaseMetaData.procedureReturnsResult+" AS PROCEDURE_TYPE "+
>>>>>>> " FROM pg_proc p ";
>>>>>>> if (procedureNamePattern != null) {
>>>>>>>! sql += " WHERE p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
>>>>>>> }
>>>>>>> sql += " ORDER BY PROCEDURE_NAME ";
>>>>>>> }
>>>>>>>***************
>>>>>>>*** 1822,1831 ****
>>>>>>> " 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 && !"".equals(schemaPattern)) {
>>>>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
>>>>>>> }
>>>>>>> if (procedureNamePattern != null) {
>>>>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
>>>>>>> }
>>>>>>> sql += " ORDER BY n.nspname, p.proname ";
>>>>>>> } else {
>>>>>>>--- 1818,1827 ----
>>>>>>> " 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 && !"".equals(schemaPattern)) {
>>>>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
>>>>>>> }
>>>>>>> if (procedureNamePattern != null) {
>>>>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
>>>>>>> }
>>>>>>> sql += " ORDER BY n.nspname, p.proname ";
>>>>>>> } else {
>>>>>>>***************
>>>>>>>*** 1833,1839 ****
>>>>>>> " FROM pg_proc p,pg_type t "+
>>>>>>> " WHERE p.prorettype=t.oid ";
>>>>>>> if (procedureNamePattern != null) {
>>>>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern.toLowerCase())+"' ";
>>>>>>> }
>>>>>>> sql += " ORDER BY p.proname ";
>>>>>>> }
>>>>>>>--- 1829,1835 ----
>>>>>>> " FROM pg_proc p,pg_type t "+
>>>>>>> " WHERE p.prorettype=t.oid ";
>>>>>>> if (procedureNamePattern != null) {
>>>>>>>! sql += " AND p.proname LIKE '"+escapeQuotes(procedureNamePattern)+"' ";
>>>>>>> }
>>>>>>> sql += " ORDER BY p.proname ";
>>>>>>> }
>>>>>>>***************
>>>>>>>*** 1997,2006 ****
>>>>>>> " LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog') "+
>>>>>>> " WHERE c.relnamespace = n.oid ";
>>>>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
>>>>>>>! select += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
>>>>>>> }
>>>>>>> if (tableNamePattern != null) {
>>>>>>>! select += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern.toLowerCase())+"' ";
>>>>>>> }
>>>>>>> orderby = " ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME ";
>>>>>>> } else {
>>>>>>>--- 1993,2002 ----
>>>>>>> " LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog') "+
>>>>>>> " WHERE c.relnamespace = n.oid ";
>>>>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
>>>>>>>! select += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
>>>>>>> }
>>>>>>> if (tableNamePattern != null) {
>>>>>>>! select += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern)+"' ";
>>>>>>> }
>>>>>>> orderby = " ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME ";
>>>>>>> } else {
>>>>>>>***************
>>>>>>>*** 2290,2296 ****
>>>>>>> " LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog')
"+
>>>>>>> " WHERE a.attnum > 0 AND NOT a.attisdropped ";
>>>>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
>>>>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
>>>>>>> }
>>>>>>> } else if (connection.haveMinimumServerVersion("7.1")) {
>>>>>>> sql = "SELECT NULL::text AS
nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description"+
>>>>>>>--- 2286,2292 ----
>>>>>>> " LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog')
"+
>>>>>>> " WHERE a.attnum > 0 AND NOT a.attisdropped ";
>>>>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
>>>>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
>>>>>>> }
>>>>>>> } else if (connection.haveMinimumServerVersion("7.1")) {
>>>>>>> sql = "SELECT NULL::text AS
nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description"+
>>>>>>>***************
>>>>>>>*** 2308,2317 ****
>>>>>>> }
>>>>>>>
>>>>>>> if (tableNamePattern != null && !"".equals(tableNamePattern)) {
>>>>>>>! sql += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern.toLowerCase())+"' ";
>>>>>>> }
>>>>>>> if (columnNamePattern != null && !"".equals(columnNamePattern)) {
>>>>>>>! sql += " AND a.attname LIKE '"+escapeQuotes(columnNamePattern.toLowerCase())+"' ";
>>>>>>> }
>>>>>>> sql += " ORDER BY nspname,relname,attname ";
>>>>>>>
>>>>>>>--- 2304,2313 ----
>>>>>>> }
>>>>>>>
>>>>>>> if (tableNamePattern != null && !"".equals(tableNamePattern)) {
>>>>>>>! sql += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern)+"' ";
>>>>>>> }
>>>>>>> if (columnNamePattern != null && !"".equals(columnNamePattern)) {
>>>>>>>! sql += " AND a.attname LIKE '"+escapeQuotes(columnNamePattern)+"' ";
>>>>>>> }
>>>>>>> sql += " ORDER BY nspname,relname,attname ";
>>>>>>>
>>>>>>>***************
>>>>>>>*** 2410,2417 ****
>>>>>>>
>>>>>>> if (columnNamePattern == null)
>>>>>>> columnNamePattern = "%";
>>>>>>>- else
>>>>>>>- columnNamePattern = columnNamePattern.toLowerCase();
>>>>>>>
>>>>>>> f[0] = new Field(connection, "TABLE_CAT", iVarcharOid, getMaxNameLength());
>>>>>>> f[1] = new Field(connection, "TABLE_SCHEM", iVarcharOid, getMaxNameLength());
>>>>>>>--- 2406,2411 ----
>>>>>>>***************
>>>>>>>*** 2432,2438 ****
>>>>>>> " AND c.relkind = 'r' "+
>>>>>>> " AND a.attnum > 0 AND NOT a.attisdropped ";
>>>>>>> if (schema != null && !"".equals(schema)) {
>>>>>>>! sql += " AND n.nspname = '"+escapeQuotes(schema.toLowerCase())+"' ";
>>>>>>> }
>>>>>>> } else {
>>>>>>> sql = "SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl,a.attname "+
>>>>>>>--- 2426,2432 ----
>>>>>>> " AND c.relkind = 'r' "+
>>>>>>> " AND a.attnum > 0 AND NOT a.attisdropped ";
>>>>>>> if (schema != null && !"".equals(schema)) {
>>>>>>>! sql += " AND n.nspname = '"+escapeQuotes(schema)+"' ";
>>>>>>> }
>>>>>>> } else {
>>>>>>> sql = "SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl,a.attname "+
>>>>>>>***************
>>>>>>>*** 2443,2451 ****
>>>>>>> " AND c.relkind = 'r' ";
>>>>>>> }
>>>>>>>
>>>>>>>! sql += " AND c.relname = '"+escapeQuotes(table.toLowerCase())+"' ";
>>>>>>> if (columnNamePattern != null && !"".equals(columnNamePattern)) {
>>>>>>>! sql += " AND a.attname LIKE '"+escapeQuotes(columnNamePattern.toLowerCase())+"' ";
>>>>>>> }
>>>>>>> sql += " ORDER BY attname ";
>>>>>>>
>>>>>>>--- 2437,2445 ----
>>>>>>> " AND c.relkind = 'r' ";
>>>>>>> }
>>>>>>>
>>>>>>>! sql += " AND c.relname = '"+escapeQuotes(table)+"' ";
>>>>>>> if (columnNamePattern != null && !"".equals(columnNamePattern)) {
>>>>>>>! sql += " AND a.attname LIKE '"+escapeQuotes(columnNamePattern)+"' ";
>>>>>>> }
>>>>>>> sql += " ORDER BY attname ";
>>>>>>>
>>>>>>>***************
>>>>>>>*** 2538,2544 ****
>>>>>>> " AND u.usesysid = c.relowner "+
>>>>>>> " AND c.relkind = 'r' ";
>>>>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
>>>>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern.toLowerCase())+"' ";
>>>>>>> }
>>>>>>> } else {
>>>>>>> sql = "SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl "+
>>>>>>>--- 2532,2538 ----
>>>>>>> " AND u.usesysid = c.relowner "+
>>>>>>> " AND c.relkind = 'r' ";
>>>>>>> if (schemaPattern != null && !"".equals(schemaPattern)) {
>>>>>>>! sql += " AND n.nspname LIKE '"+escapeQuotes(schemaPattern)+"' ";
>>>>>>> }
>>>>>>> } else {
>>>>>>> sql = "SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl "+
>>>>>>>***************
>>>>>>>*** 2548,2554 ****
>>>>>>> }
>>>>>>>
>>>>>>> if (tableNamePattern != null && !"".equals(tableNamePattern)) {
>>>>>>>! sql += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern.toLowerCase())+"' ";
>>>>>>> }
>>>>>>> sql += " ORDER BY nspname, relname ";
>>>>>>>
>>>>>>>--- 2542,2548 ----
>>>>>>> }
>>>>>>>
>>>>>>> if (tableNamePattern != null && !"".equals(tableNamePattern)) {
>>>>>>>! sql += " AND c.relname LIKE '"+escapeQuotes(tableNamePattern)+"' ";
>>>>>>> }
>>>>>>> sql += " ORDER BY nspname, relname ";
>>>>>>>
>>>>>>>***************
>>>>>>>*** 2752,2758 ****
>>>>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_attributea, pg_catalog.pg_index i ";
>>>>>>> where = " AND ct.relnamespace = n.oid ";
>>>>>>> if (schema != null && !"".equals(schema)) {
>>>>>>>! where += " AND n.nspname = '"+escapeQuotes(schema.toLowerCase())+"' ";
>>>>>>> }
>>>>>>> } else {
>>>>>>> from = " FROM pg_class ct, pg_class ci, pg_attribute a, pg_index i ";
>>>>>>>--- 2746,2752 ----
>>>>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_attributea, pg_catalog.pg_index i ";
>>>>>>> where = " AND ct.relnamespace = n.oid ";
>>>>>>> if (schema != null && !"".equals(schema)) {
>>>>>>>! where += " AND n.nspname = '"+escapeQuotes(schema)+"' ";
>>>>>>> }
>>>>>>> } else {
>>>>>>> from = " FROM pg_class ct, pg_class ci, pg_attribute a, pg_index i ";
>>>>>>>***************
>>>>>>>*** 2761,2767 ****
>>>>>>> from+
>>>>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid "+
>>>>>>> " AND a.attrelid=ci.oid AND i.indisprimary "+
>>>>>>>! " AND ct.relname = '"+escapeQuotes(table.toLowerCase())+"' "+
>>>>>>> where+
>>>>>>> " ORDER BY a.attnum ";
>>>>>>>
>>>>>>>--- 2755,2761 ----
>>>>>>> from+
>>>>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid "+
>>>>>>> " AND a.attrelid=ci.oid AND i.indisprimary "+
>>>>>>>! " AND ct.relname = '"+escapeQuotes(table)+"' "+
>>>>>>> where+
>>>>>>> " ORDER BY a.attnum ";
>>>>>>>
>>>>>>>***************
>>>>>>>*** 2882,2888 ****
>>>>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_attributea, pg_catalog.pg_index i ";
>>>>>>> where = " AND ct.relnamespace = n.oid ";
>>>>>>> if (schema != null && !"".equals(schema)) {
>>>>>>>! where += " AND n.nspname = '"+escapeQuotes(schema.toLowerCase())+"' ";
>>>>>>> }
>>>>>>> } else {
>>>>>>> select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, ";
>>>>>>>--- 2876,2882 ----
>>>>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_attributea, pg_catalog.pg_index i ";
>>>>>>> where = " AND ct.relnamespace = n.oid ";
>>>>>>> if (schema != null && !"".equals(schema)) {
>>>>>>>! where += " AND n.nspname = '"+escapeQuotes(schema)+"' ";
>>>>>>> }
>>>>>>> } else {
>>>>>>> select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, ";
>>>>>>>***************
>>>>>>>*** 2896,2902 ****
>>>>>>> from+
>>>>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid "+
>>>>>>> " AND a.attrelid=ci.oid AND i.indisprimary "+
>>>>>>>! " AND ct.relname = '"+escapeQuotes(table.toLowerCase())+"' "+
>>>>>>> where+
>>>>>>> " ORDER BY table_name, pk_name, key_seq";
>>>>>>> return connection.createStatement().executeQuery(sql);
>>>>>>>--- 2890,2896 ----
>>>>>>> from+
>>>>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid "+
>>>>>>> " AND a.attrelid=ci.oid AND i.indisprimary "+
>>>>>>>! " AND ct.relname = '"+escapeQuotes(table)+"' "+
>>>>>>> where+
>>>>>>> " ORDER BY table_name, pk_name, key_seq";
>>>>>>> return connection.createStatement().executeQuery(sql);
>>>>>>>***************
>>>>>>>*** 3008,3017 ****
>>>>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_namespace n2, pg_catalog.pg_trigger t,
pg_catalog.pg_triggert1, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_class ic, pg_catalog.pg_proc p1,
pg_catalog.pg_procp2, pg_catalog.pg_index i, pg_catalog.pg_attribute a ";
>>>>>>> where = " AND c.relnamespace = n.oid AND c2.relnamespace=n2.oid ";
>>>>>>> if (primarySchema != null && !"".equals(primarySchema)) {
>>>>>>>! where += " AND n.nspname = '"+escapeQuotes(primarySchema.toLowerCase())+"' ";
>>>>>>> }
>>>>>>> if (foreignSchema != null && !"".equals(foreignSchema)) {
>>>>>>>! where += " AND n2.nspname = '"+escapeQuotes(foreignSchema.toLowerCase())+"' ";
>>>>>>> }
>>>>>>> } else {
>>>>>>> select = "SELECT DISTINCT NULL::text as pnspname, NULL::text as fnspname, ";
>>>>>>>--- 3002,3011 ----
>>>>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_namespace n2, pg_catalog.pg_trigger t,
pg_catalog.pg_triggert1, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_class ic, pg_catalog.pg_proc p1,
pg_catalog.pg_procp2, pg_catalog.pg_index i, pg_catalog.pg_attribute a ";
>>>>>>> where = " AND c.relnamespace = n.oid AND c2.relnamespace=n2.oid ";
>>>>>>> if (primarySchema != null && !"".equals(primarySchema)) {
>>>>>>>! where += " AND n.nspname = '"+escapeQuotes(primarySchema)+"' ";
>>>>>>> }
>>>>>>> if (foreignSchema != null && !"".equals(foreignSchema)) {
>>>>>>>! where += " AND n2.nspname = '"+escapeQuotes(foreignSchema)+"' ";
>>>>>>> }
>>>>>>> } else {
>>>>>>> select = "SELECT DISTINCT NULL::text as pnspname, NULL::text as fnspname, ";
>>>>>>>***************
>>>>>>>*** 3052,3061 ****
>>>>>>> + where;
>>>>>>>
>>>>>>> if (primaryTable != null) {
>>>>>>>! sql += "AND c.relname='" + escapeQuotes(primaryTable.toLowerCase()) + "' ";
>>>>>>> }
>>>>>>> if (foreignTable != null) {
>>>>>>>! sql += "AND c2.relname='" + escapeQuotes(foreignTable.toLowerCase()) + "' ";
>>>>>>> }
>>>>>>>
>>>>>>> sql += "ORDER BY ";
>>>>>>>--- 3046,3055 ----
>>>>>>> + where;
>>>>>>>
>>>>>>> if (primaryTable != null) {
>>>>>>>! sql += "AND c.relname='" + escapeQuotes(primaryTable) + "' ";
>>>>>>> }
>>>>>>> if (foreignTable != null) {
>>>>>>>! sql += "AND c2.relname='" + escapeQuotes(foreignTable) + "' ";
>>>>>>> }
>>>>>>>
>>>>>>> sql += "ORDER BY ";
>>>>>>>***************
>>>>>>>*** 3548,3554 ****
>>>>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_indexi, pg_catalog.pg_attribute a, pg_catalog.pg_am am ";
>>>>>>> where = " AND n.oid = ct.relnamespace ";
>>>>>>> if (schema != null && ! "".equals(schema)) {
>>>>>>>! where += " AND n.nspname = '"+escapeQuotes(schema.toLowerCase())+"' ";
>>>>>>> }
>>>>>>> } else {
>>>>>>> select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, ";
>>>>>>>--- 3542,3548 ----
>>>>>>> from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci,
pg_catalog.pg_indexi, pg_catalog.pg_attribute a, pg_catalog.pg_am am ";
>>>>>>> where = " AND n.oid = ct.relnamespace ";
>>>>>>> if (schema != null && ! "".equals(schema)) {
>>>>>>>! where += " AND n.nspname = '"+escapeQuotes(schema)+"' ";
>>>>>>> }
>>>>>>> } else {
>>>>>>> select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, ";
>>>>>>>***************
>>>>>>>*** 3573,3579 ****
>>>>>>> from+
>>>>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid AND a.attrelid=ci.oid AND ci.relam=am.oid "+
>>>>>>> where+
>>>>>>>! " AND ct.relname = '"+escapeQuotes(tableName.toLowerCase())+"' ";
>>>>>>>
>>>>>>> if (unique) {
>>>>>>> sql += " AND i.indisunique ";
>>>>>>>--- 3567,3573 ----
>>>>>>> from+
>>>>>>> " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid AND a.attrelid=ci.oid AND ci.relam=am.oid "+
>>>>>>> where+
>>>>>>>! " AND ct.relname = '"+escapeQuotes(tableName)+"' ";
>>>>>>>
>>>>>>> if (unique) {
>>>>>>> sql += " AND i.indisunique ";
>>>>>>>
>>>>>>>
>>>>>>>------------------------------------------------------------------------
>>>>>>>
>>>>>>>
>>>>>>>---------------------------(end of broadcast)---------------------------
>>>>>>>TIP 4: Don't 'kill -9' the postmaster
>>>>>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 3: if posting/reading through Usenet, please send an appropriate
>>subscribe-nomail command to majordomo@postgresql.org so that your
>>message can get through to the mailing list cleanly
>>
>>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>