16.20 - Function Types - 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)

Aggregate Intermediate Storage

The aggregate library functions allocate the intermediate storage you need for accumulating summary information in an aggregate function.

IF you want to … THEN use this library function …
allocate intermediate storage for accumulating summary information in an aggregate function FNC_DefMem

ARRAY Data Type Interface

ARRAY interface functions enable a UDF, UDM, or external stored procedure to access and set the values of the elements in an ARRAY data type.

IF you want to … THEN use this library function …
get the total number of elements in an ARRAY input parameter that are set to a value, including NULL FNC_GetArrayElementCount
get information such as the element data type, number of dimensions, total number of elements, and scope information about an ARRAY input parameter FNC_GetArrayTypeInfo_EON
get the number of dimensions defined for an ARRAY input parameter when this information is not known to the external routine FNC_GetArrayNumDimensions
set either one bit or all bits in a NullBitVector FNC_SetNullBitVector
check the value of one bit in a NullBitVector FNC_CheckNullBitVector
set one bit in a NullBitVector where the bit to be set may be referenced by the ARRAY type element index as specified by dimension FNC_SetNullBitVectorByElemIndex
check the value of one bit in a NullBitVector where the bit to be checked may be referenced by the ARRAY type element index as specified by dimension FNC_CheckNullBitVectorByElemIndex
get a range of one or more elements of an ARRAY type and for any elements that are either NULL or not present, set the bit to 0 in the NullBitVector FNC_GetArrayElements
set one or more elements of an ARRAY return parameter to the same value FNC_SetArrayElements
set the value of one or more elements of an ARRAY return parameter where each element can be set to a different value FNC_SetArrayElementsWithMultiValues
get one or more UDT_HANDLES that can be used to operate on the elements of an ARRAY data type whose element type is a UDT FNC_GetUDTHandles

DATASET Data Type Interface

The following functions enable a UDF or external stored procedure to access and set the value of a DATASET parameter, or to get information about the DATASET type parameter.

IF you want to … THEN use this library function …
get information about a DATASET type instance, such as the maximum length, inline length, and storage format FNC_GetDatasetInfo
get the schema of a DATASET type instance FNC_GetDatasetSchema
read the schema of a DATASET type instance, which is stored as a LOB, using the LOB FNC routines FNC_GetDatasetSchemaLob
get the value of a DATASET type instance when the DATASET data is not stored as a LOB FNC_GetInternalValue
set the value of a DATASET type instance when the DATASET data will not be stored as a LOB FNC_SetInternalValue
read DATASET data, which is stored as a LOB, using the LOB FNC routines FNC_GetDatasetInputLob
write DATASET data to a LOB associated with a DATASET instance FNC_GetDatasetResultLob
pass a LOB_LOCATOR to a DATASET type instance allowing the instance to use the data referenced by the locator to set its schema and binary-encoded Avro value FNC_SetDatasetLob

Geospatial Data Type Interface

The following functions allow you to access, manipulate, and return geospatial data of type ST_Geometry, MBR, and MBB using external routines.

IF you want to … THEN use this library function …
get the maximum length of an ST_Geometry type and an indication of whether or not it is stored as a LOB FNC_GetGeometryInfo
get the Well-Known Text (WKT) representation of an inline ST_Geometry value (that is, one that is not stored as a LOB) FNC_GeomGetWKT
get the WKT representation of an ST_Geometry value that is stored as a LOB FNC_GeomGetWKTClob
get the length in bytes of an ST_Geometry WKT FNC_GeomGetWKTSize
get the LOB locator to a CLOB for an ST_Geometry that is the return value of a UDF/UDM or an INOUT or OUT parameter for an external stored routine FNC_GeomGetResultWKTClob
set the value of an ST_Geometry type using a WKT string FNC_GeomSetWKT
set the value of an ST_Geometry type using a WKT CLOB FNC_GeomSetWKTClob
get the Well-Known Binary (WKB) representation of an inline ST_Geometry value (that is, one that is not stored as a LOB) FNC_GeomGetWKB
get the WKB representation of an ST_Geometry value that is stored as a LOB FNC_GeomGetWKBBlob
get the length in bytes of an ST_Geometry WKB FNC_GeomGetWKBSize
get the LOB locator to a BLOB for an ST_Geometry that is the return value of a UDF/UDM or an INOUT or OUT parameter for an external stored procedure FNC_GeomGetResultWKBBlob
set the value of an ST_Geometry type using a WKB representation of the geometry FNC_GeomSetWKB
set the value of an ST_Geometry type using a WKB BLOB representation of the geometry FNC_GeomSetWKBBlob
return a buffer containing four coordinate values that define an MBR (minimum bounding rectangle) type FNC_MBRGetValue
set the four coordinate values that define an MBR type using a buffer FNC_MBRSetValue
return a buffer containing six coordinate values that define an MBB (minimum bounding box) type FNC_MBBGetValue
set the six coordinate values that define an MBB using a buffer FNC_MBBSetValue

