Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

31.16. element_types

The view element_types contains the data type descriptors of the elements of arrays. When a table column, 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.array_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 31-14. 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), DOMAIN (the array is used by that domain), ROUTINE (the array is used by a parameter or the return data type of that function).
array_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 Applies to a feature not available in PostgreSQL
collation_schema sql_identifier Applies to a feature not available in PostgreSQL
collation_name sql_identifier Applies to a feature not available in PostgreSQL
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 character_data 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.

 
 
  Published courtesy of The PostgreSQL Global Development Group Design by Interspire