Re: DROP COLUMN

On Wed, 2002-07-17 at 08:48, Hiroshi Inoue wrote:
> I sent a draft by mistake, sorry.
> 
> Hannu Krosing wrote:
> > 
> > On Wed, 2002-07-17 at 09:11, Hiroshi Inoue wrote:
> > > Bruce Momjian wrote:
> > >
> > > > From my perspective, when client coders like Dave Page and others say
> > > > they would prefer the flag to the negative attno's, I don't have to
> > > > understand.  I just take their word for it.
> > >
> > > do they really love to check attisdropped everywhere ?
> > > Isn't it the opposite of the encapsulation ?
> > > I don't understand why we would do nothing for clients.
> > 
> > AFAIK, there is separate work being done on defining SQL99 compatible
> > system views, that most client apps could and should use.
> > 
> > But those (few) apps that still need intimate knowledge about postrges'
> > internals will always have to query the original system _tables_.
> > 
> > Also, as we have nothing like Oracles ROWNR, I think it will be quite
> > hard to have colnums without gaps in the system views,
> 
> Agreed. However do we have to give up all views which omit
> dropped columns ? Logical numbers aren't always needed.

Of course not. I just proposed it as a solution for getting
ORDINAL_POSITION for ANSI/ISO system view COLUMNS.

The standard view is defined below but we will no doubt have to
implement it differently ;)

CREATE VIEW COLUMNS AS   SELECT DISTINCT     TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,     C.COLUMN_NAME,
ORDINAL_POSITION,    CASE WHEN EXISTS             ( SELECT *               FROM DEFINITION_SCHEMA.SCHEMATA AS S
      WHERE ( TABLE_CATALOG, TABLE_SCHEMA )                   = (S.CATALOG_NAME, S.SCHEMA_NAME )                 AND
                 ( SCHEMA_OWNER IN                       (  PUBLIC , CURRENT_USER )                     OR SCHEMA_OWNER
IN                    ( SELECT ROLE_NAME                       FROM ENABLED_ROLES ) ) )          THEN COLUMN_DEFAULT
     ELSE NULL     END AS COLUMN_DEFAULT,     IS_NULLABLE,     COALESCE (D1.DATA_TYPE, D2.DATA_TYPE)              AS
DATA_TYPE,    COALESCE (D1.CHARACTER_MAXIMUM_LENGTH,               D2.CHARACTER_MAXIMUM_LENGTH)              AS
CHARACTER_MAXIMUM_LENGTH,    COALESCE (D1.CHARACTER_OCTET_LENGTH, D2.CHARACTER_OCTET_LENGTH)              AS
CHARACTER_OCTET_LENGTH,    COALESCE (D1.NUMERIC_PRECISION, D2.NUMERIC_PRECISION)              AS NUMERIC_PRECISION,
COALESCE(D1.NUMERIC_PRECISION_RADIX, D2.NUMERIC_PRECISION_RADIX)              AS NUMERIC_PRECISION_RADIX,     COALESCE
(D1.NUMERIC_SCALE,D2.NUMERIC_SCALE)              AS NUMERIC_SCALE,     COALESCE (D1.DATETIME_PRECISION,
D2.DATETIME_PRECISION)             AS DATETIME_PRECISION,     COALESCE (D1.INTERVAL_TYPE, D2.INTERVAL_TYPE)
AS INTERVAL_TYPE,     COALESCE (D1.INTERVAL_PRECISION, D2.INTERVAL_PRECISION)              AS INTERVAL_PRECISION,
COALESCE(C1.CHARACTER_SET_CATALOG, C2.CHARACTER_SET_CATALOG)              AS CHARACTER_SET_CATALOG,     COALESCE
(C1.CHARACTER_SET_SCHEMA,C2.CHARACTER_SET_SCHEMA)              AS CHARACTER_SET_SCHEMA,     COALESCE
(C1.CHARACTER_SET_NAME,C2.CHARACTER_SET_NAME)              AS CHARACTER_SET_NAME,     COALESCE (D1.COLLATION_CATALOG,
D2.COLLATION_CATALOG)             AS COLLATION_CATALOG,     COALESCE (D1.COLLATION_SCHEMA, D2.COLLATION_SCHEMA)
    AS COLLATION_SCHEMA,     COALESCE (D1.COLLATION_NAME, D2.COLLATION_NAME)              AS COLLATION_NAME,
