The server and UDF form a producer and consumer relationship when exchanging rows of data.
Production and consumption refer to table row data. The producer produces table rows; the consumer consumes table rows.
The server executes scalar and aggregate UDFs once for each matching row of a query. These UDFs consume input scalar parameters and produce, and return, a single scalar parameter. This data exchange occurs during the evaluate method using the get_value() and set_value() APIs.
However, scalar production and consumption is an inefficient method of data exchange if your UDF must produce or consume a table. Table UDFs that produce a table, and TPFs that consume a table, use the row block data structure of the v4 API. Row blocks allow for bulk row and column data exchange. The row block is populated by a producer, and read from by a consumer.
In this example, the table UDF my_table_udf() is a producer of data. SAP Sybase IQ, the server, is the consumer of the data:
SELECT * FROM my_table_udf()
In general, a table UDF is always a producer of data. The server, however, may not always be the consumer:
SELECT * FROM my_tpf( TABLE( SELECT * FROM my_table_udf() ) )
The outer TPF, my_tpf(), is the consumer for the table input parameter specified by SELECT * from my_table_udf(). SAP Sybase IQ is the consumer of the table produced by the my_tpf() TPF. A TPF, therefore, can be both a consumer and a producer.
The TPF does not have to consume from a table UDF. In this example, the TPF consumes the table data produced by the inner query, which is produced by the SAP Sybase IQ server:
SELECT * FROM my_tpf( TABLE( SELECT * FROM my_table where my_table.c1 < 10 ) )
In a TPF, therefore, SAP Sybase IQ can be both the consumer and producer of table data.
In the v4 API, a row block defines a memory area where data is produced to, and consumed from. In general, the layout of a row block conceptually matches the row and column format of the table; a row block consists of a number of rows, and each row consists of a number of columns. Either the producer or consumer must allocate the row block, and must also deallocate it when the time comes.
Rows and column have their own specific attributes that only apply to them. For example, rows have a status flag which indicates if the row is present or not. This flag lets a TPF change the row status without having to move the column data. Columns have a null mask that indicates if the data value is null or not. Row blocks also have some additional attributes: maximum number of rows, and current number of rows, for example. These row block attributes are useful when a UDF wants to create a row block to handle a large set of rows, but produce a smaller number of rows as required.
The fetch_into is called when the consumer allocates the row block and passes it to the producer. The producer is then requested to populate as many rows as possible, up to the maximum number of rows. The fetch_block is called when the consumer wants the producer to allocate the row block. Fetch_block is efficient if you are developing a TPF that filters rows of data. The server (consumer) allocates the row block and fetches from the TPF using the fetch_into API. The TPF can then pass the same row block to the input parameter using the fetch_block API.