16.20 - Table Operator Basic Structure - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)
The table operator must perform the iteration over the input rows; therefore, you should structure the operator as follows:
  • Open the input stream.
  • Process the rows by iteration.
  • Close the stream.

You define a table operator by specifying the SQLTABLE parameter style in the CREATE FUNCTION statement. With this parameter style, a table operator is passed an input row in the IndicData format and is expected to return the output row in the IndicData format as default.

For input streams:
  • You can open and close an input stream multiple times within a table operator invocation. Each open resets the read position to the start of the stream.
  • You can optionally decide to never open the input stream, read only a subset of the input rows or open but never read an input stream.
  • You cannot close an input stream that is not open, open an already open input stream, or read past the end of file.
For output streams:
  • You can open and close an output stream a single time within a table operator invocation.
  • You can optionally decide to never open the output stream or open but never write the output stream.
  • You cannot close an output stream that is not open, open an already open output stream, or close and reopen an output stream.

The completion determination mechanism is when the table operator exits.

If the PARTITION BY clause was specified, then opening and closing a stream refers to the rows within the partition. For example, End of File occurs after the last row in the partition was read. If PARTITION BY was not specified, then opening and closing a stream refers to the rows within the AMP.

The following SQLTABLE iterator functions are available for table operator writers:
  • FNC_TblOpGetStreamCount
  • FNC_TblOpOpen
  • FNC_TblOpRead
  • FNC_TblOpGetAttributeByNdx
  • FNC_TblOpBindAttributeByNdx
  • FNC_TblOpWrite
  • FNC_TblOpClose

For more information about these functions, see Table Operator Interface.