UDTF examples

This example unpivots sales data that is stored in a row containing sales by quarter into four rows of data, one for each quarter.

--	Usage Example:
--	select * from table(unpivot_sales(2009,100,200,300,400));

function processRow(y,q1,q2,q3,q4)
sls = {}
sls[1] = { y, 1, q1 }
sls[2] = { y, 2, q2 }
sls[3] = { y, 3, q3 }
sls[4] = { y, 4, q4 }
return sls

end	

function getType()	
return "UDTF"
	
end	
function getName()	
return "unpivot_sales"
	
end	
function getArgs()	
args={}	"integer" }
args[1] = { "year",	
args[2] = { "q1sales",	"double"	}
args[3] = { "q2sales",	"double"	}
args[4] = { "q3sales",	"double"	}
args[5] = { "q4sales",	"double"	}
return args

end	
function getShape()	
columns={}	"integer" }
columns[1] = { "year",	
columns[2] = { "quarter",	"integer" }
columns[3] = { "sales",	"double"  }
return columns	
end

The second UDTF example utilizes the outputRow() and outputFinalRow() methods to output rows instead of relying only on the processRow() method. This UDTF performs the same task as the first example, plus it also outputs one final row that contains the total value of all sales processed by the UDTF.

--	Usage Example:
--	select * from table with final(unpivot_final(2010,100,200,300,400));

total=0
function processRow(y,q1,q2,q3,q4)
sls = {}
sls[1] = { y, 1, q1 }
sls[2] = { y, 2, q2 }
sls[3] = { y, 3, q3 }
sls[4] = { y, 4, q4 }
total = q1 + q2 + q3 + q4
return null

end

function outputRow(rownum)
if sls[rownum] == null then return null end
return sls[rownum]
end

function outputFinalRow(rownum)
if rownum > 1 then return null end
return { null, null, total }

end	
function getType()	
return "UDTF"	
end	
function getName()	
return "unpivot_final"	
end	
function getArgs()	
return {{ "year", "integer"}, 

{ "q1sales", "double" },
{ "q2sales", "double" },
{ "q3sales", "double" },
{ "q4sales", "double" }}

end	

function getShape()	
return {{ "year",	"integer" },
{ "quarter", "integer" }, 
{ "sales", "double" }}
end