Global Information

The global information function returns session information related to the current execution of a UDF, UDM, or external stored procedure.

IF you want to … THEN use this library function …
get global information, such as user account, user name, user ID, and session number related to the currently running UDF, UDM, or external stored procedure FNC_DbsInfo_EON

GLOP Access

The GLOP access functions provide a way for external routines to map a GLOP set and access the data. Note that you must call FNC_Get_GLOP_Map first before calling any other GLOP function.

IF you want to … THEN use this library function …
get the GLOP set map information associated with an external routine FNC_Get_GLOP_Map
map different pages of a read-only GLOP or reload the single page of a read/write or globally modifiable GLOP FNC_GLOP_Map_Page
lock a vproc-local GLOP for reading or writing FNC_GLOP_Lock
unlock a previously locked GLOP FNC_GLOP_Unlock
copy the GLOP data in one mapping instance to the GLOP data of all mapping instances on all vprocs on all nodes FNC_GLOP_Global_Copy
get information as to the specific node ID, vproc ID, vproc type, and task ID for the currently running external routine FNC_Where_Am_I

JSON Data Type Interface

The following functions enable a UDF, UDM, or external stored procedure to access and set the value of a JSON parameter, or to get information about the JSON type parameter.

IF you want to … THEN use this library function …
get the maximum length and character set of a JSON type and an indication of whether or not the JSON data is stored as a LOB FNC_GetJSONInfo
get the maximum length, inline length, character set, storage format of a JSON type instance, and an indication of whether or not the JSON data is stored as a LOB FNC_GetExtendedJSONInfo
get the value of a JSON type instance when the JSON data is not stored as a LOB FNC_GetInternalValue
set the value of a JSON type instance when the JSON data will not be stored as a LOB FNC_SetInternalValue
get a LOB_LOCATOR for a JSON instance which has its data stored as a LOB, and use this locator with LOB FNC routines to read data from the JSON instance FNC_GetJSONInputLob
get a LOB_RESULT_LOCATOR to a LOB associated with a JSON instance, and use this locator with LOB FNC routines to write data to the JSON instance FNC_GetJSONResultLob

You can specify the JSON data type as an attribute of a structured UDT. When passing a structured UDT that includes a JSON attribute to or from an external routine, you can use the following interface functions to access or set the value of the JSON attribute, or get information about a JSON attribute.

IF you want to … THEN use this library function …
get information about a JSON attribute, such as the maximum length, character set, or whether or not the data of the attribute is stored as a LOB FNC_GetStructuredAttributeInfo_EON
get a string representation of a JSON attribute when the data of the JSON attribute is not stored as a LOB FNC_GetStructuredAttribute
set the string representation of a JSON document to a JSON attribute when the JSON data will not be stored as a LOB FNC_SetStructuredAttribute
get a LOB_LOCATOR to the LOB which stores the data of a JSON attribute and use this locator with LOB FNC routines to read data from the attribute FNC_GetStructuredInputLobAttribute
get a LOB_RESULT_LOCATOR to a LOB where the data of a JSON attribute may be stored and use this locator with LOB FNC routines to write data to the attribute FNC_GetStructuredResultLobAttribute

LOB Access

LOB access functions enable a UDF, UDM, or external stored procedure to use a locator to access the contents of a referenced object and to append data to a LOB object.

IF you want to … THEN use this library function …
get the length, in bytes, of a large object FNC_GetLobLength
append a sequence of bytes to a large object that is contained in a UDT or defined to be the result object of a UDF or external procedure FNC_LobAppend
release all resources associated with a read context, regardless of whether the end of data was reached FNC_LobClose
convert a locator into a persistent object reference FNC_LobLoc2Ref
establish a read context for subsequent sequential reads of a referenced object FNC_LobOpen
perform a sequential read using a specified context FNC_LobRead
convert a persistent object reference into a locator FNC_LobRef2Loc

Memory Allocation

To prevent memory leaks in the database, the sqltypes_td.h header file redefines malloc and free to call the Teradata library functions FNC_malloc and FNC_free.

