Mastering PostgreSQL 12 - Third Edition

Writing simple PL/Python code

In this section, you will learn how to write simple Python procedures. The example that we'll discuss here is quite simple: if you are visiting a client by car in Austria, you can deduct 42 Euro cents per kilometer in expenses, in order to reduce your income tax. So, what the function does is take the number of kilometers, and return the amount of money that we can deduct from our tax bill. Here is how it works:

CREATE OR REPLACE FUNCTION calculate_deduction(km float)  
  RETURNS numeric AS 
$$ 
if   km <= 0: 
  elog(ERROR, 'invalid number of kilometers') 
else: 
   return km * 0.42 $$ LANGUAGE 'plpythonu'; 

The function ensures that only positive values are accepted. Finally, the result is calculated and returned. As you can ...