Creating SQL table functions

The task of creating SQL table functions can be done at any time.

Before you begin

Before you create an SQL table function, ensure that you have the privileges required to execute the CREATE FUNCTION (table) statement.


Restrictions

See: Restrictions on SQL functions

Procedure

  1. Define the CREATE FUNCTION (table) statement:
    1. Specify a name for the function.
    2. Specify a name and data type for each input parameter.
    3. Specify the routine attributes.
    4. Specify the RETURNS TABLE keyword.
    5. Specify the BEGIN ATOMIC keyword to introduce the function-body.
    6. Specify the function body.
    7. Specify the RETURN clause with brackets in which you specify a query that defines the result set to be returned.
    8. Specify the END keyword.
  2. Execute the CREATE FUNCTION (table) statement from a supported interface.

Results

The CREATE FUNCTION (table) statement should execute successfully and the table function should be created.

Example

Example 1
The following is an example of an SQL table function that is used to track and audit updates made to employee salary data:
CREATE FUNCTION update_salary  (updEmpNum CHAR(4), amount INTEGER) 
RETURNS TABLE (emp_lastname VARCHAR(10), 
			   emp_firstname VARCHAR(10), 
			   newSalary INTEGER) 
LANGUAGE SQL
MODIFIES SQL DATA
NO EXTERNAL ACTION
NOT DETERMINISTIC
BEGIN ATOMIC

  INSERT INTO audit_table(user, table, action, time)
    VALUES (USER, 'EMPLOYEE',
    'Salary update. Values: ' || updEmpNum || ' ' || char(amount), CURRENT_TIMESTAMP);

  RETURN (SELECT lastname, firstname, salary
    FROM FINAL TABLE(UPDATE employee SET salary = salary + amount WHERE employee.empnum = updEmpNum));

END
This function updates the salary of an employee specified by updEmpNum, by the amount specified by amount, and also records in an audit table named audit_table, the user that invoked the routine, the name of the table that was modified, and the type of modification made by the user. A SELECT statement that references a data change statement in the FROM clause is used to get back the updated row values.
Example 2
The following is an example of an SQL table function:
CREATE TABLE t1(pk INT, c1 INT, date DATE)

CREATE TABLE t1_archive LIKE T1%

CREATE FUNCTION archive_tbl_t1(ppk INT) 
  RETURNS TABLE(pk INT, c1 INT, date DATE)
LANGUAGE SQL 
MODIFIES SQL DATA 
BEGIN ATOMIC

  DECLARE c1 INT;

  DECLARE date DATE;

  SET (c1, date) = (SELECT c1, date FROM OLD TABLE(DELETE FROM t1 WHERE t1.pk = ppk));

  INSERT INTO T1_ARCHIVE VALUES (ppk, c1, date);

  RETURN VALUES (ppk, c1, date);
END%

What to do next

After creating the table function, you might want to invoke the function to test it.