OBJECTTABLE([LANGUAGE lang] rowScript [PASSING val AS name ...] COLUMNS colName colType colScript [DEFAULT defaultExpr] ...) AS idOBJECTTABLE
The OBJECTTABLE function processes an object input to produce tabular output. The function itself defines what columns it projects. The OBJECTTABLE function is implicitly a nested table and can be used within FROM clauses.
Usage
Parameters
- lang
-
An optional string literal that is the case sensitive language name of the scripts to be processed. The script engine must be available via a JSR-223 ScriptEngineManager lookup.
In some instances this may mean making additional modules available to your vdb, which can be done via the same process as adding modules/libraries for UDFs.
If a LANGUAGE is not specified, the default 'teiid_script' is used.
name:: An identifier that binds the val expression value into the script context.
rowScript:: A string literal that specifies the script to create the row values.
For each non-null item that the Iterator produces, the columns are evaluated.
colName/colType:: ID/data type of the column, which can optionally be defaulted with the DEFAULT clause expression defaultExpr.
colScript:: A string literal that specifies the script that evaluates to the column value.
Syntax rules
-
Columns names cannot contain duplicates.
-
Teiid places several special variables in the script execution context. The CommandContext is available as
teiid_context. Additionally thecolScriptsmay accessteiid_rowandteiid_row_number.teiid_rowis the current row object produced by the row script.teiid_row_numberis the current 1-based row number. -
rowScriptis evaluated to an Iterator. If the results is already an Iterator, it is used directly. If the evaluation result is an Iteratable, Array, or Array type, then an Iterator is obtained. Any other Object will be treated as an Iterator of a single item. In all cases null row values are skipped.
|
Note
|
Although there are no restrictions on naming PASSING variables, it is best to choose names that you can reference as identifiers in the target language. |
OBJECTTABLE examples
-
Accessing special variables:
SELECT x.* FROM OBJECTTABLE('teiid_context' COLUMNS "user" string 'teiid_row.userName', row_number integer 'teiid_row_number') AS xThe result would be a row with two columns containing the user name and 1 respectively.
|
Note
|
Languages other than teiid_script generally permit unrestricted access to Java functionality.
As a result, by default, their use is restricted.
You can override the restrictions by declaring allowable languages by name in the
|
teiid_script
teiid_script is a simple scripting expression language that allows access to passing and special variables,
and to non-void 0-argument methods on objects and indexed values on arrays/lists.
A teiid_script expression begins by referencing the passing or special variable.
Then, any number of . accessors can be chained to evaluate the expression to a different value.
Methods may be accessed by their property names, for example, foo rather than getFoo.
If the object includes both a getFoo() and foo() method, then the accessor foo references foo (),
and getFoo should be used to call the getter.
An array or list index is accessed using a 1-based positive integral value, using the same . accessor syntax.
The same logic as the system function array_get is used.
That is, if the index is out of bounds, null is returned, rather than an exception.
teiid_script is effectively dynamically typed as typing is performed at runtime. If an accessor does not exist on the object, or if the method is not accessible, then an exception is raised. If any point in the accessor chain evaluates to a null value, then null will be returned.
teiid_script examples
-
To get the VDB description string:
teiid_context.session.vdb.description-
To get the first character of the VDB description string:
teiid_context.session.vdb.description.toCharArray.1