Period Data Type Interface

To access or set the value of a Period parameter or return type, an external routine must use specific library functions.

IF you want to … THEN use this library function …
get the value of a Period type FNC_GetInternalValue
set the value of a Period type FNC_SetInternalValue

Query Band Access

A UDF, UDM, or external stored procedure can use query band access functions to retrieve query band name-value pairs that have been set on a session, transaction, or profile to identify the originating source of queries and help manage task priorities and track system use.

IF you want to … THEN use this library function …
retrieve the current query band string for the transaction, session, and profile FNC_GetQueryBand
retrieve transaction, session, or profile name-value pairs from the query band string that FNC_GetQueryBand returns FNC_GetQueryBandPairs
search the transaction, session, and/or profile name-value pairs in the query band string that FNC_GetQueryBand returns and retrieve the value for a specified name FNC_GetQueryBandValue

Stored Procedure Invocation

External stored procedures can call the stored procedure invocation function.

IF you want to … THEN use this library function …
call stored procedures from within an external stored procedure FNC_CallSP

String Argument and Result Processing

A UDF, UDM, or external stored procedure can use string argument and result processing functions when working with BYTE, CHAR, CHARACTER(n) CHARACTER SET GRAPHIC, or VARCHAR input parameters, output parameters, or results.

These functions are particularly useful for UDFs that are used for the algorithmic compression and decompression of table columns.

IF you want to … THEN use this library function …
determine the size of the output buffer that a scalar or aggregate UDF must return as the result FNC_GetOutputBufferSize
get the length, in bytes, for an external routine input argument that has a data type of BYTE FNC_GetByteLength
get the length, in bytes, for an external routine input argument that has a data type of CHAR FNC_GetCharLength
get the length, in bytes, for an external routine input argument that has a data type of CHARACTER CHARACTER SET GRAPHIC FNC_GetGraphicLength
get the length, in bytes, for an external routine input argument that has a data type of VARCHAR FNC_GetVarCharLength
set the length, in bytes, for an external stored procedure output parameter or the result of a UDF or UDM that has a VARCHAR data type FNC_SetVarCharLength

Table Function Processing

The table function processing functions are called by a table UDF.

IF you want to … THEN use this library function …
determine how the table function was called in the FROM TABLE clause of the SELECT statement and what action to perform FNC_GetPhase
FNC_GetPhaseEx
designate one copy of the table function to be a controlling copy that can distribute global control data among other copies running on other AMP vprocs FNC_TblControl
use a control scratchpad to propagate data from the table function control copy to all other copies running on all other AMP vprocs FNC_TblAllocCtrlCtx
FNC_TblGetCtrlCtx
use a general scratchpad to retain data between iterations of a local table function copy FNC_TblAllocCtx
FNC_TblGetCtx
not participate in the process of returning rows FNC_TblOptOut
gracefully abort a request when a copy of the table function encounters an error condition and cannot continue FNC_TblAbort
obtain node ID and AMP ID information that allows table functions to configure themselves to run on specific AMPs FNC_TblGetNodeData
FNC_AMPInfo
implement a table function that can run on any AMP and only needs one copy to participate in the transaction and request FNC_TblFirstParticipant
get the definitions of the result columns that must be returned by a table function with dynamic result row specification FNC_TblGetColDef

Table Operator Interface

The table operator interface functions allow table operator and contract function writers to access and set metadata. They also provide an interface to read and write rows in input and output streams.

To run in nonprotected mode, C language table operators must be thread safe since the AMP environment is multithreaded.

The table operator FNC functions handle character sets as follows:

  • The character set of any string parameter will match the character set defined when the table operator was created. This is the same as existing UDF behavior.
  • Any functions (such as FNC_TblOpGetCustomValue) that perform string comparisons will be case insensitive.

The following functions provide access to the metadata associated with the entire operator or with a specific stream. They also provide mechanisms to set metadata for the entire operator or for an individual stream.

