PIPE statement

The PIPE statement is used to return a row from a compiled table function.

Invocation

This statement can be embedded in a compound SQL (compiled) statement of an SQL table function. It is not an executable statement and cannot be dynamically prepared.

Authorization

No privileges are required to invoke the PIPE statement. However, the authorization ID of the statement must hold the necessary privileges to invoke any expression that is embedded in the PIPE statement.

Syntax

Read syntax diagramSkip visual syntax diagramPIPE (,expressionNULL)(row-fullselect)row-expressionexpressionNULL

Description

( expression, . )
Specifies a row value is returned from the function. The number of expressions (or NULL keywords) in the list must match the RETURNS data type of the function and the value of each expression must be assignable to the corresponding column or field in the RETURNS data type of the function.
row-fullselect
Specifies a fullselect that returns a single row with the number of columns corresponding to the number of columns or fields in the RETURNS data type of the function. The value in each column of the row returned by the fullselect must be assignable to the corresponding column or field in the RETURNS data type of the function. If the result of the row fullselect is no rows, null values are returned.
row-expression
Specifies the row value is returned from the function. The number of fields in the row must match the RETURNS data type of the function and each field in the row must be assignable to the corresponding field in the RETURNS data type of the function. If the row-expression and the RETURNS data type are user-defined row types, the type names must be the same (SQLSTATE 42821).
expression
Specifies a scalar value is returned from the function. The RETURNS data type of the table function must have a single column and the expression value must be assignable to that column.
NULL
Specifies that a null value is returned from the function. A null value is returned for each column or row field.

Notes

  • Locally declared procedures: The PIPE statement cannot be used within a procedure that is locally declared in the compound SQL (compiled) statement of an SQL table function.
  • Similar terms: An SQL table function that uses a PIPE statement is sometimes referred to as a pipelined function.

Example

Create a table function called NEXT52 that returns a week number and date for the same day of the week for the next 52 weeks, along with the associated ISO week number.
CREATE OR REPLACE FUNCTION NEXT52 (START_TS TIMESTAMP)
   RETURNS TABLE (WEEKNUM SMALLINT, WEEKNUM_DATE DATE, ISO_WEEK SMALLINT)
BEGIN
   DECLARE WN INTEGER DEFAULT 1;
   DECLARE WND DATE;
   SET WND = START_TS;
   WHILE (WN < 53) DO
      SET WND = WND + 7 DAYS;
      PIPE (WN, WND, WEEK_ISO(WND));
      SET WN = WN + 1;
   END WHILE;
   RETURN;
END