PostgreSQL

Скачать в pdf «PostgreSQL»

Ю


CO


о


15.15 |


О


о


СО


21.15


CO


CO


Ю


sticker |


О


ОЭ


-C*


LO


о


о


0.89 |


3.00 |


3.89


639 |


bulb |


CO


Ю


CO


о


го


го


3.90 |


5.00 |


8.90

(3 rows)


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)


Figure 18.5: SQL getstatename function


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
18.5 shows an SQL function that internally accesses the statename table. It looks up the proper state name for the state code supplied to the function.


Figure 18.6 shows two queries which yield identical results, though using different approaches. 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.

Скачать в pdf «PostgreSQL»