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
Procedure
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:
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.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
- 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.