Creating Custom Functions

I l @ ve RuBoard

PostgreSQL includes a number of predefined functions to help in data manipulation (see Chapter 4, "PostgreSQL Functions," for a full listing). Many of the ones included are general-purpose functions that aid in converting, formatting, or aggregating data.

There are many cases in which the system would benefit from the existence of user-defined functions. Functions are particularly useful when the same information needs to be accessed repeatedly. In these cases, it is possible to create a user -defined function that is stored within the server.

Typically, when a function is created, a query plan is precompiled and stored as ready to be executed. This benefits system speed tremendously because the client application only needs to request that the function be executed instead of having to supply the SQL code and wait for it to be parsed, executed, and returned.

The benefits of using custom-created functions are not only limited to speed considerations. In many instances, the standard SQL language does not provide sufficient control to perform the desired action. For instance, if conditional branching, loop iteration, or complex variable substitution is needed, creating custom functions might be the only way to accomplish the task at hand.

As mentioned in Chapter 11, "Server-Side Programming," PostgreSQL includes a number of procedural languages that can be used to write custom functions. Although each has its own strengths, on balance, PL/pgSQL is probably the most accessible. PL/pgSQL allows standard SQL commands to be included, along with more advanced control structures like loops , if-then-else, and variable substitution.

Example Uses

In this section, you will examine instances of when creating custom functions would be useful.

Code Reuse

As previously mentioned, one of the situations in which custom functions are desirable is where you want to avoid the duplication of work.

In this example, we look at a function named homestate , which accepts an employee id value and returns that employee's home state.

 CREATE FUNCTION homestate(int) RETURNS text  AS '       DECLARE            empid ALIAS FOR ;       BEGIN            SELECT state FROM payroll WHERE                 employee_id=empid;            IF FOUND THEN                 RETURN state;            ELSE                 RETURN "N/A";            END IF;       END  ' LANGUAGE 'plpgsql'; 

Upon creation of the preceding function, PostgreSQL preformulates a query plan on the payroll table and awaits execution.

A carefully designed database will include a number of these prefabricated queries. They offer the following benefits to the system as a whole:

• Speed and efficiency. The preformed query plan already exists in the database engine and awaits execution.

• Format control of returned data. In the preceding example, when an employee's home state is not found, they will automatically be presented with an "N/A" value. This is provided directly from the custom function, with no intervening steps needed on the client end.

• Abstraction. In the preceding example, the client application doesn't directly have any knowledge of the underlying database schema. As a result, the basic table structures could be changed considerably, and as long as the input and output interfaces of this function continued to work in the same way, the client application would not need any modification.

• Indirect benefits. It could be argued that the mere inclusion of such a predefined query function improves the database and application architecture. That is to say, it forces the programmers/DBA to think about what types of data will be most accessed. Accordingly, this can lead to other insights that improve the efficiency of the system as a whole.

Combining Functions

By combining multiple functions, it is possible to build a more flexible, yet consistent, database.

In this example, there is a specific user interface (UI) feature that you are trying to create. It comes to the developer's attention that when users are entering information into the system, they want to be able to enter either the employee name or the employee ID into the dialog box.

This task is made easier because of the fact that you can assume that all employee IDs will be strictly composed of numbers , whereas employee names will consist entirely of letters .

Rather than having to re-create this feature for each instance of its use, it is decided to create a general case function that simply accepts either input (ID or name) and returns the employee ID.

Furthermore, it is decided that only the employee ID is to be stored in tables that link against the payroll table. This helps with the concept known as data normalization, the idea of which is to have consistent representation and nonredundant data stored across the database.

Therefore, the following function can be developed that will accept either format and return the employee ID. (The full potential of this function will not be seen until later.)

 CREATE FUNCTION getempid(varchar) RETURNS int  AS '       DECLARE            empval ALIAS FOR ;       BEGIN            /*Determine if empval is name                 if so, return the emp_id,                 otherwise, return back the emp_id            */            IF empval ~ '[a-zA-Z]' THEN                 SELECT employee_id FROM payroll WHERE                      last_name=empval;                 RETURN last_name            ELSE                 RETURN empval;            END IF;       END  ' LANGUAGE 'plpgsql'; 

