VIRTUAL and PHYSICAL model types are somewhat loose terms. For instance Teiid currently allows CREATE FOREIGN TABLE and CREATE VIRTUAL PROCEDURE to exist within physical models.

CREATE FOREIGN PROCEDURE needs to be in a PHYSICAL model.

CREATE VIRTUAL FUNCTION needs to be to defined by a java function, but there's a JIRA to allow it to be defined by a Teiid expression.

CREATE FOREIGN PROCEDURE

• Definition: a stored procedure in source
  
• PHYSICAL model type only
  
• Standard Properties
  • UPDATECOUNT
  • NAMEINSOURCE
  • ANNOTATION
  • Parameter directions:  IN (VARIADIC) | OUT (RESULT/RETURN) | INOUT
  • only 1 parameter can be the RETURN direction
  • only 1 parameter can be VARIADIC

• Custom Properties
  • Standard source procedure
  • A result set is optional
  • native-query is optional
    • non-prepared (Applies to JDBC procedures using the native-query option.)

• Direct Query Procedure
  • A variadic object in and a result set is required with ONLY 1 column with datatype = object[]

• AS clause is not supported

• Examples:
• CREATE FOREIGN PROCEDURE func (x integer, y integer) returns table (z integer);
• CREATE FOREIGN PROCEDURE func (x integer, y integer) returns integer;

CREATE FOREIGN FUNCTION (note translators have a term pushdown function that is a must pushdown function they report to the engine)

• Definition: A function that is supported by the source, where Teiid may pushdown to source instead of evaluating in Teiid engine
• PHYSICAL model type only
• Standard Properties
  • UPDATECOUNT
  • NAMEINSOURCE
  • ANNOTATION
  • Parameter direction can only be IN or RETURN
  • only 1 parameter can be the RETURN direction
• Custom Properties
  • native-query is optional
  • DETERMINISM
    • NONDETERMINISTIC
    • COMMAND_DETERMINISTIC
    • SESSION_DETERMINISTIC
    • USER_DETERMINISTIC
    • VDB_DETERMINISTIC
    • DETERMINISTIC
  • NULL-ON-NULL (boolean)
  • VARARGS (boolean)
    • Indicates that the last argument of the function can be repeated 0 to any number of times. default false. It is more proper to use a VARIADIC parameter.
  • AGGREGATE (boolean)
    • Indicates the function is a user defined aggregate function. Properties specific to aggregates are listed below.
    • if (AGGREGATE == TRUE) then the following properties come into play
      • ANALYTIC (boolean)    indicates the aggregate function must be windowed. default false.
      • ALLOWS-ORDERBY (boolean)     indicates the aggregate function supports an ORDER BY clause. default false
      • ALLOWS-DISTINCT (boolean) indicates the aggregate function supports the DISTINCT keyword. default false
      • DECOMPOSABLE (boolean)      indicates the single argument aggregate function can be decomposed as agg(agg(x) ) over subsets of data. default false
      • USES-DISTINCT-ROWS (boolean)  indicates the aggregate function effectively uses distinct rows rather than all rows. default false
  • AS clause is not supported
• Virtual Procedure ("CREATE VIRTUAL PROCEDURE") - Similar to stored procedure, however this is defined using the Teiid's Procedure language and evaluated in the Teiid's engine
• Examples:
  • CREATE FOREIGN FUNCTION func (x integer, y integer) returns boolean OPTIONS ("teiid_rel:native-query"'$1 << $2');

CREATE VIRTUAL FUNCTION (aka User Defined Function)

• Definition: A user defined function, where user can define the calling semantics shown below, however the function implementation is defined using a JAVA Class
• VIRTUAL model type only

• Standard Properties
  • UPDATECOUNT
  • NAMEINSOURCE << Does this mean anything - no, it's a shared property for all metadata objects
  • ANNOTATION

• Custom Properties
  • CATEGORY (required)
  • DETERMINISM
    • NONDETERMINISTIC
    • COMMAND_DETERMINISTIC
    • SESSION_DETERMINISTIC
    • USER_DETERMINISTIC
    • VDB_DETERMINISTIC
    • DETERMINISTIC
  • NULL-ON-NULL (boolean)
  • JAVA_CLASS (string) (required)
  • JAVA_METHOD (string) (required)
  • UDF_JAR_PATH (Not a Teiid property, but reference is required in order to auto-deploy the jar and to add it to a DDL VDB archive).
    • Will not help when creating a dynamic VDB though. Maybe need a tooling property that ends up in a dynamic vdb.xml, but ignored by Teiid - Teiid deals with modules
  • VARARGS (boolean)
    • Indicates that the last argument of the function can be repeated 0 to any number of times. default false. It is more proper to use a VARIADIC parameter.
  • AGGREGATE (boolean)
    • Indicates the function is a user defined aggregate function. Properties specific to aggregates are listed below.
    • if (AGGREGATE == TRUE) then the following properties come into play
      • ANALYTIC (boolean)    indicates the aggregate function must be windowed. default false.
      • ALLOWS-ORDERBY (boolean)     indicates the aggregate function supports an ORDER BY clause. default false
      • ALLOWS-DISTINCT (boolean) indicates the aggregate function supports the DISTINCT keyword. default false
      • DECOMPOSABLE (boolean)      indicates the single argument aggregate function can be decomposed as agg(agg(x) ) over subsets of data. default false
      • USES-DISTINCT-ROWS (boolean)  indicates the aggregate function effectively uses distinct rows rather than all rows. default false
  • AS clause is not supported
  • Examples:

    • CREATE VIRTUAL FUNCTION celsiusToFahrenheit(celsius decimal) RETURNS decimal OPTIONS (JAVA_CLASS 'org.something.TempConv',  JAVA_METHOD 'celsiusToFahrenheit');


    • CREATE VIRTUAL FUNCTION sumAll(arg integer) RETURNS integer OPTIONS (JAVA_CLASS 'org.something.SumAll',  JAVA_METHOD 'addInput', AGGREGATE 'true', VARARGS 'true', "NULL-ON-NULL" 'true');

CREATE VIRTUAL PROCEDURE

• Definition: Similar to stored procedure, however this is defined using the Teiid's Procedure language and evaluated in the Teiid's engine.
• Standard Properties
  • UPDATECOUNT
  • NAMEINSOURCE << Does this mean anything - no, it's a shared property for all metadata objects
  • ANNOTATION
  • non-prepared (Applies to JDBC procedures using the native-query option.)  - not meaningful
  • a result set optional
  • requires SQL definition

• EXAMPLE:

CREATE VIRTUAL PROCEDURE getTweets(query varchar) RETURNS (created_on varchar(25), from_user varchar(25), to_user varchar(25),

                 profile_image_url varchar(25), source varchar(25), text varchar(140)) AS

                select tweet.* from

                    (call twitter.invokeHTTP(action => 'GET', endpoint =>querystring('',query as "q"))) w,

                    XMLTABLE('results' passing JSONTOXML('myxml', w.result) columns

                    created_on string PATH 'created_at',

                    from_user string PATH 'from_user',

                    to_user string PATH 'to_user',

                    profile_image_url string PATH 'profile_image_url',

                    source string PATH 'source',

                    text string PATH 'text') tweet