IF you want to … THEN use this library function …
retrieve the unique identifier associated with a table operator FNC_TblOpGetUniqID
retrieve column definitions of a stream FNC_TblOpGetColDef
retrieve the number of columns in a stream FNC_TblOpGetColCount
get the information on the base type or attribute types for a UDT or complex data type (CDT) FNC_TblOpGetBaseInfo
retrieve metadata information about one or more UDT columns for an input or output stream FNC_TblOpGetUDTMetadata
retrieve information on all of the attributes of a structured UDT type FNC_TblOpGetStructuredAttributeInfo
retrieve the number of Custom clause keys FNC_TblOpGetCustomKeyCount
retrieve the number of values associated with a key in a Custom clause, their total size in bytes, and their type FNC_TblOpGetCustomKeyInfoOf
retrieve the number of values in a Custom clause at a given index, their total size in bytes, and their type FNC_TblOpGetCustomKeyInfoAt
retrieve all the values associated with a key in a Custom clause FNC_TblOpGetCustomValuesOf
retrieve the alias name (AS name) associated with an input stream FNC_TblOpGetAsClauseName
retrieve the HASH BY information for an input stream FNC_TblOpGetHashByDef
retrieve the number of columns in the HASH BY clause FNC_TblOpGetCountHashByDef
retrieve the LOCAL ORDER BY information for an input stream FNC_TblOpGetLocalOrderByDef
retrieve the number of columns in the LOCAL ORDER BY clause FNC_TblOpGetCountLocalOrderByDef
allow the contract function writer to set the HASH BY specification FNC_TblOpSetHashByDef
allow the contract function writer to set the ordering specifications FNC_TblOpSetLocalOrderByDef
find out whether or not the input to the table operator is DIMENSION input FNC_TblOpIsDimension
communicate the output columns of an output stream to the parser FNC_TblOpSetOutputColDef
set an opaque binary string value (the contract function context) that the contract function passes to the associated table operator at execution time. FNC_TblOpSetContractDef
retrieve the length of the contract function context FNC_TblOpGetContractLength
retrieve the contract function context FNC_TblOpGetContractDef
set the format of an input or output stream FNC_TblOpSetFormat
get the default format or the format set (by FNC_TblOpSetFormat) in the contract function FNC_TblOpGetFormat
generate an error message FNC_TblOpSetError
access information about the calling table operator FNC_TblOpGetFunctionDef

These functions are LOB-related interface functions.

IF you want to … THEN use this library function …
establish a read context for subsequent sequential reads of a referenced object FNC_LobOpen_CL
convert an output column index into a LOB_RESULT_LOCATOR, which can then be used by FNC_LOBAppend FNC_LobCol2Loc
retrieve the length of an input LOB FNC_GetLobLength_CL

The following functions allow table operator writers to open streams, read rows, write rows, and close streams. In addition, you can get and set values of specific attributes in the current row of a stream.

IF you want to … THEN use this library function …
get the number of input and output streams passed to the table operator FNC_TblOpGetStreamCount
initialize the iterator interface for reading or writing a stream FNC_TblOpOpen
read rows from an input stream and set the read context to the next input row of data FNC_TblOpRead
access a specific input attribute value FNC_TblOpGetAttributeByNdx
bind an output attribute value to a memory location FNC_TblOpBindAttributeByNdx
write current output data to spool and set output context to the next output row FNC_TblOpWrite
close a stream and flush the data to the database FNC_TblOpClose

These functions provide access to input or output buffers.

IF you want … THEN use this library function …
high performance direct buffer read access to the current input buffer FNC_TblOpReadBuf
high performance direct buffer read access with support for multiple input streams FNC_TblOpReadBufEx
high performance direct buffer write access FNC_TblOpWriteBuf

The following function disables the cogroup functionality for table operators that handle multiple inputs streams.

IF you want … THEN use this library function …
to turn off cogroup functionality FNC_TblOpDisableCoGroup

The following functions can be used by table operators to import and export data from and to foreign servers.

IF you want to … THEN use this library function …
get values that hash to the specified AMPs FNC_GetAmpHash
determine the AMP which would be responsible for a key based on the input FNC_GetHashAmp
set the number of rows exported FNC_SetActivityCount
obtain node ID and AMP ID information that allows table functions and table operators to configure themselves to run on specific AMPs FNC_TblGetNodeData
record the number of bytes transferred between Teradata Database and the foreign server by the table operator FNC_TblOpBytesTransferred
get the information on the base type or attribute types for a UDT or complex data type (CDT) FNC_TblOpGetBaseInfo
retrieve column definitions of a stream and get the output column definition for the contract function FNC_TblOpGetColDef
retrieve the contract function context FNC_TblOpGetContractDef
get the phase in the parser from which the contract function is being called FNC_TblOpGetContractPhase
get the text query string for the foreign server and get the interface version that is currently supported FNC_TblOpGetExternalQuery
get the contract definition of a nested inner table operator for the outer table operator to use FNC_TblOpGetInnerContract
set an opaque binary string value (the contract function context) that the contract function passes to the associated table operator at execution time FNC_TblOpSetContractDef
reset the lengths in column definitions for VARCHAR data types FNC_TblOpSetDisplayLength
set the EXPLAIN text when the table operator has the hexplain custom clause set FNC_TblOpSetExplainText
set attributes of the format of the input and output streams FNC_ TblOpSetFormat
allow the contract function writer to set the HASH BY specification FNC_TblOpSetHashByDef
set casting statements on the input columns so that the data types are cast as indicated by the caller FNC_TblOpSetInputColTypes
allow the contract function writer to set the ordering specification when developing table operators FNC_TblOpSetLocalOrderByDef

