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.COLUMNS AS C
LEFT JOIN
( DEFINITION_SCHEMA.DATA_TYPE_DESCRIPTOR AS D1
LEFT JOIN
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
FROM DEFINITION_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