Example: SQL table UDFs

 

This example shows a table function that returns data based on a date.

CREATE FUNCTION PROJFUNC(indate DATE) 
  RETURNS TABLE (PROJNO CHAR(6), ACTNO SMALLINT, ACTSTAFF DECIMAL(5,2), 
           ACSTDATE DATE, ACENDATE DATE) 

  LANGUAGE SQL 
  BEGIN 
   RETURN SELECT * FROM PROJACT 
     WHERE ACSTDATE<=indate; 
  END

The function can then be called as:

SELECT * FROM TABLE(PROJFUNC(:datehv)) X

SQL table functions are required to have one and only one RETURN statement.

 

Parent topic:

Writing UDFs as SQL functions