Describing Scalar and Table User-Defined Functions

There are multiple methods used to define UDFs in SAP HANA:

• Executing direct SQL statements in the SQL Console of SAP Web IDE to generate the run-time object — this is not recommended as this approach provides no support for managing the lifecycle of functions, such as adjusting the code of an existing function. It also means the function is not considered as part of a complete set of development files that should be built together as a complete unit.

• Defining the UDF in a development source file of the type .hdbfunction in the SAP Web IDE Development view, and then building it to generate the runtime object — this is the recommended approach. Code samples will still refer to the direct SQL method (using the CREATE FUNCTION statement) so as to provide a complete example. The chief syntactic difference is that .hdbfunction files omit the CREATE keyword.

You can create scalar user-defined functions to use like built-in functions. The SQL statement to define UDFs is CREATE FUNCTION. The basic syntax to define a scalar UDF using the direct SQL method is:

123456
CREATE FUNCTION <function name> (<list of input parameters with type>)
RETURNS <scalar result parameter name and type>
AS
BEGIN
   <function body>
END;

Following is an example of using scalar user-defined functions like built-in functions. Remember to prefix parameter names with : to retrieve their values.

12345678910111213
-- create a function that converts overtime hours to minutes

CREATE FUNCTION Convert_Hours (im_hours INTEGER)
RETURNS ex_result DEC(5,2)
AS 
BEGIN
ex_result := :im_hours * 60;
END;

-- after the function is created, use it in a SELECT statement:

SELECT PNr, Name, Overtime, Convert_Hours(Overtime) AS Overminutes
    FROM Official;

UDFs are deleted using the DROP FUNCTION command:

1
DROP FUNCTION <function name>;

You can also use imperative logic in scalar UDFs, to the extent that this does not conflict with the restrictions above. Imperative language constructs allow the developer to control data and execution flow.

• Recall the capabilities of imperative logic:

  • Scalar variable manipulation

  • Branching logic

  • Loops

  • DDL statements and INSERT, UPDATE, and DELETE statements

• Imperative logic is executed exactly as scripted and is procedural. It prevents parallel processing.

You can use IF statements in function bodies.

12345678910111213141516171819
-- modify the previous function to convert overtime hours to a specified unit of time

CREATE FUNCTION Convert_Hours (im_hours INTEGER, im_to VARCHAR(1))
RETURNS ex_result DEC(5,2)
AS 
BEGIN
IF :im_to = 'm' 
    THEN ex_result := :im_hours * 60;
  ELSEIF :im_to = 'd' 
    THEN ex_result := :im_hours / 24;
  ELSE ex_result := :im_hours;
END IF;   

END;

-- after the function is created, use it in a SELECT statement:

SELECT PNr, Name, Convert_Hours(Overtime, 'm') AS Overminutes
    FROM Official;

A function should always be created as a source file in the Development view of SAP Web IDE. Click a project folder that belongs to a HDB module and use the menu option New → Function. You don’t need to specify the extension .hdbfunction as this is automatically appended to the name you provide for the file.

As this is the recommended approach to define a function, let’s take a look at it now.

The file opens with basic shell code for you to complete. When you have finished writing the code, you simply save, then build the object. Assuming you have no errors, a runtime function is created in the container that belongs to the project. You can then open the SQL Console against the container and write some SQL to test your function.

If the function does not behave the way you expected, you can then enjoy the benefit of having a source file where you simply modify the code in the source file, and re-save and re-build before re-testing. If you were using SQL statements directly to create the function, you would have to hope you saved the SQL code somewhere, otherwise you would have to begin again and re-enter it all over.

You do not drop the function directly using a DROP statement in SQL. If you wish to remove a function you simply delete the source file. Then, in the next build of the folder in which it was stored, the run-time object is then deleted.

When defining table UDFs or stored procedures, a useful feature is dynamic filtering using the built-in table function APPLY_FILTER. This function allows you to apply a dynamic WHERE clause to a database table or table variable and assign the result to a table variable, as shown below.

Syntax:

1
<var_name> = APPLY_FILTER(<table_or_table_variable>, <filter_variable_name>);

The first argument is the table or table variable that will be filtered as if it had a WHERE clause added. The second argument is a character expression that describes the tests to be applied to the first argument. By providing parameters for the tests, you can apply a different WHERE to the same table on subsequent calls of the function,

If the following were included in a function:

12345
DECLARE filterin varchar(100):='birthday is not null';
filterout = APPLY_FILTER(Owner, :filterin);
	
SELECT * 
    FROM :filterout;

The result would be same as:

123
SELECT *
    FROM Owner
WHERE Birthday is not null

In this case, the filter expression can be any logical test up to 100 characters long, since that is the definition of the filterin variable.

12345678910111213141516171819202122
-- modify the previous function to convert the overtime of officials to the desired
-- unit, for whatever subset of the Officials table is desired, up to 1000 characters of
-- WHERE clause test
-- use the previously-created Convert_Hours function for the time conversion

CREATE FUNCTION Convert_OfficialsHours (im_filter VARCHAR(1000), im_to VARCHAR(1) )
RETURNS TABLE 
(PNr NVARCHAR(3),
 Name NVARCHAR(20),
 Overtime DEC(5,2))
AS 
BEGIN
lt_official = APPLY_FILTER(Official, :im_filter);
RETURN 
    SELECT PNr, Name, Convert_Hours(Overtime, :im_to) AS Overtime
        FROM :lt_official;
END;

-- after the function is created, use it in a SELECT statement:

SELECT *
    FROM Convert_OfficialsHours('Name LIKE ''%Ms%''','d');