Extend Your SQL with User-Defined Functions in OpenEdge 11.7

With user-defined functions, OpenEdge database developers can extend their programming to validate business logic, use less bandwidth and more.

OpenEdge SQL 11.7 allows you as database developers to create your own routines. These routines, called User-Defined Functions (UDF), can accept parameters, perform custom actions and return results. These are required especially when it comes to business logic, complex algorithmic calculations and custom actions. They also help reduce network traffic.

UDFs help the user extend the SQL functionality. You can write UDFs in Java to provide functionality that is not available in SQL or SQL built-in functions by default.

UDFs can be added in an SQL statement to a SQL function or to an expression. For example, you can use the UDFs in the following cases:

• The select list of a SELECT statement
• The condition of a WHERE clause
• ON CLAUSE, ORDER BY, and GROUP BY clauses
• The VALUES clause of an INSERT statement
• The SET clause of an UPDATE and DELETE statement

With UDFs, you can express your business logic and execute the business logic in the SQL engine, from where it can be called in your SQL queries.

Prerequisites

You must create UDFs as top-level functions before they can be used within an SQL statement. To use a user function in an SQL expression, you must own or have EXECUTE privileges on the user function.

Benefits of UDFs

• Extends programming richness via user code - Your database can now do more than only process data. With UDFs, databases can now also validate business logic and perform custom actions.
• Optimizes network traffic - UDFs allow data manipulation such that you do not need a lot of interaction with the database and you can optimize your results based on your business logic within the database. This reduces network bandwidth utilization.
• Reuse code, which can be called akin to stored procedures with business logic.

Limitations of UDFs

• When called in SQL queries, UDFs are executed only in a single thread. They follow a serial execution plan. Hence, if the business logic inside UDFs would work better in a multi-threaded environment, it would be better to not implement it as a user-defined function, since it would lead to overall performance degradation.
• UDFs cannot change database state, so there might be usage constraints if you are attempting to change the database information inside your business logic.
• The OpenEdge 11.7 release only supports scalar UDF.

When to Choose a UDF?

• You want a single value as the result. For example, total cost of a business trip, total project cost in man-days, or the birth date of a person.
• The return type can be any valid SQL type except arrays and lobs.

Example of an OpenEdge SQL User-Defined Function

Now that you know the benefits and limitations of a UDF, let’s walk through an example. We’ll create a function called days_between and see how to use it in a SQL select statement. This UDF accepts two input parameters named shipdate and orderdate which are of type date, calculates difference between them and returns a bigint value as output from the UDF.

Save Time and Bandwidth

With user-defined functions in OpenEdge 11.7, you can extend your SQL functionality to save time, gain capabilities and reduce bandwidth use. You can read more about UDFs in our documentation, and don’t forget to check out everything that’s new in OpenEdge 11.7.