SQL functions allow you to name queries and store them in the database for later access. This section describes a variety of SQL functions of increasing complexity.
Functions are created with the CREATE FUNCTION command and removed with DROP FUNCTION. CREATE FUNCTION requires the following information:
![[*]](cross_ref_motif.gif)
shows the creation of a simple
SQL function to convert a temperature from Fahrenheit to
centigrade degrees.
It supplies the following information to CREATE FUNCTION:
The function action in ftoc() uses SELECT to perform a computation. It does not access any tables. The $1 in the SELECT is automatically replaced by the first argument of the function call. If a second argument were present, it would be represented as $2.
When the query SELECT ftoc(68) is executed, it calls ftoc(). This function replaces $1 with 68 and then makes the computation. In a sense, it executes a SELECT inside a SELECT. The outer SELECT calls ftoc(), and ftoc() uses its own SELECT to perform the computation.
Constants in the function contain decimal points, so floating-point computations are performed. Without them, division would be performed using integers. For example, the query SELECT 1/4 returns 0, whereas SELECT 1.0/4.0 returns 0.25.
Figure shows an SQL server-side
function to compute a tax.
test=> CREATE FUNCTION tax(numeric)
test-> RETURNS numeric
test-> AS 'SELECT ($1 * 0.06::numeric(8,2))::numeric(8,2);'
test-> LANGUAGE 'sql';
CREATE
test=> SELECT tax(100);
tax
------
6.00
(1 row)
The casts to NUMERIC(8,2) are required because
the result of the computation must be rounded to two decimal places.
This function uses the more compact double-colon form of type casting,
rather than CAST. Section
provides more information about type casting. SELECT tax(100)
performs a simple computation, similar to ftoc().
One powerful use of server-side functions is their use in SQL
queries. Figure shows the use
of tax() with the part table from Figure .
test=> CREATE TABLE part (
test(> part_id INTEGER,
test(> name CHAR(30),
test(> cost NUMERIC(8,2),
test(> weight FLOAT
test(> );
CREATE
test=> INSERT INTO part VALUES (637, 'cable', 14.29, 5);
INSERT 20867 1
test=> INSERT INTO part VALUES (638, 'sticker', 0.84, 1);
INSERT 20868 1
test=> INSERT INTO part VALUES (639, 'bulb', 3.68, 3);
INSERT 20869 1
test=> SELECT part_id,
test-> name,
test-> cost,
test-> tax(cost),
test-> cost + tax(cost) AS total
test-> FROM part
test-> ORDER BY part_id;
part_id | name | cost | tax | total
---------+--------------------------------+-------+------+-------
637 | cable | 14.29 | 0.86 | 15.15
638 | sticker | 0.84 | 0.05 | 0.89
639 | bulb | 3.68 | 0.22 | 3.90
(3 rows)
In this figure, three rows are inserted into the table, then a SELECT displays columns from the part table with additional computed columns showing the tax and the cost plus tax.
Figure shows a more complex function
that computes shipping charges.
test=> CREATE FUNCTION shipping(numeric)
test-> RETURNS numeric
test-> AS 'SELECT CASE
test'> WHEN $1 < 2 THEN CAST(3.00 AS numeric(8,2))
test'> WHEN $1 >= 2 AND $1 < 4 THEN CAST(5.00 AS numeric(8,2))
test'> WHEN $1 >= 4 THEN CAST(6.00 AS numeric(8,2))
test'> END;'
test-> LANGUAGE 'sql';
CREATE
test=> SELECT part_id,
test-> trim(name) AS name,
test-> cost,
test-> tax(cost),
test-> cost + tax(cost) AS subtotal,
test-> shipping(weight),
test-> cost + tax(cost) + shipping(weight) AS total
test-> FROM part
test-> ORDER BY part_id;
part_id | name | cost | tax | subtotal | shipping | total
---------+---------+-------+------+----------+----------+-------
637 | cable | 14.29 | 0.86 | 15.15 | 6.00 | 21.15
638 | sticker | 0.84 | 0.05 | 0.89 | 3.00 | 3.89
639 | bulb | 3.68 | 0.22 | 3.90 | 5.00 | 8.90
(3 rows)
This function uses CASE to compute shipping charges based on weight. It calls shipping() to generate a detailed analysis of the tax and shipping charges associated with each part. It prints the part number, name, cost, tax, subtotal of cost plus tax, shipping charge, and total of cost, tax, and shipping charge. The SELECT uses trim () to remove trailing spaces and narrow the displayed result.
If the tax rate or shipping charges change, you can easily modify the function to reflect the new rates. Simply use DROP FUNCTION to remove the function and then recreate it with new values. All user applications will automatically begin using the new version because the computations are embedded in the database, not in the user applications.
Server-side functions can also access database tables. Figure
shows an SQL function that internally accesses the statename
table.
test=> CREATE FUNCTION getstatename(text)
test-> RETURNS text
test-> AS 'SELECT CAST(name AS TEXT)
test-> FROM statename
test-> WHERE code = $1;'
test-> LANGUAGE 'sql';
CREATE
test=> SELECT getstatename('AL');
getstatename
--------------------------------
Alabama
(1 row)
It looks up the proper state name for the state code supplied to the function.
Figure shows two queries which
yield identical results, though using different approaches.
test=> SELECT customer.name, statename.name
test-> FROM customer, statename
test-> WHERE customer.state = statename.code
test-> ORDER BY customer.name;
name | name
--------------------------------+--------------------------------
Fleer Gearworks, Inc. | Alabama
Mark Middleton | Indiana
Mike Nichols | Florida
(3 rows)
test=> SELECT customer.name, getstatename(customer.state)
test-> FROM customer
test-> ORDER BY customer.name;
name | getstatename
--------------------------------+--------------------------------
Fleer Gearworks, Inc. | Alabama
Mark Middleton | Indiana
Mike Nichols | Florida
(3 rows)
The first query joins the customer and statename tables. The second query does a SELECT on customer; for each row, getstatename() is then called to find the customer's state name. The two queries yield the same result only if each customer row joins to exactly one statename row. If any customer rows did not join to a statename row or joined to many statename rows, the results would be different. Also, because the second query executes the SQL function for every row in customer, it works more slowly than the first query.