Pipelined Table Functions

A pipelined table function is a 100% pure SQL alternative to an external User Defined Table Function (UDTF).  A pipelined function uses the new PIPE SQL statement to return UDTF results, row by row.  A new form of the RETURN statement is also utilized to indicate the end of file (EOF) condition.

Read the programming details in the SQL Reference - PIPE statement.  An example is included in SQL Programming - table UDF example.

An overview of the new support is included below.

Ease of Implementation
Pipelined table functions are easy to implement.  The alternative to a pipelined function is typically a complex query, which may include advanced joins, selective aggregation, or CASE logic statements.  Many programmers choose to perform data conditioning inside table functions because they're more comfortable with the procedure constructs. 

Note: The best practice of embracing 'set at a time' processing (going to the query engine once instead of many times) remains the best SQL programming practice.  The power and flexibility provided by a pipelined function should be used in those cases where a traditional SQL UDTF is not a viable choice.

Additional Support
Pipelined table functions allow the flexibility to programmatically create 'virtual' tables with greater control than SELECT or CREATE VIEW can provide.  Some examples of this include: pass function parameters to provide behavior customization, perform data validation/correction/conversion, log bad values, return lines of text from a backup log file or other operating system file, return multiple rows from a single input row, reference multiple databases within a single statement, provide customized join behavior, perform outer joins to more than one table at once, and perform outer joins to a list of values or a subquery. 

Big Data / Analytics / Performance
Pipelined table functions can provide for more efficient processing of large amounts of data returned by an SQL table function.  Rather than building a temporary table (either on disk or in memory) and then returning the entire table to the data processor, the data can be returned to the processor one row at a time.  A temporary table is not necessary and the expense of creating a temporary table can be avoided.  This can provide memory savings and better performance for an SQL table function.

In some cases, only a subset of the data from an SQL table function is needed and pipelined table functions can be used to avoid creation of data that is not required.  Because only the rows which are required will be produced, this can provide better performance for an SQL table function.

Interoperability / Portability
This enhancement will enable portability of pipelined table functions from other platforms.

Examples
Use a PIPE statement to return rows from an SQL table function.

CREATE FUNCTION TRANSFORM() RETURNS TABLE ( EMPLOYEE_NAME CHAR(20), UNIQUE# INT )
BEGIN
  DECLARE EMPNAME VARCHAR(15);
  DECLARE MYRECNUM INTEGER DEFAULT 1;
  DECLARE AT_END INTEGER DEFAULT 0;
  DECLARE EMP_CURSOR CURSOR FOR SELECT lastname FROM employee;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
    SET AT_END = 1;

  OPEN EMP_CURSOR;
  MYLOOP: LOOP
    FETCH EMP_CURSOR INTO EMPNAME;
    IF AT_END = 1 THEN
      LEAVE MYLOOP;
    END IF;
    PIPE (EMPNAME, MYRECNUM);  -- return single row
    SET MYRECNUM = MYRECNUM + 1;
  END LOOP;

  CLOSE EMP_CURSOR;
  RETURN;
END;

The Database SQL programming manual includes a UDTF example:

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 above function returns the data with no error checking or validation of the data.  Conversion of that function to a pipelined function with extra error checking and verification:

CREATE FUNCTION PROJFUNC(indate DATE)
  RETURNS TABLE (PROJNO CHAR(6), ACTNO SMALLINT, ACTSTAFF DECIMAL(5,2),
           ACSTDATE DATE, ACENDATE DATE)
  LANGUAGE SQL
  BEGIN
   FOR C1 CURSOR FOR SELECT * FROM PROJACT
     WHERE ACSTDATE <= indate DO
     -- Add code for additional selection/verification of fields
     -- perhaps ignore certain project numbers
     -- perhaps validate staff information and/or modify it
     PIPE (PROJNO, ACTNO, ACTSTAFF, ACSTDATE, ACENDATE);
   END FOR;
   RETURN;
  END;