User Defined Functions Overview¶
OpenEdge's built-in functions can be referenced in 4GL code, including within the WHERE clause of a data access language statement (e.g., FOR EACH, OPEN QUERY, FIND, etc.). These functions are executed within the Progress client process, whether they occur in data access statements or within other business logic.
FWD includes compatible implementations of these built-in functions. These can be executed in two places:
- within the FWD application server process, for functions which occur outside data access statements;
- within the database server process, for functions which occur within the WHERE clauses of data access statements.
The second case exists because FWD attempts to push as much data access processing to the database server as possible, for efficiency. It is inefficient to fetch a set of records from the database to the application server and filter them using built-in functions at the application server. This nearly always will fetch many more records than are actually necessary to satisfy a query. Instead, FWD lets the database perform the filtering, using the FWD implementation of the built-in functions.
FWD converts the WHERE clause of a 4GL data access statement to a functionally equivalent snippet of FWD Query Language (FQL), possibly with placeholders for query substitution parameters. These are used at runtime to compose a SQL query which is sent to the backing database. When the FWD conversion encounters a 4GL built-in function in a WHERE clause, it determines whether the sub-expression containing that function can be factored out into a query substitution parameter, or whether the function must execute at the database server. The latter case occurs when the function references a table which is part of the query. In such a case, FWD converts that function into an inline, FQL reference to the FWD Java implementation of the equivalent built-in function.
For example, the 4GL WHERE clause:
DECIMAL(my-table.num-text) = 1.234
would convert to the following FQL snippet:
toDec(myTable.numText) = 1.234
At runtime, a full FQL statement is generated using the converted FQL WHERE clause snippet. That FQL statement is transformed to an equivalent, database dialect-specific SQL statement, and the latter is executed against the active database back-end using JDBC. Thus, FQL is portable across different database implementations, so the application does not need to be re-converted if the choice of back-end database changes.
The above FQL snippet would correspond with the following SQL WHERE clause expression:
toDec(my_table.num_text) = 1.234
By default, the database will not have a native toDec function, so in order for such queries to work, any built-in function that can be referenced from a FQL query has to be defined in the database as a user-defined function (UDF) when the database is initialized.
To meet this requirement, FWD provides several, compatible implementations of the 4GL built-in functions as database UDFs. As of FWD v4, the following implementations are provided:
- a "database native" implementation, wherein the UDFs are defined using simple SQL (if possible) or more complex procedure language logic (if necessary); and
- a Java implementation, which represents a subset of the FWD engine's runtime classes, adapted to run within the database server.
The Java implementation requires there to be support in the database for running Java code inside the database server. Some database vendors provide this support by default; for others, additional work must be done to add this support using third-party technologies (e.g., PL/Java for PostgreSQL).
The mechanism to define such UDFs varies by database vendor. They are available for FWD's supported database platforms as follows:
| Database | Native UDF Implementation | Java UDF Implementation |
|---|---|---|
| PostgreSQL (Linux) | FWD v4+ | FWD v3+ |
| PostgreSQL (Windows) | FWD v4+ | FWD v3+ |
| SQL Server (Windows) | Coming soon | FWD v3+ |
| H2 (Java) (not for production use) | N/A | FWD v3+ |
The implementations are intended to be mutually exclusive; only one implementation should be installed in a particular database instance at a time. Using the native UDF implementation is recommended, if it is available for the target database. While there arguably is some advantage to having a single, consistent Java UDF implementation across all databases, which also shares its implementation with the FWD runtime, the database-native UDF implementation is preferred for the following reasons:
- The native implementations are designed to be installed using only SQL (e.g., CREATE FUNCTION); no JVM or other binaries need be installed on the database server. This tends to make the deployment of the native UDF implementations both simpler and less controversial (some cloud providers may have security concerns which will disallow running the Java UDF implementation in a managed, cloud environment).
- While the goal of the native implementations is to be at least as performant as the Java implementation, performance equivalence cannot be guaranteed (in either direction). However, the native implementation has the advantage that at least those UDFs which are implemented directly in SQL (as opposed to a more complex procedure language) are transparent to a database's query planner, while all the Java UDFs are opaque to a database's query planner. This may allow a database to create more efficient query plans using the native UDFs vs. the Java UDFs.
- The Java implementation will be made obsolete in future FWD releases (in all but the H2 database), as native UDF implementations are made available for every supported platform.