# CHAPTER 6: User Defined Functions

## 6.1 Introduction

The Empress 4GL is a high-level programming interface through which databases can be accessed using the Empress 4GL script language which provides much of the same functionality as the ANSI SQL standard. However, many sites have data processing needs which are unique, and not contained in the standard. System administrators can customize their version of Empress by adding "User Defined Functions" (UDFs) which meet these needs. UDF's added at this "system" level can then be made available to all users who create applications using the 4GL. The advantages of this are clear: an expanded 4GL script language capable of doing customized processing, consistent function behaviour system-wide, and increased user productivity by eliminating redundant development costs.

As an example, an audio-visual application may need to process data through a series of spatial/temporal filters. The bulk data could be stored in a table in its "raw" form, and the filters in a second table. Users could then select data/filter combinations, and could then output the processed data to a monitor and speaker. The processing operations would be UDFs.

User defined functions can be made accessible to users through the SQL interface only, the 4GL interface only, or through both the SQL and 4GL interfaces. Here, we describe only how to install these functions for use in the 4GL. Creation of the actual functions is the same for both SQL-based and 4GL-based UDFs, and is described in detail in the Empress User Defined Functions manual. In the following sections, we assume that the user is familiar with creating user defined functions, and so we mention a number of C-structures which we only briefly describe. For a more detailed description, see the Empress User Defined Functions manual.

For those with no experience in creating user defined functions, we offer the following synopsis. A UDF is defined in Empress by a number of structures which contain information regarding the keyword, the type of function/operator, and the manner in which the function handles input and output. There are two types of UDFs in Empress: SC (Standard C) and DT (Data Type), each having different advantages and drawbacks which are described in the Empress User Defined Functions manual. However, for the purposes of incorporating a UDF into the 4GL, these differences can be ignored. Finally, we note that the structures which define a UDF in Empress include the following:

1. a keyword/operator specification in the Keyword table which gives the name of the UDF and its type (e.g., function or operator),
2. a User Function Table which connects the keyword to the actual user-supplied C routine, and
3. the C-routine itself. Although an actual UDF requires more structures than just these, the above are all that will be needed for the following discussion.

## 6.2 Function Precedence

By locating the data structures and code describing the UDF in different places, the system administrator can affect where various functions can be accessed. That is, the UDF can be accessible to SQL only, 4GL only, or both.

We begin with a list of the major files, table structures, and their usage. All files are in the Empress `custom` subdirectory.
Table 6-1

File Keyword | Table Name | Description
`usrstdfns.c` | `usrsktab` | SC function keywords
`usrfns.c` | `usrxktab` | DT function keywords

The list of keyword table names above is contained in a "master list" called `usrktabs` in the file `usrtabs.c`. For Empress SQL, the order in which the keyword tables are named in `usrktabs` determines how a given keyword gets evaluated when there are multiple keyword definitions.

However, the Empress 4GL functions also have a keyword table that is not included in the above:
Table 6-2

File | Keyword Table Name | Description
`aptabs.c` | `usraktab` | 4GL function keywords

This table is only used in the 4GL, where it is searched first, followed by the keywords defined in the SC and DT keyword tables.

Note, however, that if the keyword is to be available only within the 4GL, its keyword is not necessarily placed in one `aptabs.c` file (see next section).

In all cases, after the user function tables are searched, Empress searches the built-in function tables. These function tables would contain the standard Empress 4GL functions such as `next_rec` or `concat` for example.

## 6.3 Relevant Elements of File "aptabs.c"

The file `aptabs.c` contains three relevant sections for the purposes of our discussion. These are

1. the Keyword table (`usraktab`),
2. the User Function table (`usratab`),
3. C-routines written by the user.

These same sections are present in the `usrstdfns.c` and `usrfns.c` files, and have analogous purposes. For more on this, see the Empress User Defined Functions manual.

## 6.4 Function Accessibility

Accessing UDF's through various parts of Empress (e.g., Empress SQL, Empress 4GL, Empress Report Writer, mr routines) requires making entries in the appropriate keyword tables above. Functions available in the 4GL can be made invisible in the SQL, and vice-versa. It is also possible to have a single function available in both the SQL and 4GL. The following table summarizes the situation:
Table 6-3

Available In: | Add Keyword to Table:
`4GL` | `SQL` | `usrxktab (DT)` | `usrsktab (SC)` | `usraktab (4GL)`
`no` | `no` | `no` | `no` | `no`
`no` | `yes` | `yes*` | `yes*` | `no`
`yes` | `no` | `no` | `no` | `yes`
`yes` | `yes` | `yes*` | `yes*` | `yes`

(*) for each function,, add a keyword to one of these tables as appropriate.

We will now consider how to implement each of the last two cases:

A. UDF Available in both SQL and 4GL:

Let us assume that you create a UDF as a regular SC or DT function accessed through SQL, and that you want to make this function available in the 4GL as well. Thus, you will already have made an entry into either `usrxktab` or `usrsktab`. To make this UDF available to the 4GL, you must:

1. make a keyword entry in usraktab in `aptabs.c`, but
2. make no user function table entry in `aptabs.c`, and
3. make no (new) C-function body in `aptabs.c`; the old C-function in `usrstdfns.c`/`usrfns.c` will be picked up.

Doing this makes the 4GL recognize the keyword and look for its definition in the SQL set of UDFs.

B. UDF Available only in the 4GL:

Here, we assume that the UDF which you want to create does not currently exist. There are two ways in which a UDF can be made available only to the 4GL, and each method allows for slightly different capabilities. The two methods are:

Method 1:

1. make a keyword entry in `usraktab` in `aptabs.c`,
2. make an entry in the User Function Table of
- `usrstdfns.c` (if the UDF is of SC type),
- `usrfns.c` (if the UDF is of DT type)
Also, put the C-function body in this file.
3. do not make a keyword entry in SC/DT keyword table (i.e., `usrsktab` or `usrxktab`)

Method 2:

1. make an entry in the User Function Table in file `aptabs.c`,
2. put the C-function body in `aptabs.c`,
3. do not make any keyword entry anywhere (i.e., no entry in `usraktab`, `usrsktab` or `usrxktab`).

The differences between these methods are in the types of functions which can be defined, and where they can be used. Here is a summary:

Method 1 UDF's:
- cannot be used in a `call()` in a 4GL script, i.e., cannot be used as a procedure
- can be any operator/function keytag type

Method 2 UDF's:
- can be accessed through a `call()` in a 4GL script
- are SC-type "tuple" functions (arguments are passed by the C `(argc, argv)` convention) with a keytag type of `opf_x`.

Details on SC versus DT type functions, the meaning of keytags, the importance of Keyword tables, User Function tables, etc., is explained in detail in the manual Empress User Defined Functions. If the database administrator plans to add UDFs to the database, this manual must be consulted.

## 6.5 Function Incorporation

Once you have created the desired entries in the files `aptabs.c`, `usrfns.c` and `usrstdfns.c`, you must compile and relink the executables. This procedure is discussed in the manual Empress User Defined Functions in the following chapters:

File | See
`usrstdfns.c` | Chapter 4, "Standard C Functions"
`usrfns.c` | Chapter 5, "Data Type Functions"
`aptabs.c` | Chapter 6, "4GL Functions"

``