Using Functions in PLpgSQL
Using Functions in PL/pgSQLFunctions are the heart of most programming languages. In PostgreSQL, functions are stored directly in the database and are called by the backend. When you insert a PL/pgSQL function into the database, the source code is converted into bytecode that can be executed efficiently by the PL/pgSQL bytecode interpreter. Each function is assigned an object ID. Function OverloadingA very powerful feature of PL/pgSQL is function overloading, which means that a certain name for a function can be used to represent various versions of a function. Let's assume a function called calc_middle() that calculates the average of two integer values. Because it also might be useful to have a function that calculates the average of two dates, the programmer might use the same name for the two functions: calc_middle(int4, int4) for calculating the average of two integer values, and calc_middle(timestamp, timestamp) for calculating the average of two timestamps. Functions that have the same name need not have the same number of parameters passed to it. You can also create a function named calc_middle(int4, int4, int4) , which is used to calculate the average of three integer values. SELECT Statements and PL/pgSQL FunctionsImagine a table containing the name of an employee, the number of the room the employee works in, and the salary of the employee: CREATE TABLE employees(id serial, name varchar(50), room int4, salary int4); INSERT INTO employees (name, room, salary) VALUES ('Paul', 1, 3000); INSERT INTO employees (name, room, salary) VALUES ('Josef', 1, 2945); INSERT INTO employees (name, room, salary) VALUES ('Linda', 2, 3276); INSERT INTO employees (name, room, salary) VALUES ('Carla', 1, 1200); INSERT INTO employees (name, room, salary) VALUES ('Hillary', 2, 4210); INSERT INTO employees (name, room, salary) VALUES ('Alice', 3, 1982); INSERT INTO employees (name, room, salary) VALUES ('Hugo', 4, 1982); Our target is to write a function that checks whether a user is in the table and performs an INSERT or an UPDATE operation: CREATE FUNCTION insertupdate(text, int4) RETURNS bool AS ' DECLARE intext ALIAS FOR ; newsal ALIAS FOR ; checkit record; BEGIN SELECT INTO checkit * FROM employees WHERE name=intext; IF NOT FOUND THEN INSERT INTO employees(name, room, salary) VALUES(intext,''1'',newsal); RETURN ''t''; ELSE UPDATE employees SET salary=newsal, room=checkit.room WHERE name=intext; RETURN ''f''; END IF; RETURN ''t''; END; ' LANGUAGE 'plpgsql'; In the DECLARE section, a variable called checkit is defined as type record . In PostgreSQL, the datatype record is used to store entire rows. The SELECT statement assigns the entire row returned by it to checkit . If no rows match the SELECT statements, PL/pgSQL performs an INSERT statement and inserts the value of intext , 1 , and the value of newsal into the database. If a row is found, an UPDATE query is performed. Note If a SELECT statement returns more than one row, all rows but the first will silently be omitted by the database in case of a SELECT INTO recordname statement. Let's take a closer look at the INSERT statement. In PL/pgSQL, variables such as intext have to be used without using quotes; otherwise , the name of the variable would be interpreted as a string. Values, however, have to be used quoted (with single quotes). PL/pgSQL functions are passed to the server as one string with single quotes. Single quotes within that string have to be escaped. This can easily be done by writing '' instead of ' (two single quotes instead of one single quote). An example for escaping a character is RETURN ''t'' instead of RETURN 't' , as it would be in the psql shell. In the example database, the function insertupdate(text, int4) can be called with the name and the salary of the person that should be inserted or updated: yourdb=# SELECT insertupdate('Alf',700); insertupdate -------------- t (1 row) The database now contains a person named Alf who has a salary of 700 . This can easily be found out by using this command: SELECT * FROM employees WHERE name='Alf'; Here is the result: id name room salary ----+------+------+-------- 8 Alf 1 700 (1 row) Let's call the function insertupdate(text, int4) again: yourdb=# SELECT insertupdate('Alf',1250); insertupdate -------------- f (1 row) And the result: SELECT * FROM employees WHERE name='Alf'; id name room salary ----+------+------+-------- 8 Alf 1 1250 (1 row) An important issue can be found in the UPDATE query: The salary is set to its new value, and room is set to checkit.room . Setting room to checkit.room is redundant, because room already has the value of checkit.room . We included this into the statement to show you how a variable of type record can be accessed in PL/pgSQL. The syntax is simple: Just add a dot and the name of the column to the variable's name. Working with SELECT and LoopsSometimes SELECT statements return multiple rows. In case of SELECT INTO , everything but the first row is silently omitted by PL/pgSQL. In this section, you explore an example where a SELECT that returns multiple rows is processed using a simple FOR loop without losing data: CREATE FUNCTION countsel(text) RETURNS int4 AS ' DECLARE inchar ALIAS FOR ; colval record; tmp text; result int4; BEGIN result = 0; FOR colval IN SELECT name FROM employees LOOP tmp := substr(colval.name, 1, 1); IF tmp = inchar THEN result := result + 1; END IF; END LOOP; RETURN result; END; ' LANGUAGE 'plpgsql'; The function can be used to find out how many records start with a certain character in column employees.name . The parameter inchar is used to pass the letter to look for to the function. Then a variable called colval is defined as record . In the FOR loop, all records returned by the SELECT statement are assigned to colval , one each time the loop is processed. Exception HandlingPostgreSQL has an exception handling model that is far from perfect. It is extremely difficult to find out where the real error occurred and what to do about it. Exception handling is an extremely important matter in all programming languages. Sometimes functions can't go on calculating the result of a function call, and an error has to be displayed. The following example shows you how the sum of integer values reaching from a lower limit to a higher limit can be found by using Carl Friedrich Gauss's formula for finding the sum of an arithmetic progression. Note The sum of integer values from 1 to n can easily be found with the formula (n+1)*n/2. CREATE FUNCTION calcsum(int4, int4) RETURNS int4 AS ' DECLARE lower ALIAS FOR ; higher ALIAS FOR ; lowres int4; lowtmp int4; highres int4; result int4; BEGIN IF (lower < 1) OR (higher < 1) THEN RAISE EXCEPTION ''both param. have to be > 0''; ELSE IF (lower <= higher) THEN lowtmp := lower - 1; lowres := (lowtmp+1)*lowtmp/2; highres := (higher+1)*higher/2; result := highres-lowres; ELSE RAISE EXCEPTION ''The first value (%) has to be higher than the second value (%)'', higher, lower; END IF; END IF; RETURN result; END; ' LANGUAGE 'plpgsql'; If the lower value or the higher value is smaller than 1 , we want the function to raise an exception and to abort calculation. Displaying exceptions can easily be done with the RAISE EXCEPTION command. The error message the function will display has to be written between single quotes (two single quotes are necessary because the single quotes have to be escaped). We also want to display an error when the lower value is higher than the higher value passed to the function. In this case, we want our error message to contain the two values. The symbol % can be used to put variables into the error message ”in this example, the first % is substituted by higher , the second % by lower . Making Functions More Independent from DatatypesSometimes a datatype for a column is not suitable any more. Imagine a text field for storing names that is currently a char(20) field. Someone decides to enlarge the field to char(30) ”dumping the database, changing the attributes of the field, and reinsertings the data into the table. And what about our dozens of functions? In this case, it is useful to have functions that are as independent from datatypes as possible. PostgreSQL offers some powerful but easy methods for obtaining this goal. Let's take a look at the following example: CREATE FUNCTION checksal(text) RETURNS int4 AS ' DECLARE inname ALIAS FOR ; sal employees%ROWTYPE; myval employees.salary%TYPE; BEGIN SELECT INTO myval salary FROM employees WHERE name=inname; RETURN myval; END; ' LANGUAGE 'plpgsql'; Let's run the code: SELECT checksal('Paul'); The result contains one record: checksal ---------- 3000 (1 row) This function can be used to retrieve the salary of a person from the table employees . In the database we used before, the field salary is a 4-byte integer. But who would like to rewrite the function if salary becomes numeric? The solution of the problem lies in the capability of PostgreSQL of assigning a datatype of a certain field or a row to a variable. In this case, we assign the datatype of the column salary in table employees to myval . We have added an additional line in the DECLARE section to show you how the type of a row can be assigned to a variable. ROWTYPE and TYPE can sometimes be very tricky and should be used carefully . Imagine a function that simply divides one value by another. If both values and the result are integer values, everything will be okay; but what if the result is not an integer value? Problems like that can easily occur when changing datatypes (let's say for example, from numeric to integer). Remember that when you change datatypes, the result of a function can suddenly be wrong and the function won't display an error. The following example should point out the problem more clearly: mydb=# select 5/2; ?column? ---------- 2 (1 row) The result of 5 divided by 2 is 2.5 , but PostgreSQL silently casts to integer . Here is an even more interesting one: mydb=# select timestamp(5000000/2); timestamp ------------------------ 1970-01-29 23:26:40+01 (1 row) PostgreSQL adds 2,500,000 microseconds to January 1, 1970 (UNIX starting time).  |
