SELECT score(1), ID, FREEDATA FROM Docs WHERE contains(freedata, 'nick', 1) > 0Source Supported Functions
While Teiid Spring Boot provides an extensive scalar function library, it contains only those functions that can be evaluated within the query engine. In many circumstances, especially for performance, a source function allows for calling a source specific function. The semantics of defining the source function as similar or same to one of defining the UDF.
For example, suppose you want to use the Oracle-specific functions score and contains like:
The score and contains functions are not part of built-in scalar function library. While you could write your own custom scalar function to mimic their behavior, it’s more likely that you would want to use the actual Oracle functions that are provided by Oracle when using the Oracle Free Text functionality.
In order to configure Teiid Spring Boot to push the above function evaluation to Oracle, Teiid Spring Boot provides a few different ways one can configure their instance.
If you source handing of the function can be described by simple parameter substitution into a string, then you may not need to extend the translator for a source specific function. You can use the extension property teiid_rel:native-query to define the syntax handling - see also DDL Metadata for functions.
See defining the metadata using DDL, you can define your source function in the VDB as
CREATE DATABASE "{vdb-name}";
USE DATABASE "{vdb-name}";
CREATE SERVER AccountsDB FOREIGN DATA WRAPPER oracle OPTIONS ("resource-name" 'java:/oracleDS');
CREATE SCHEMA "{model-name}" SERVER AccountsDB;
SET SCHEMA "{model-name}";
CREATE FOREIGN FUNCTION SCORE (val integer) RETURNS integer;By default when a source can provide metadata, the Source model’s metadata is automatically retrieved from the source if they were JDBC, File, WebService. The File and WebService sources are static, so one can not add additional metadata on them. However on the JDBC sources you can retrieve the metadata from source and then user can append additional metadata on top of them. For example
CREATE DATABASE "{vdb-name}";
USE DATABASE "{vdb-name}";
CREATE SERVER AccountsDB FOREIGN DATA WRAPPER oracle OPTIONS ("resource-name" 'java:/oracleDS');
CREATE SCHEMA "{model-name}" SERVER AccountsDB;
SET SCHEMA "{model-name}";
IMPORT FROM AccountsDB INTO "{model-name}";
CREATE FOREIGN FUNCTION SCORE (val integer) RETURNS integer;
The above example uses NATIVE metadata type (NATIVE is the default for source/physical models) first to retrieve schema information from source, then uses DDL metadata type to add additional metadata. Only metadata not available via the NATIVE translator logic would need to be specified via DDL.