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.