At first glance, this function doesn't appear to be that useful. It simply determines whether the variable passed is a digit or alphabetical, and it returns the employee ID for that person. Moreover, it seems that if this function is already passed the employee ID, it simply returns that value directly back. On the surface, this might seem like a waste. However, when combined with other functions, the true potential for such a function can be seen.

For instance, by combining the first function, homestate , with this latest function, you can enable it to accept either the last name or the employee ID. In this case, you use your latest function as a wrapper to ensure a flexible range of input values. The clientside code would appear as follows :

 SELECT homestate(getempid('Stinson')); 

Or

 SELECT homestate(getempid(592915)); 

Or, finally

 SELECT homestate(getempid(strInputValue)); 

By combining the two functions, this allows a more flexible range of accepted input data, while still storing data in a consistent format on the back end. Moreover, if the developers one day realize that they want to allow users to input the Social Security number as well, it will only require a modification of the getempid function.

Stored Procedures

In reality, stored procedures and functions are exactly the same thing. Namely, they are a set of code statements that are created with a CREATE FUNCTION command. The difference is more conceptual than concrete.

In general, functions accept an input value, perform some lookup or manipulation of it, and return an output value.A classic example of a function can be seen in the upper function. This function accepts a character string, converts it to uppercase, and returns the resultant string. For instance:

 > select upper('abcdefg');  > ABCDEFG 

Stored procedures, however, do more than just accept a value and provide return data. Generally, they perform some basic procedure or alteration to database tables. For instance, consider the following example.

In this example, we want an easy way to make adjustments/ inserts to the employee table. Specifically, the users want an easy way to assign new or existing employees to a new job description. The specifications for such a function are as follows:

• If an employee doesn't exist, add the person and assign him or her to the specified job.

• If an employee already exists, change his or her job description to the new one.

Given these specs , a sample stored procedure that accomplishes this might appear as the following:

 CREATE FUNCTION assignjob(int, varchar) RETURNS int  AS '       DECLARE            empid ALIAS FOR ;            jobdesc ALIAS FOR ;            retval INTEGER:= 0;            emprec RECORD;       BEGIN            /*Determine if employee exist in table*/            SELECT INTO emprec WHERE employee_id=empid;            IF FOUND THEN                 /*Emp exist, modify his job description*/                 UPDATE employee SET job_description=jondesc                      WHERE employee_id=empid;                 retval := 1;            ELSE                 /*Emp doesn't exist, add him*/                 INSERT INTO employee VALUES (empid,jobdesc);                 retval := 1;            END IF;            RETURN retval;       END  ' LANGUAGE 'plpgsql'; 

Although the preceding example is still considered a function, it actually performs modifications to database tables instead of just calculating return values. For this reason, examples like the preceding are referred to as stored procedures.

This might be seen as just a difference of semantics. However, it underlines a conceptual difference between the two approaches.

Stored procedures are very useful in automating table manipulations that must occur regularly. For instance, a good use might be to perform a task such as voiding a payroll check. Typically, such an operation requires modifying many tables in a standard account system setup. Although it could be coded directly into the client application, that might make for a more rigid application in the end.

For instance, with the current system, modifications might need to be made to the payroll , employee , AP , and GL tables to fully void an incorrectly printed check. There would be no problem, per se, with coding this procedure directly from the client machine. If in the future, however, there is a new table ” JobCost ”that needs to be updated, this could be a needlessly complex change to make. It could require changing the code in dozens or hundreds of client applications.

A better approach would have been to create the task of voiding a check as a stored procedure (that is, function) within the database back end. The benefit of this setup is that the clients simply call the voidcheck function and are oblivious to the actual steps the server is taking to complete their request. On the server side, it is relatively minor to update the function to affect another table; therefore, the entire system becomes much more flexible.

I l @ ve RuBoard