SYS.ArrayIterate(IN val object[]) RETURNS TABLE (col object)
SYS schema
System schema for public information and actions.
This table supplies information about all the elements (columns, tags, attributes, etc) in the virtual database.
Column name | Type | Description |
---|---|---|
VDBName |
string |
VDB name |
SchemaName |
string |
Schema name |
TableName |
string |
Table name |
Name |
string |
Element name (not qualified) |
Position |
integer |
Position in group (1-based) |
NameInSource |
string |
Name of element in source |
DataType |
string |
Teiid runtime data type name |
Scale |
integer |
Number of digits after the decimal point |
ElementLength |
integer |
Element length (mostly used for strings) |
sLengthFixed |
boolean |
Whether the length is fixed or variable |
SupportsSelect |
boolean |
Element can be used in SELECT |
SupportsUpdates |
boolean |
Values can be inserted or updated in the element |
IsCaseSensitive |
boolean |
Element is case-sensitive |
IsSigned |
boolean |
Element is signed numeric value |
IsCurrency |
boolean |
Element represents monetary value |
IsAutoIncremented |
boolean |
Element is auto-incremented in the source |
NullType |
string |
Nullability: "Nullable", "No Nulls", "Unknown" |
MinRange |
string |
Minimum value |
MaxRange |
string |
Maximum value |
DistinctCount |
integer |
Distinct value count, -1 can indicate unknown |
NullCount |
integer |
Null value count, -1 can indicate unknown |
SearchType |
string |
Searchability: "Searchable", "All Except Like", "Like Only", Unsearchable" |
Format |
string |
Format of string value |
DefaultValue |
string |
Default value |
JavaClass |
string |
Java class that will be returned |
Precision |
integer |
Number of digits in numeric value |
CharOctetLength |
integer |
Measure of return value size |
Radix |
integer |
Radix for numeric values |
GroupUpperName |
string |
Upper-case full group name |
UpperName |
string |
Upper-case element name |
UID |
string |
Element unique ID |
Description |
string |
Description |
TableUID |
string |
Parent Table unique ID |
TypeName |
string |
The type name, which may be a domain name |
TypeCode |
integer |
JDBC SQL type code |
ColumnSize |
string |
If numeric, the precision, if character, the length, and if date/time, then the string length of a literal value. |
This table supplies information on datatypes.
Column name | Type | Description |
---|---|---|
Name |
string |
Teiid type or domain name |
IsStandard |
boolean |
True if the type is basic |
Type |
String |
One of Basic, UserDefined, ResultSet, Domain |
TypeName |
string |
Design-time type name (same as Name) |
JavaClass |
string |
Java class returned for this type |
Scale |
integer |
Max scale of this type |
TypeLength |
integer |
Max length of this type |
NullType |
string |
Nullability: "Nullable", "No Nulls", "Unknown" |
IsSigned |
boolean |
Is signed numeric? |
IsAutoIncremented |
boolean |
Is auto-incremented? |
IsCaseSensitive |
boolean |
Is case-sensitive? |
Precision |
integer |
Max precision of this type |
Radix |
integer |
Radix of this type |
SearchType |
string |
Searchability: "Searchable", "All Except Like", "Like Only", "Unsearchable" |
UID |
string |
Data type unique ID |
RuntimeType |
string |
Teiid runtime data type name |
BaseType |
string |
Base type |
Description |
string |
Description of type |
TypeCode |
integer |
JDBC SQL type code |
Literal_Prefix |
string |
literal prefix |
Literal_Prefix |
string |
literal suffix |
This table supplies information about the columns referenced by a key.
Column name | Type | Description |
---|---|---|
VDBName |
string |
VDB name |
SchemaName |
string |
Schema name |
TableName |
string |
Table name |
Name |
string |
Element name |
KeyName |
string |
Key name |
KeyType |
string |
Key type: "Primary", "Foreign", "Unique", etc |
RefKeyUID |
string |
Referenced key UID |
UID |
string |
Key UID |
Position |
integer |
Position in key |
TableUID |
string |
Parent Table unique ID |
This table supplies information about primary, foreign, and unique keys.
Column name | Type | Description |
---|---|---|
VDBName |
string |
VDB name |
SchemaName |
string |
Schema name |
Table name |
string |
Table name |
Name |
string |
Key name |
Description |
string |
Description |
NameInSource |
string |
Name of key in source system |
Type |
string |
Type of key: "Primary", "Foreign", "Unique", etc |
IsIndexed |
boolean |
True if key is indexed |
RefKeyUID |
string |
Referenced key UID (if foreign key) |
RefTableUID |
string |
Referenced key table UID (if foreign key) |
RefSchemaUID |
string |
Referenced key table schema UID (if foreign key) |
UID |
string |
Key unique ID |
TableUID |
string |
Key Table unique ID |
SchemaUID |
string |
Key Table Schema unique ID |
ColPositions |
short[] |
Array of column positions within the key table |
This supplies information on procedure parameters.
Column name | Type | Description |
---|---|---|
VDBName |
string |
VDB name |
SchemaName |
string |
Schema name |
ProcedureName |
string |
Procedure name |
Name |
string |
Parameter name |
DataType |
string |
Teiid runtime data type name |
Position |
integer |
Position in procedure args |
Type |
string |
Parameter direction: "In", "Out", "InOut", "ResultSet", "ReturnValue" |
Optional |
boolean |
Parameter is optional |
Precision |
integer |
Precision of parameter |
TypeLength |
integer |
Length of parameter value |
Scale |
integer |
Scale of parameter |
Radix |
integer |
Radix of parameter |
NullType |
string |
Nullability: "Nullable", "No Nulls", "Unknown" |
Description |
string |
Description of parameter |
TypeName |
string |
The type name, which may be a domain name |
TypeCode |
integer |
JDBC SQL type code |
ColumnSize |
string |
If numeric, the precision, if character, the length, and if date/time, then the string length of a literal value. |
DefaultValue |
string |
Default value |
This table supplies information about the procedures in the virtual database.
Column name | Type | Description |
---|---|---|
VDBName |
string |
VDB name |
SchemaName |
string |
Schema name |
Name |
string |
Procedure name |
NameInSource |
string |
Procedure name in source system |
ReturnsResults |
boolean |
Returns a result set |
UID |
string |
Procedure UID |
Description |
string |
Description |
SchemaUID |
string |
Parent Schema unique ID |
This supplies information on function parameters.
Column name | Type | Description |
---|---|---|
VDBName |
string |
VDB name |
SchemaName |
string |
Schema name |
FunctionName |
string |
Function name |
FunctionUID |
string |
Function UID |
Name |
string |
Parameter name |
DataType |
string |
Teiid runtime data type name |
Position |
integer |
Position in procedure args |
Type |
string |
Parameter direction: "In", "Out", "InOut", "ResultSet", "ReturnValue" |
Precision |
integer |
Precision of parameter |
TypeLength |
integer |
Length of parameter value |
Scale |
integer |
Scale of parameter |
Radix |
integer |
Radix of parameter |
NullType |
string |
Nullability: "Nullable", "No Nulls", "Unknown" |
Description |
string |
Description of parameter |
TypeName |
string |
The type name, which may be a domain name |
TypeCode |
integer |
JDBC SQL type code |
ColumnSize |
string |
If numeric, the precision, if character, the length, and if date/time, then the string length of a literal value. |
This table supplies information about the functions in the virtual database.
Column name | Type | Description |
---|---|---|
VDBName |
string |
VDB name |
SchemaName |
string |
Schema name |
Name |
string |
Function name |
NameInSource |
string |
Function name in source system |
UID |
string |
Function UID |
Description |
string |
Description |
IsVarArgs |
boolean |
Does the function accept variable arguments |
This table supplies user-defined properties on all objects based on metamodel extensions. Normally, this table is empty if no metamodel extensions are being used.
Column name | Type | Description |
---|---|---|
Name |
string |
Extension property name |
Value |
string |
Extension property value |
UID |
string |
Key unique ID |
ClobValue |
clob |
Clob Value |
This table supplies informaton about column’s key reference.
Column name | Type | Description |
---|---|---|
PKTABLE_CAT |
string |
VDB name |
PKTABLE_SCHEM |
string |
Schema name |
PKTABLE_NAME |
string |
Table/View name |
PKCOLUMN_NAME |
string |
Column name |
FKTABLE_CAT |
string |
VDB name |
FKTABLE_SCHEM |
string |
Schema name |
FKTABLE_NAME |
string |
Table/View name |
FKCOLUMN_NAME |
string |
Column name |
KEY_SEQ |
short |
Key Sequence |
UPDATE_RULE |
integer |
Update Rule |
DELETE_RULE |
integer |
Delete Rule |
FK_NAME |
string |
FK name |
PK_NAME |
string |
PK Nmae |
DEFERRABILITY |
integer |
This table supplies information about all the schemas in the virtual database, including the system schema itself (System).
Column name | Type | Description |
---|---|---|
VDBName |
string |
VDB name |
Name |
string |
Schema name |
IsPhysical |
boolean |
True if this represents a source |
UID |
string |
Unique ID |
Description |
string |
Description |
PrimaryMetamodelURI |
string |
URI for the primary metamodel describing the model used for this schema |
This table supplies information about all the groups (tables, views, documents, and so forth) in the virtual database.
Column name | Type | Description |
---|---|---|
VDBName |
string |
VDB name |
SchemaName |
string |
Schema Name |
Name |
string |
Short group name |
Type |
string |
Table type (Table, View, Document, …) |
NameInSource |
string |
Name of this group in the source |
IsPhysical |
boolean |
True if this is a source table |
SupportsUpdates |
boolean |
True if group can be updated |
UID |
string |
Group unique ID |
Cardinality |
integer |
Approximate number of rows in the group |
Description |
string |
Description |
IsSystem |
boolean |
True if in system table |
SchemaUID |
string |
Parent Schema unique ID |
This table supplies information about the currently connected virtual database, of which there is always exactly one (in the context of a connection).
Column name | Type | Description |
---|---|---|
Name |
string |
The name of the VDB |
Version |
string |
The version of the VDB |
Description |
string |
The description of the VDB |
LoadingTimestamp |
timestamp |
The timestamp loading began |
ActiveTimestamp |
timestamp |
The timestamp when the vdb became active. |
See also the PostGIS Documentation
Column name | Type | Description |
---|---|---|
srid |
integer |
Spatial Reference Identifier |
auth_name |
string |
Name of the standard or standards body |
auth_srid |
integer |
SRID for the auth_name authority |
srtext |
string |
Well-Known Text representation |
proj4text |
string |
For use with the Proj4 library |
See also the PostGIS Documentation
Column name | Type | Description |
---|---|---|
F_TABLE_CATALOG |
string |
catalog name |
F_TABLE_SCHEMA |
string |
schema name |
F_TABLE_NAME |
string |
table name |
F_GEOMETRY_COLUMN |
string |
column name |
COORD_DIMENSION |
integer |
Number of coordinate dimensions |
SRID |
integer |
Spatial Reference Identifier |
TYPE |
string |
Geometry type name |
Note: The coord_dimension
and srid properties
are determined from the {http://www.teiid.org/translator/spatial/2015}coord_dimension
and {http://www.teiid.org/translator/spatial/2015}srid
extension properties on the column.
When possible, these values are set automatically by the relevant importer.
If the values are not set, they will be reported as 2
and 0
, respectively.
If client logic expects actual values, such as integration with GeoServer, you can set these values manually.
Returns a resultset with a single column with a row for each value in the array.
select array_get(cast(x.col as string[]), 2) from (exec arrayiterate((('a', 'b'),('c','d')))) x
This will produce two rows - 'b', and 'd'.