PIPE statement

The PIPE statement returns one row from a table function. An SQL table function that uses a PIPE statement is referred to as a pipelined function.

Syntax


                          .-,--------------.        
                          V                |        
>>-+--------+--PIPE--+-(----+-expression-+-+--)-+--------------><
   '-label:-'        |      '-NULL-------'      |   
                     +-(--row-fullselect--)-----+   
                     +-expression---------------+   
                     '-NULL---------------------'

Description

label: Specifies the label for the PIPE statement. The label name cannot be the same as the routine name or another label within the same scope. For more information, see References to SQL labels.
(expression, ...): Specifies a row value is returned from the function. The number of expressions or NULL keywords in the list must match the number of columns in the function result. The data types of each expression must be assignable to the data type of the corresponding column defined for the function result, using the storage assignment rules as described in Assignments and comparisons.
row-fullselect: Specifies a fullselect that returns a single row. The number of columns in the fullselect must match the number of columns in the function result. The data types of the result table columns of the fullselect must be assignable to the data types of the columns defined for the function result, using the storage assignment rules as described in Assignments and comparisons. If the result of the row fullselect is no rows, a null value is returned for each column. An error is returned if there is more than one row in the result.
expression: Specifies a scalar value is returned from the function. The table function must return a single column and the value of the expression must be assignable to that column.
NULL: Specifies that a null value is returned from the function. If there is more than one result column, a null value is returned for each column.

Example

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;