Re: JDBC idea with special JDBC views

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
>
>