17.05 - Example: Function Processing with Input Table Variable Substitution - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

If an input table correlation name in the SELECT statement matches an IN TABLE substitution variable in the function mapping definition, the corresponding IN TABLE parameter name in the function mapping definition is substituted for function processing.

The function mapping definition usr_AllPairsShortestPath for the function AllPairsShortestPath includes IN TABLE variables for substitution.

CREATE FUNCTION MAPPING usr_AllPairsShortestPath
FOR AllPairsShortestPath SERVER TD_SERVER_DB.coprocessor
USING
vertices(vertex) IN TABLE ,
edges(edge) IN TABLE ,
sources(source) IN TABLE ,
targets(trgt) IN TABLE ,
TargetKey(GroupCol) ,
EdgeWeight(2) ,
MaxDistance(Distance) ,
Directed ,GroupSize ,SequenceInputBy;

This SELECT statement specifies the vertex and edge as table correlation names.

SELECT * FROM usr_AllPairsShortestPath (
ON callers AS vertex PARTITION BY callerid
ON calls AS edge PARTITION BY callerfrom
USING
TargetKey ('callerto')
EdgeWeight ('calls')
MaxDistance ('-1')
) as dt ORDER BY source, target;

The query is rewritten as follows during function processing. The table correlation name vertices is substituted for vertex and the table correlation name edges is substituted for edge.

SELECT * FROM AllPairsShortestPath (
ON callers AS vertices PARTITION BY callerid
ON calls AS edges PARTITION BY callerfrom
TargetKey ('callerto')
EdgeWeight ('calls')
MaxDistance ('-1')
) ORDER BY source, target;