How to write PL/pgSQL Function

pl/pgsql function inline_code_block line and pl/pgsql function tutorial and pl/pgsql function line at sql statement and plpgsql function parameters
Dr.MohitBansal Profile Pic
Published Date:26-10-2017
Your Website URL(Optional)
Your First PL/pgSQL Function A function is the basic building block for extending PostgreSQL. A function accepts input in the form of parameters, and can create output in the form of output parameters or return values. Many functions are provided by PostgreSQL itself such as the common mathematical functions, for example, square root and absolute value. For a comprehensive list of what is already available, go to The functions that you create have all of the same privileges and power that the built-in functions possess. The developers of PostgreSQL use the same libraries to extend the database that you use as a developer to write your business logic. This means that you have the tools available to be a first class citizen of the PostgreSQL development community. In fact, there are no second-class seats on this bus. A function accepts parameters that can be of any data type available in PostgreSQL and it returns results to the caller using any of the same types. What you do within the function is entirely up to you. You have been enabled to do anything that PostgreSQL is capable of doing. You are herewith also warned that you are capable of doing anything that PostgreSQL is capable of doing. The training wheels are off. In this chapter, you will learn: • The basic building blocks of a PostgreSQL function • Passing parameters into a function • Basic control structures inside of a function • Returning results out of a functionYour First PL/pgSQL Function Why PL/pgSQL? PL/pgSQL is a powerful SQL scripting language heavily influenced by PL/SQL, the stored procedure language distributed with Oracle. It is included in the vast majority of PostgreSQL installations as a standard part of the product, so it usually requires no setup at all to begin. PL/pgSQL also has a dirty little secret. The PostgreSQL developers don't want you to know that it is a full-fledged SQL development language, capable of doing pretty much anything within the PostgreSQL database. Why is that a secret? For years, PostgreSQL did not claim to have stored procedures. PL/pgSQL functions were originally designed to return scalar values and were intended for simple mathematical tasks and trivial string manipulation. Over the years, PL/pgSQL grew a rich set of control structures and gained the ability to be used by triggers, operators, and indexes. In the end, the developers were grudgingly forced to admit that they had a complete stored procedure development system on their hands. Along the way, the goal of PL/pgSQL changed from simplistic scalar functions to providing access to all of the PostgreSQL system internals with full control structure. The full list of what is available in the current version is provided at Today, some of the benefits of using PL/pgSQL are: • It is easy to use • It is available by default on most deployments of PostgreSQL • It is optimized for performance of data intensive tasks In addition to PL/pgSQL, PostgreSQL also allows many other languages to be plugged in to the database, some of which we will cover in this book. You may also choose to write your functions in Perl, Python, PHP, bash, and a host of other languages, but they will likely need to be added to your instance of PostgreSQL. Structure of a PL/pgSQL function It doesn't take much to get a PL/pgSQL function working. Here is a very basic example: CREATE FUNCTION mid(varchar, integer, integer) RETURNS varchar AS BEGIN RETURN substring(1,2,3); 50 Chapter 3 END; LANGUAGE plpgsql; The previous function shows the minimal elements of a PL/pgSQL function. It creates an alias for thesubstring built-in function calledmid. This is a handy alias to have around for developers that come from Microsoft SQL Server or MySQL and are wondering what happened to themid function. It also illustrates the most basic parameter passing strategy. The parameters are not named and are accessed in the function by relative location from left to right. The basic elements are name, parameters, return type, body, and language. It could be argued that parameters are not mandatory for a function and neither is the return value. This might be useful for a procedure that operates on data without providing a response, but it would be prudent to return a value ofTRUE to indicate that the procedure succeeded. Accessing function arguments Function arguments can also be passed and accessed by name, instead of just by the ordinal order. By accessing the parameters by name, it makes the resulting function code a little more readable. The following is an example of a function that uses named parameters: CREATE FUNCTION mid(keyfield varchar, starting_point integer) RETURNS varchar AS BEGIN RETURN substring(keyfield,starting_point); END LANGUAGE plpgsql; The previous function also demonstrates overloading of themid function. Overloading is another feature of PostgreSQL functions, which allows for multiple procedures using the same name, but different parameters. In this case, we first declared themid function with three parameters, but in this example, overloading is used to implement an alternative form of themid function where there are only two parameters. When the third parameter is omitted, the result will be the string starting fromstarting_point and continuing to the end of the input string. SELECT mid('Kirk L. Roybal',9); 51 Your First PL/pgSQL Function The previous line of code yields the following result: Roybal In order to access the function parameters by name, PostgreSQL makes a few educated guesses depending on the statement. Consider for a moment the following function: CREATE OR REPLACE FUNCTION ambiguous(parameter varchar) RETURNS integer AS DECLARE retval integer; BEGIN INSERT INTO parameter (parameter) VALUES (parameter) RETURNING id INTO retval; RETURN retval; END language plpgsql; SELECT ambiguous ('parameter'); This is an example of positively atrocious programming that should never occur outside of an example of how not to write functions. However, PostgreSQL is intelligent enough to correctly deduce that the contents of thefunction parameter are only legal in theVALUES list. All other occurrences of "parameter" are actually physical PostgreSQL entities. We also introduced an optional section to the function. We declare a variable before theBEGIN statement. Variables that appear in this section are valid during the execution of the function. Also of note in this function is theRETURNING id INTO retval statement. This feature allows the developer to specify the identity field of the record, and return the value of that field after the record has been inserted. Our function then returns this value to the caller as an indicator that the function succeeded and a way to find the record that has been inserted. 52 Chapter 3 Conditional expressions Conditional expressions allow developers to control the action of the function based on a defined criteria. The following is an example of using a CASE statement to control how a string is treated based on its value. If the value is null, or contains a zero length string, it is treated the same as null. CREATE OR REPLACE FUNCTION format_us_full_name( prefix text, firstname text, mi text, lastname text, suffix text) RETURNS text AS DECLARE fname_mi text; fmi_lname text; prefix_fmil text; pfmil_suffix text; BEGIN fname_mi := CONCAT_WS(' ', CASE trim(firstname) WHEN '' THEN NULL ELSE firstname END, CASE trim(mi) 53 Your First PL/pgSQL Function WHEN '' THEN NULL ELSE mi END '.'); fmi_lname := CONCAT_WS(' ', CASE fname_mi WHEN '' THEN NULL ELSE fname_mi END, CASE trim(lastname) WHEN '' THEN NULL ELSE lastname END); prefix_fmil := CONCAT_WS('. ', CASE trim(prefix) WHEN '' THEN NULL ELSE prefix END, CASE fmi_lname WHEN '' 54 Chapter 3 THEN NULL ELSE fmi_lname END); pfmil_suffix := CONCAT_WS(', ', CASE prefix_fmil WHEN '' THEN NULL ELSE prefix_fmil END, CASE trim(suffix) WHEN '' THEN NULL ELSE suffix '.' END); RETURN pfmil_suffix; END; LANGUAGE plpgsql; The idea here is that when any element of a full name is missing, the surrounding punctuation and white space should also be missing. This function returns a well formatted full name of a person in the USA, with as much of the name filled in as possible. When running this function, you will see the following: postgres= select format_us_full_name('Mr', 'Martin', 'L', 'King', 'Jr'); format_us_full_name 55 Your First PL/pgSQL Function - Mr. Martin L. King, Jr. (1 row) postgres= select format_us_full_name('', 'Martin', 'L', 'King', 'Jr'); format_us_full_name - Martin L. King, Jr. (1 row) Another way to use conditional expressions is by using theIF/THEN/ELSE blocks. The following is the same function again written usingIF statements rather than CASE statements: CREATE OR REPLACE FUNCTION format_us_full_name( prefix text, firstname text, mi text, lastname text, suffix text) RETURNS text AS DECLARE fname_mi text; fmi_lname text; prefix_fmil text; pfmil_suffix text; 56 Chapter 3 BEGIN fname_mi := CONCAT_WS(' ', IF(trim(firstname) ='',NULL,firstname), IF(trim(mi) = '', NULL, mi '.') ); fmi_lname := CONCAT_WS(' ', IF(fname_mi = '',NULL, fname_mi), IF(trim(lastname) = '', NULL, lastname) ); prefix_fmil := CONCAT_WS('. ', IF(trim(prefix) = '', NULL, prefix), IF(fmi_lname = '', NULL, fmi_lname) ); pfmil_suffix := CONCAT_WS(', ', IF (prefix_fmil = '', NULL, prefix_fmil), IF (trim(suffix) = '', NULL, suffix '.') ); RETURN pfmil_suffix; END; LANGUAGE plpgsql; 57 Your First PL/pgSQL Function PostgreSQL PL/pgSQL provides several more syntactical variants of these conditional expressions. This introduction has focused on the most commonly used ones. For a more complete discussion of the topic, visithttp://www.postgresql. org/docs/current/static/functions-conditional.html. Loops with counters The PL/pgSQL language provides a simple way to loop through some elements. The following is a function that returns the nth Fibonacci sequence number: CREATE OR REPLACE FUNCTION fib(n integer) RETURNS decimal(1000,0) AS DECLARE counter integer := 0; DECLARE a decimal(1000,0) := 0; DECLARE b decimal(1000,0) := 1; BEGIN IF (n 1) THEN RETURN 0; END IF; LOOP EXIT WHEN counter = n; counter := counter + 1; SELECT b,a+b INTO a,b; END LOOP; 58 Chapter 3 RETURN a; END; LANGUAGE plpgsql; SELECT fib(4); The previous code results in3 as the output. The highest Fibonacci number we can calculate with this function is 4785. If a value of the parameter is larger than that, the result will not fit into the 1000 length decimal we declared to return. Just for the record, in the Fibonacci sequence each element in the sequence is the sum of the previous 2 elements. Thus, the first few elements of the sequence should be 0,1,1,2,3,5,8,13,21,34, and so on. There are a few PostgreSQL Fibonacci sequence functions out there on the interwebs, but they use the dreaded recursive method. In this case recursion is a Bad Thing™. In this function, we also introduced default values to the variables in the declarations section. When the function is invoked, the variables will be initially set to these values. Also take a quick gander at the statementSELECT b,a+b INTO a,b. This statement makes two variable assignments at the same time. It avoids the use of a third variable while acting on botha andb. For some additional looping syntax, take a look at the PostgreSQL documentation page at structures.html. Looping through query results Before we embark on this journey through query result loops, I think it is fair to warn you that if you are using this method you are probably Doing It Wrong™. This is one of the most processor and memory intensive operations that PostgreSQL offers. There are exceedingly few reasons to iterate through a result set on the database server that offset this cost. I would encourage you to think very hard about how to implement the same idea using a function, values list in a query, temporary table, and permanent table, or precompute the values in any way possible to avoid this operation. So, do you still think you have an overwhelming reason to use this technique? Ok, read on. 59 Your First PL/pgSQL Function The following is the simple version: FOR row IN EXECUTE 'SELECT FROM job_queue q WHERE NOT processed LIMIT 100' LOOP CASE q.process_type WHEN 'archive_point_of_sale' THEN INSERT INTO hist_orders (...) SELECT ... FROM orders INNER JOIN order_detail ... INNER JOIN item ...; WHEN 'prune_archived_orders' THEN DELETE FROM order_detail WHERE order_id in (SELECT order_id FROM hist_orders); DELETE FROM orders WHERE order_id IN (SELECT order_id FROM hist_orders); ELSE RAISE NOTICE 'Unknown process_type: %', q.process_type; END; UPDATE job_queue SET processed = TRUE WHERE id =; END LOOP; 60 Chapter 3 The previous example shows a basic strategy pattern of processing messages in a job queue. Using this technique, rows in a table contain a list of jobs that need to be processed. We introduce theEXECUTE statement here, too. TheSELECT statement is a string value. UsingEXECUTE, we can dynamically build PL/pgSQL commands as strings and then invoke them as statements against the database. This technique comes in handy when we want to change the table name or other SQL keywords that make up our statement. These parts of the SQL statement cannot be stored in variables, and are not generally "changeable". WithEXECUTE, we can change any part of the statement we jolly well please. The following is an example that comes from the PostgreSQL documentation that shows dynamic commands running inside of a loop: CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS DECLARE mviews RECORD; BEGIN PERFORM cs_log('Refreshing materialized views...'); FOR mviews IN SELECT FROM cs_materialized_views ORDER BY sort_key LOOP Now "mviews" has one record from cs_materialized_views PERFORM cs_log('Refreshing materialized view ' quote_ident(mviews.mv_name) ' ...'); EXECUTE 'TRUNCATE TABLE ' quote_ident(mviews.mv_name); EXECUTE 'INSERT INTO ' quote_ident(mviews.mv_name) ' ' mviews.mv_query; 61 Your First PL/pgSQL Function END LOOP; PERFORM cs_log('Done refreshing materialized views.'); RETURN 1; END; LANGUAGE plpgsql; The previous looping example shows a more complex function that refreshes the data in some staging tables. These staging tables are designated "materialized views" because the data is actually physically transferred to the staging tables. This method is a common way to reduce query execution overhead for many presentations of the same data. In this case, the inefficiency of looping is trivial compared to the continued cost of repeated queries to the same data. PERFORM versus SELECT You may have noticed a statement in the previous example that we haven't covered yet.PERFORM is the command to use when you want to just discard the results of a statement. If the previous example were changed to: SELECT cs_log("Done refreshing materialized views"); The query engine would returnNo destination for result data. We could retrieve the results into variables and then proceed to ignore the variables, but that is just a little too sloppy for my taste. By using thePERFORM statement, we have indicated that ignoring the results was not accidental. We were happy with the fact that the log was appended to blindly, and if it wasn't, oh well, we didn't fail to continue the execution because of a log entry issue. 62 Chapter 3 Returning a record All of our function examples so far have featured a simple scalar value in theRETURN clause. For more complex return types, we have several choices. One option is to return a set of records conforming to a table den fi ition. For the sake of this example, we will assume that you are in the middle of a big software development upgrade procedure that uses a name/value pair table structure to store settings. You have been asked to change the table structure from the key and value columns to a series of columns where the column name is now the name of the key. By the way, you also need to preserve the settings for every version of the software you have ever deployed. Looking at the existingCREATE TABLE statement for the table you have to work with, we find: CREATE TABLE application_settings_old ( version varchar(200), key varchar(200), value varchar(2000)); When you run aselect statement against the table, you find out that there are not very many settings, but there have been quite a few versions of them. So you make a new table that is a little more explicit. CREATE TABLE application_settings_new ( version varchar(200), full_name varchar(2000), description varchar(2000), print_certificate varchar(2000), show_advertisements varchar(2000), show_splash_screen varchar(2000)); Transforming the settings data into this new format can be accomplished with an insert statement and a function that conveniently returns our data to us in the new table format. Let's go ahead and define the function: CREATE OR REPLACE FUNCTION flatten_application_settings(app_version varchar(200)) RETURNS setof application_settings_new AS 63 Your First PL/pgSQL Function BEGIN Create a temporary table to hold a single row of data IF EXISTS (SELECT relname FROM pg_class WHERE relname='tmp_settings') THEN TRUNCATE TABLE tmp_settings; ELSE CREATE TEMP TABLE tmp_settings (LIKE application_settings_new); END IF; the row will contain all of the data for this application version INSERT INTO tmp_settings (version) VALUES (app_version); add the details to the record for this application version UPDATE tmp_settings SET full_name = (SELECT value FROM application_settings_old WHERE version = app_version AND key='full_name'), description = (SELECT value FROM application_settings_old 64 Chapter 3 WHERE version = app_version AND key='description'), print_certificate = (SELECT value FROM application_settings_old WHERE version = app_version AND key='print_certificate'), show_advertisements = (SELECT value FROM application_settings_old WHERE version = app_version AND key='show_advertisements'), show_splash_screen = (SELECT value FROM application_settings_old WHERE version = app_version AND key='show_splash_screen'); hand back the results to the caller RETURN QUERY SELECT FROM tmp_settings; END; LANGUAGE plpgsql; The previous function returns a single row of data to the calling query. The row contains all of the settings that were previously defined as key/value pairs, but now are explicitly defined fields. The function and the final table could also be enhanced to transform the data types of the settings to something more explicit. But hey, I'm just a lowly book author, not a "real" developer, so I'll leave that one up to you. 65 Your First PL/pgSQL Function We then proceed to use the function to do the transformation: INSERT INTO application_settings_new SELECT ( flatten_application_settings(version)). FROM ( SELECT version FROM application_settings_old GROUP BY version) And violá The data is now available in tabular form in the new table structure. Acting on function results The previous example showed one way to retrieve and further process function results. The following are a few more useful ways to call a function: SELECT fib(55); SELECT (flatten_application_settings('9.08.97')). SELECT FROM flatten_application_settings('9.08.97'); Any of the previous methods will create a legal field list in PostgreSQL, which in turn can be used in any way that fields in a simple SELECT statement on a table are used. The example from the previous section used the results of theflatten_ application_settings() function, a source of data for anINSERT statement. The following is an example of how to use the same function as a data source forUPDATE: UPDATE application_settings_new SET full_name = flat.full_name, description = flat.description, print_certificate = flat.print_certificate, show_advertisements = flat.show_advertisements, show_splash_screen = flat.show_splash_screen FROM flatten_application_settings('9.08.97') flat; 66 Chapter 3 Using the application version as a key, we can update the records in the new table. Isn't that a really handy way to keep up with changes to the application settings, while both the old and new applications are still active? I'll take any compliments in the form of cash (or beer), please. Summary Writing functions in PostgreSQL is an extremely powerful tool. PostgreSQL functions provide the ability to add functionality to the database core to increase performance, security, and maintainability. They can be written in just about any language that is available to the open source community, and several that are proprietary. If the language that you would like to write them in is not available, it can be made available quickly and easily through a very robust and complete compatibility layer. 67