17.05 - Example: Function Processing with Any Input Table 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)

The function mapping definition usr_AllPairsShortestPath for the function AllPairsShortestPath specifies that vertices can be substituted for any input table that does not correspond to any of the other specified input tables.

CREATE FUNCTION MAPPING syslib.usr_AllPairsShortestPath
FOR AllPairsShortestPath SERVER coprocessor
USING
vertices(ANY) IN TABLE,
edges(edge) IN TABLE,
sources IN TABLE,
targets(dest) OUT TABLE,
TargetKey('col1'),MaxDistance(Distance),EdgeWeight('2'),
GroupCol('Col_'||GroupColumn||'_'||Distance),
Distance(SELECT distnum FROM ssqtbl WHERE username=CURRENT_USER),
Directed;

This SELECT statement specifies vertex and edges as table correlation names.

SELECT * FROM usr_AllPairsShortestPath (
ON callers AS vertex PARTITION BY callerid
ON calls AS edges 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. Because the function mapping definition specifies that vertices can be substituted for any table that does not correspond to any of the other specified input tables, vertices is substituted for vertex.

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;

This SELECT statement specifies vertices and edges as table correlation names.

SELECT * FROM usr_AllPairsShortestPath (
ON callers AS vertices PARTITION BY callerid
ON calls AS edges 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, specifying the vertices and edges input tables.

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;

This SELECT statement does not specify a correlation for the table callers and specifies edges as a correlation for calls.

SELECT * FROM usr_AllPairsShortestPath (
ON callers PARTITION BY callerid
ON calls AS edges 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. Because the function mapping definition specifies that vertices can be substituted for any table that does not have a correlation, vertices is correlated to callers.

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;