Stored functions support

Hybrid Data Pipeline supports DB2, Oracle, PostgreSQL, and SQL Server stored functions for OData Version 4 services as described here.

• Functions that are unbound (static operations)
• Function imports
• Functions that return primitive types
• Function invocation with OData system query options $filter

Note that the following aspects of OData Version 4 functions are NOT supported.

• Functions that return complex types and entities
• Functions that are bound to entities
• Built-in functions
• Functions with OUT/INOUT parameters
• Overloaded functions
• Function invocation as part of $select
• Function invocation as part of $orderby
• Function invocation as part of parameter value
• Parameter aliases are not supported. Hence, invoking functions with function parameters as URL query parameters is not supported.
• The following additional limitations apply to PostgreSQL.
  • The BYTEA data type is not supported.
  • The BIT data type is mapped as BINARY. To work around this issue, you can create a function parameter or return type as BIT.
  • Synonyms are not supported. To work around this issue, the functions of other schema can be accessed with the following steps.
    1. Create a user (for example, USER_A) and functions in one schema (for example, SCHEMA_A) .
    2. Create another user (for example, USER_B) and set a search path for this user to access functions for SCHEMA_A:

       alter user USER_B set search_path to SCHEMA_A;

    Now USER_B can access the functions created for SCHEMA_A without using the fully qualified name (schemaName.functionName), while USER_A remains the owner of those functions.