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
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