DOMAIN_CATALOG,DOMAIN_SCHEMA, DOMAIN_NAME,      COALESCE (D1.USER_DEFINED_TYPE_CATALOG,
D2.USER_DEFINED_TYPE_CATALOG)             AS UDT_CATALOG,     COALESCE (D1.USER_DEFINED_TYPE_SCHEMA,
D2.USER_DEFINED_TYPE_SCHEMA)             AS UDT_SCHEMA,     COALESCE (D1.USER_DEFINED_TYPE_NAME,
D2.USER_DEFINED_TYPE_NAME)             AS UDT_NAME,     COALESCE (D1.SCOPE_CATALOG, D2.SCOPE_CATALOG) AS SCOPE_CATALOG,
   COALESCE (D1.SCOPE_SCHEMA, D2.SCOPE_SCHEMA) AS SCOPE_SCHEMA,     COALESCE (D1.SCOPE_NAME, D2.SCOPE_NAME) AS
SCOPE_NAME,    COALESCE (D1.MAXIMUM_CARDINALITY, D2.MAXIMUM_CARDINALITY)              AS MAXIMUM_CARDINALITY,
COALESCE(D1.DTD_IDENTIFIER, D2.DTD_IDENTIFIER) AS DTD_IDENTIFIER,     IS_SELF_REFERENCING   FROM ( (
DEFINITION_SCHEMA.COLUMNSAS C          LEFT JOIN            ( DEFINITION_SCHEMA.DATA_TYPE_DESCRIPTOR AS D1
LEFTJOIN              DEFINITION_SCHEMA.COLLATIONS AS C1              ON ( ( C1.COLLATION_CATALOG, C1.COLLATION_SCHEMA,
                   C1.COLLATION_NAME )                 = ( D1.COLLATION_CATALOG, D1.COLLATION_SCHEMA,
 D1.COLLATION_NAME ) ) )            ON ( ( C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME,                   'TABLE',
C.DTD_IDENTIFIER)               = ( D1.OBJECT_CATALOG, D1.OBJECT_SCHEMA, D1.OBJECT_NAME,
D1.OBJECT_TYPE,D1.DTD_IDENTIFIER ) ) ) )        LEFT JOIN          ( DEFINITION_SCHEMA.DATA_TYPE_DESCRIPTOR AS D2
  LEFT JOIN            DEFINITION_SCHEMA.COLLATIONS AS C2            ON ( ( C2.COLLATION_CATALOG, C2.COLLATION_SCHEMA,
                C2.COLLATION_NAME )               = ( D2.COLLATION_CATALOG, D2.COLLATION_SCHEMA,
D2.COLLATION_NAME) ) )          ON ( ( C.DOMAIN_CATALOG, C.DOMAIN_SCHEMA, C.DOMAIN_NAME,                  'DOMAIN',
C.DTD_IDENTIFIER)             = ( D2.OBJECT_CATALOG, D2.OBJECT_SCHEMA, D2.OBJECT_NAME,                 D2.OBJECT_TYPE,
D2.DTD_IDENTIFIER) )      WHERE ( C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME,              C.COLUMN_NAME ) IN
      ( SELECT                  TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,                  COLUMN_NAME
FROMDEFINITION_SCHEMA.COLUMN_PRIVILEGES                WHERE ( SCHEMA_OWNER IN                        ( 'PUBLIC',
CURRENT_USER)                      OR                        SCHEMA_OWNER IN                        ( SELECT ROLE_NAME
                       FROM ENABLED_ROLES ) ) )        AND            C.TABLE_CATALOG          = ( SELECT CATALOG_NAME
           FROM INFORMATION_SCHEMA_CATALOG_NAME );
 

----------------
Hannu