37.23. element_types
The view
element_types
contains the data type
descriptors of the elements of arrays. When a table column, composite-type attribute,
domain, function parameter, or function return value is defined to
be of an array type, the respective information schema view only
contains
ARRAY
in the column
data_type
. To obtain information on the element
type of the array, you can join the respective view with this view.
For example, to show the columns of a table with data types and
array element types, if applicable, you could do:
SELECT c.column_name, c.data_type, e.data_type AS element_type FROM information_schema.columns c LEFT JOIN information_schema.element_types e ON ((c.table_catalog, c.table_schema, c.table_name, 'TABLE', c.dtd_identifier) = (e.object_catalog, e.object_schema, e.object_name, e.object_type, e.collection_type_identifier)) WHERE c.table_schema = '...' AND c.table_name = '...' ORDER BY c.ordinal_position;
This view only includes objects that the current user has access to, by way of being the owner or having some privilege.
Table 37.21.
element_types
Columns
Name | Data Type | Description |
---|---|---|
object_catalog
|
sql_identifier
|
Name of the database that contains the object that uses the array being described (always the current database) |
object_schema
|
sql_identifier
|
Name of the schema that contains the object that uses the array being described |
object_name
|
sql_identifier
|
Name of the object that uses the array being described |
object_type
|
character_data
|
The type of the object that uses the array being described: one
of
TABLE
(the array is used by a column of
that table),
USER-DEFINED TYPE
(the array is
used by an attribute of that composite type),
DOMAIN
(the array is used by that domain),
ROUTINE
(the array is used by a parameter or
the return data type of that function).
|
collection_type_identifier
|
sql_identifier
|
The identifier of the data type descriptor of the array being
described. Use this to join with the
dtd_identifier
columns of other information
schema views.
|
data_type
|
character_data
|
Data type of the array elements, if it is a built-in type, else
USER-DEFINED
(in that case, the type is
identified in
udt_name
and associated
columns).
|
character_maximum_length
|
cardinal_number
|
Always null, since this information is not applied to array element data types in PostgreSQL |
character_octet_length
|
cardinal_number
|
Always null, since this information is not applied to array element data types in PostgreSQL |
character_set_catalog
|
sql_identifier
|
Applies to a feature not available in PostgreSQL |
character_set_schema
|
sql_identifier
|
Applies to a feature not available in PostgreSQL |
character_set_name
|
sql_identifier
|
Applies to a feature not available in PostgreSQL |
collation_catalog
|
sql_identifier
|
Name of the database containing the collation of the element type (always the current database), null if default or the data type of the element is not collatable |
collation_schema
|
sql_identifier
|
Name of the schema containing the collation of the element type, null if default or the data type of the element is not collatable |
collation_name
|
sql_identifier
|
Name of the collation of the element type, null if default or the data type of the element is not collatable |
numeric_precision
|
cardinal_number
|
Always null, since this information is not applied to array element data types in PostgreSQL |
numeric_precision_radix
|
cardinal_number
|
Always null, since this information is not applied to array element data types in PostgreSQL |
numeric_scale
|
cardinal_number
|
Always null, since this information is not applied to array element data types in PostgreSQL |
datetime_precision
|
cardinal_number
|
Always null, since this information is not applied to array element data types in PostgreSQL |
interval_type
|
character_data
|
Always null, since this information is not applied to array element data types in PostgreSQL |
interval_precision
|
cardinal_number
|
Always null, since this information is not applied to array element data types in PostgreSQL |
domain_default
|
character_data
|
Not yet implemented |
udt_catalog
|
sql_identifier
|
Name of the database that the data type of the elements is defined in (always the current database) |
udt_schema
|
sql_identifier
|
Name of the schema that the data type of the elements is defined in |
udt_name
|
sql_identifier
|
Name of the data type of the elements |
scope_catalog
|
sql_identifier
|
Applies to a feature not available in PostgreSQL |
scope_schema
|
sql_identifier
|
Applies to a feature not available in PostgreSQL |
scope_name
|
sql_identifier
|
Applies to a feature not available in PostgreSQL |
maximum_cardinality
|
cardinal_number
|
Always null, because arrays always have unlimited maximum cardinality in PostgreSQL |
dtd_identifier
|
sql_identifier
|
An identifier of the data type descriptor of the element. This is currently not useful. |