JDBC idea with special JDBC views - Mailing list pgsql-jdbc

Hello,
  I have idea for few methods in JDBC
We can  add views pg_gettables, pg_getcolumns, pg_getprimarykeys
etc. and method work only connection.executeQuery("select * from
pg_gettables") (and like).Columns in view are like JDBC specification.
Now JDBC source stay unchanged and we can speedup query for view and change
with change JDBC specification
In begining schema can be null, but later we add schema support with only
change views (without change JDBC source)
We need COLUMN_SIZE and PRECISION for any view.We can make function like
format_type in postgresql source or simple view like (for getColumns) :
create view pg_getcolumns as (this is not complete view for getColumns, only
example - can be much better)
select null::varchar as "TABLE CAT",
        null::varchar as "TABLE_SCHEM",
        c.relname as "TABLE_NAME",a.attname as "COLUMN_NAME",
        CASE t.typname
                when 'int2' then 'Types.SMALLINT'
                when 'int4' then 'Types.INTEGER'
                when 'oid' then 'Types.INTEGER'
                when 'numeric' then 'Types.NUMERIC'
                else
                        'Types.OTHER'
        end as "DATA_TYPE",
        typname as "TYPE_NAME",
        case typname
                when 'varchar' then
                        case atttypmod
                                when -1 then null
                        else
                                 (atttypmod-4)
                        end
                when 'bpchar' then
                        case atttypmod
                                when -1 then null
                        else
                                 (atttypmod-4)
                        end
        else
                attlen
        end as "COLUMN_SIZE",
        null::int4 as "BUFFER_LENGTH",
        case typname
                when 'numeric' then
                        case atttypmod
                                when -1 then null
                        else
                                (atttypmod-4) >> 16
                        end
                when 'decimal' then
                        case atttypmod
                                when -1 then null
                        else
                                 (atttypmod-4) >>16
                        end
        end as "DECIMAL_DIGITS",
        case typname
                when 'numeric' then
                        case atttypmod
                                when -1 then null
                        else
                                (int4(atttypmod)-int4('4')) & int4('65535')
                        end
                when 'decimal' then
                        case atttypmod
                                when -1 then null
                        else
                                (int4(atttypmod)-int4('4')) & int4('65535')
                        end
        end as "NUM_PREC_RADIX"
from pg_class c,pg_catalog.pg_type t,pg_catalog.pg_attribute a
where c.oid=a.attrelid and t.oid=a.atttypid;

getColumns is like :

    connection.CreateStatement().executeQuery("SELECT * " +
        "FROM pg_getcolumns " +
        "WHERE \"TABLE_CAT\" LIKE " + catalog==null ? "%" : catalog +
etc

   Maybe is better with PreparedStatement in this method

regards
Haris Peco