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
.-,--------------.
V |
>>-PIPE--+-(----+-expression-+-+--)-+--------------------------><
| '-NULL-------' |
+-(--row-fullselect--)-----+
+-row-expression-----------+
+-expression---------------+
'-NULL---------------------'
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