For details about the data structures used by the table operator FNC functions, see Table Operator Data Structures.

TD_ANYTYPE Parameter Access

The TD_ANYTYPE parameter access function enables a UDF, UDM, or external stored procedure to retrieve information about TD_ANYTYPE input and output parameters.

IF you want to … THEN use this library function …
get information about the TD_ANYTYPE arguments passed into a routine FNC_GetAnyTypeParamInfo_eon

Trace

The trace library functions let you get trace output for debugging purposes during UDF, UDM, and external stored procedure development.

IF you want to … THEN use this library function …
retrieve the function trace string specified in the SET SESSION FUNCTION TRACE statement FNC_Trace_String
write trace output into a temporary trace table defined by a CREATE GLOBAL TEMPORARY TRACE TABLE statement FNC_Trace_Write_DL

UDT Interface

UDT interface functions enable a UDF, UDM, or external stored procedure to access and set the value of a distinct UDT or attribute values of a structured UDT.

IF you want to … THEN use this library function …
get the value of a distinct type FNC_GetDistinctValue
set the value of a distinct type FNC_SetDistinctValue
get the locator for a distinct type that represents a LOB FNC_GetDistinctInputLob
FNC_GetDistinctResultLob
get the number of attributes of a structured type FNC_GetStructuredAttributeCount
get information, such as data type, about the attributes of a structured type FNC_GetStructuredAttributeInfo_EON
get the attribute value of a structured type FNC_GetStructuredAttribute
FNC_GetStructuredAttributeByNdx
set the attribute value of a structured type FNC_SetStructuredAttribute
FNC_SetStructuredAttributeByNdx
get the locator for a structured type LOB attribute FNC_GetStructuredInputLobAttribute
FNC_GetStructuredInputLobAttributeByNdx
FNC_GetStructuredResultLobAttribute
FNC_GetStructuredResultLobAttributeByNdx

UDT Serialization

These functions are used to serialize/deserialize UDT parameters and return values for UDTs that support serialization and deserialization.

IF you want to … THEN use this library function …
determine whether a UDT supports serialization and deserialization FNC_UdtSerializeSupported
retrieve the actual size of a UDT in its serialized form FNC_UdtGetSerializeSize
retrieve a UDT in its serialized format FNC_UdtSerialize
deserialize data in a UDT’s serialized format back into the UDT FNC_UdtDeserialize

XML Data Type Interface

The following functions enable a UDF, UDM, or external stored procedure to access and set the value of an XML parameter, or to get information about the XML type parameter.

IF you want to … THEN use this library function …
get information about the XML value including its size and whether it stores its value as a LOB FNC_GetXMLInfo
get the value of an XML type instance when the XML data is not stored as a LOB FNC_GetXML
get a LOB locator for the CLOB representation of the XML type, and use this locator with LOB FNC routines to read data FNC_GetXMLClob
get a LOB_RESULT_LOCATOR that will be used with LOB FNC routines to set the CLOB value ( the XML return value) FNC_GetXMLResultClob
set the value of an XML type instance when the XML data will not be stored as a LOB FNC_SetXML
set the XML return value or OUT parameter value using a CLOB locator returned by FNC_GetXMLResultClob FNC_SetXMLClob
get the value of an XML type in UTF-8 binary encoding when the XML data is not stored as a LOB FNC_GetXMLByte
get a LOB locator for the BLOB representation (in UTF-8 encoding) of the XML type, and use this locator with LOB FNC routines to read data FNC_GetXMLBlob
set the value of an XML type instance (using a value in UTF-8 encoding) when the XML data will not be stored as a LOB FNC_SetXMLByte
get a LOB_RESULT_LOCATOR that will be used with LOB FNC routines to set the BLOB value ( the XML return value) FNC_GetXMLResultBlob
set the XML return value or OUT parameter value using a BLOB locator returned by FNC_GetXMLResultBlob FNC_SetXMLBlob