A PostgreSQL function is used to provide a distinct service, and is often composed of a set of declarations, expressions, and statements. PostgreSQL has very rich built-in functions for almost all the existing data types. In this chapter, we will focus on user-defined functions. However, details about the syntax and function parameters will be covered in the following chapters.
PostgreSQL supports out-of-the-box user-defined functions to be written in C, SQL and PL/pgSQL. There are also three other procedural languages that come with the standard PostgreSQL distribution: PL/Tcl, PL/Python, and PL/Perl. However, one needs to create the language in order to use them via the CREATE EXTENSION PostgreSQL command or via the createlang utility tool.
The simplest way to create a language and make it accessible to all the databases is to create it in template1, directly after the PostgreSQL cluster installation. Note that one does not need to perform this step for C, SQL, and PL/pgSQL.
For beginners, the most convenient languages to use are SQL and PL/pgSQL since they are supported directly. Moreover, they are highly portable, and do not need special care during the upgrading of the PostgreSQL cluster. Creating functions in the C language is not as easy as creating it in SQL or PL/pgSQL, but since C language is a general programing language, one could use it to create very complex functions to handle complex data types such as images.
In the following example, we will create a factorial function in the C language; this can be used as a template for creating more complex functions. One can create a PostgreSQL C function in four steps, as follows:
- Install the PostgreSQL server development library.
- Define your function in C, create a make file, and compile it as a shared library (
.so). In order to do that, one needs to read the PostgreSQL documentation provided at http://www.postgresql.org/docs/9.4/static/xfunc-c.html. - Specify the location of the shared library that contains your function. The easiest way to do this is to provide the library's absolute path when creating the function, or by copying the created function-shared library to the PostgreSQL library directory.
- Create the function in your database using the
CREATE FUNCTIONcommand.
To install the PostgreSQL development library, one can use the apt tool, as follows:
sudo apt-get install postgresql-server-dev-9.4
In C language development, the make tools are often used to compile the C code. The following is a simple
make file to compile the factorial function:
MODULES = fact PG_CONFIG = pg_config PGXS = $(shell $(PG_CONFIG) --pgxs) INCLUDEDIR = $(shell $(PG_CONFIG) --includedir-server) include $(PGXS) fact.so: fact.o cc -shared -o fact.so fact.o fact.o: fact.c cc -o fact.o -c fact.c $(CFLAGS) -I$(INCLUDEDIR)
The source code of the factorial fact for the abbreviation C function is given as follows:
#include "postgres.h" #include "fmgr.h" #ifdef PG_MODULE_MAGIC PG_MODULE_MAGIC; #endif Datum fact(PG_FUNCTION_ARGS); PG_FUNCTION_INFO_V1(fact); Datum fact(PG_FUNCTION_ARGS) { int32 fact = PG_GETARG_INT32(0); int32 count = 1, result = 1; for (count = 1; count <= fact; count++) result = result * count; PG_RETURN_INT32(result); }
The last step is to compile the code, copy the library to the PostgreSQL libraries location, and create the function:
make -f makefile cp fact.so $(pg_config --pkglibdir)/ psql -d template1 -c "CREATE FUNCTION fact(INTEGER) RETURNS INTEGER AS 'fact', 'fact' LANGUAGE C STRICT;" psql -d template1 -c "SELECT fact(5);" fact ------ 120 (1 row)
Writing C functions is quite complicated as compared to the SQL and PL/pgSQL functions. They might even cause some complications in upgrading the database if they are not well maintained.
Creating SQL functions is quite easy. The following is a function that determines if a view is updatable or not:
CREATE OR REPLACE FUNCTION is_updatable_view (text) RETURNS BOOLEAN AS $$ SELECT is_insertable_into='YES' FROM information_schema.tables WHERE table_type = 'VIEW' AND table_name = $1 $$ LANGUAGE SQL;
The body of the SQL function can be composed of several SQL statements; the result of the last SQL statement determines the function return type. An SQL PostgreSQL function cannot be used for constructing dynamic SQL statements, since the function argument can only be used to substitute data values but not identifiers. The following snippet is not valid in an SQL function:
CREATE FUNCTION ... --$1 is used to substitute table name SELECT * FROM $1;
The PL/pgSQL language is a full-fledged and preferable choice for usage on a daily basis. It can contain a variable declaration, conditional and looping construct, exception trapping, and so on.
The following function returns the factorial of an integer:
CREATE OR REPLACE FUNCTION fact(fact INT) RETURNS INT AS $$ DECLARE count INT = 1; result INT = 1; BEGIN FOR count IN 1..fact LOOP result = result* count; END LOOP; RETURN result; END; $$ LANGUAGE plpgsql;
PostgreSQL can be used in several scenarios. For example, some developers use functions as an abstract interface with higher programming languages to hide the data model. Additionally, functions can have several other usages such as:
- Performing complex logic that is difficult to perform with SQL
- In Dynamic SQL, a function argument can be used to pass table and views' names via the
EXECUTEstatement - Performing actions before or after the execution of an SQL statement via the trigger system
- Performing exception handling and additional logging via the
EXCEPTIONblocks andRAISEstatement respectively - Cleaning the SQL code by reusing the common code, and bundling the SQL codes in modules
When using PostgreSQL functions, one needs to be careful not to end with dangling functions, since the dependency between functions in not well maintained in the PostgreSQL system catalogue. The following example shows how one can end up with a dangling function:
CREATE OR REPLACE FUNCTION test_dep (INT) RETURNS INT AS $$ BEGIN RETURN $1; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION test_dep_2(INT) RETURNS INT AS $$ BEGIN RETURN test_dep($1); END; $$ LANGUAGE plpgsql; DROP FUNCTION test_dep(int); SELECT test_dep_2 (5); ERROR: function test_dep(integer) does not exist LINE 1: SELECT test_dep($1) ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT test_dep($1) CONTEXT: PL/pgSQL function test_dep_2(integer) line 3 at RETURN
In the preceding example, two functions were created with one dependent on the other. The test_dep() function was dropped leaving the test_depend_2() as a dangling function.
When creating a function, it is marked as volatile by default if the volatility classification is not specified. If the created function is not volatile, it is important to mark it as stable or immutable, because this will help the optimizer to generate the optimal exaction plans. PostgreSQL functions can have one of the following three volatility classifications:
- Volatile: The volatile function can return a different result on successive calls even if the function argument did not change, or it can change the data in the database. The
random()function is a volatile function. - Stable and immutable: These functions cannot modify the database, and are guaranteed to return the same result for the same argument. The stable function provides this guarantee within statement scope, while the immutable function provides this guarantee globally, without any scope.
For example, the random() function is volatile, since it will give a different result for each call. The function round() is immutable because it will always give the same result for the same argument. The function current time is stable, since it will always give the same result within the statement or transaction, as shown next:
car_portal=# SELECT current_time; timetz ---------------- 15:54:50.32-08 (1 row) car_portal=# BEGIN; BEGIN car_portal=# SELECT current_time; timetz ----------------- 15:54:55.464-08 (1 row) car_portal=# SELECT 'some time has passed'; ?column? ---------------------- some time has passed (1 row) car_portal=# SELECT current_time; timetz ----------------- 15:54:55.464-08 (1 row)
PostgreSQL provides the DO statement, which can be used to execute anonymous code blocks. The DO statement reduces the need for creating shell scripts for administration purposes. However, one should note that all PostgreSQL functions are transactional, so if one would like to create indexes on partitioned tables, for example, shell scripting is a better alternative.
In the web_car portal schema, let us assume that we would like to have another user who can perform only select statements. This can be done by executing the following code block:
car_portal=# CREATE user select_only; CREATE ROLE car_portal=# DO $$DECLARE r record; BEGIN FOR r IN SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema = 'car_portal_app' LOOP EXECUTE 'GRANT SELECT ON ' || quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) || ' TO select_only'; END LOOP; END$$; DO car_portal=# z account Access privileges Schema | Name | Type | Access privileges | Column access privileges ----------------+---------+-------+---------------------------------------+-------------------------- car_portal_app | account | table | car_portal_app=arwdDxt/car_portal_app+| | | | select_only=r/car_portal_app | (1 row)