Представление COLUMNS
Представление идентифицирует столбцы таблиц, определенных в данном каталоге и доступных данному пользователю. Представление определяется следующим образом:
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 = USER) THEN COLUMN_DEFAULT ELSE NULL END AS COLUMN_DEFAULT, IS_NULLABLE, COALESCE (D1.DATA_TYPE, D2.DATA_TYPE) AS DATA_TYPE, LESCE (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 (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 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, C.COLUMN_NAME) = (D1.TABLE_OR_DOMAIN_CATALOG, D1.TABLE_OR_DOMAIN_SCHEMA, D1.TABLE_OR_DOMAIN_NAME, D1.COLUMN_NAME)) 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) = (D2.TABLE_OR_DOMAIN_CATALOG, D2.TABLE_OR_DOMAIN_SCHEMA, D2.TABLE_OR_DOMAIN_NAME)) 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 GRANTEE IN ('PUBLIC', CURRENT_USER)) AND C.TABLE_CATALOG = (SELECT CATALOG_NAME FROM INFORMATION_SCHEMA_CATALOG_NAME)
