A user defined scalar function (UDSF) is a function that is called once per row. Each time it is called, it takes a value or set of values for that row of input data and returns one value.
An example is a sales tax function. For each item purchased, this function returns the sales tax for that item. In the simplest case, the sales tax rate never changes and is hard-coded in the function itself. So, for example, suppose that the input table looks like this:
| Department | ItemName | Price |
|---|---|---|
| frozen foods | ice cream | 5.00 |
| deli | pizza | 15.00 |
| frozen foods | orange juice | 1.00 |
Suppose also that a sales tax rate of 10% is hard-coded into the function. The query might look like this:
SELECT itemName, price, salesTax(price) AS SalesTax FROM priceTable;
The output in this case is:
| ItemName | Price | SalesTax |
|---|---|---|
| ice cream | 5.00 | 0.50 |
| pizza | 15.00 | 1.50 |
| orange juice | 1.00 | 0.10 |
As you can see, the number of output rows is the same as the number of input rows.
A slightly more sophisticated version of the salesTax function might take more than one input. For example, the tax rate might be a parameter rather than hard-coded into the salesTax function, in which case the function might have 2 parameters:
SELECT itemName, price, salesTax(price, taxRate) AS SalesTax FROM priceTable; SELECT SUM(price) ...;