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

This is a very good idea, however it will be a tough sell to the core
group to add views for an interface. Keep in mind that if the jdbc
interface gets a few views, then all the other interfaces will want them
as well. There is a new table called pg_depend which will help in
identifying relationships between tables.

Dave
On Thu, 2002-09-12 at 18:40, snpe wrote:
> 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
>
>