Server-Side Programming
with PL/pgSQL
The ability to write functions in PostgreSQL is an amazing feature. One can perform
any task within the scope of the database server. These tasks might be related
directly to data manipulation such as data aggregation and auditing, or can be used
to perform miscellaneous services such as statistics collection, monitoring, system
information acquisition, and job scheduling.
In this chapter, our focus is the PL/pgSQL language. PL/pgSQL can be considered
as the default PostgreSQL, which is a full-fledged procedural language. As
mentioned earlier in Chapter 4, PostgreSQL Advanced Building Blocks, PL/pgSQL is
installed by default in PostgreSQL.
Introduction
PL/pgSQL has been influenced by the PL/SQL language, which is the Oracle stored
procedural language. PL/pgSQL is a complete procedural language with rich control
structures and full integration with the PostgreSQL trigger, index, rule, user defined
data type, and operator objects.
There are several advantages of using PL/pgSQL; they are as follows:
• It is easy to use and learn
• It has very good support and documentation
• It has very flexible result data types, and it supports polymorphism
• It can return scalar values and sets using different return methods
[ 227 ]
Server-Side Programming with PL/pgSQL
SQL language and PL/pgSQL – a comparison
As shown in Chapter 4, PostgreSQL Advanced Building Blocks, one can write functions
in C, SQL, and PL/pgSQL. There are some pros and cons of each approach.
One can think of an SQL function as a wrapper around a parameterized SELECT
statement. SQL functions can be in-lined into the calling subquery leading to a
better performance. Also, since the SQL function execution plan is not cashed as
in PL/pgSQL, it often behaves better than PL/pgSQL. Moreover, caching in PL/
pgSQL can have some surprisingly bad side effects such as caching of sensitive time
stamp values, as shown in the documentation that can be found at [Link]
[Link]/docs/current/interactive/[Link].
Finally, with the introduction of CTE, recursive CTE, window functions, and
LATERAL JOINS, one can perform complex logic using only SQL.
If the function logic can be implemented in SQL, use an SQL function
instead of PL/PGSQL.
The PL/pgSQL function execution plan is cached; caching the plan can help in
reducing the execution time, but it can also hurt it in case the plan is not optimal for
the provided function parameters.
From a functionality point of view, PL/pgSQL is much more powerful than SQL
for writing functions. PL/pgSQL supports several features that the SQL functions
cannot support, including the following:
• It provides the ability to raise exceptions as well as to raise messages at
different levels such as notice and debug.
• It supports constructing of dynamic SQL using the EXECUTE command.
• It provides EXCEPTION handling.
• It has a complete set of assignment, control, and loop statements.
• It supports cursors.
• It is fully integrated with the PostgreSQL trigger system. SQL functions
cannot be used with triggers.
[ 228 ]
Chapter 7
PostgreSQL function parameters
In Chapter 4, PostgreSQL Advanced Building Blocks, we discussed the function
categories immutable, stable, and volatile. In this section, we will continue with
other function options. These options are not PL/pgSQL language-specific.
Function authorization-related parameters
The first parameters are related to security, and can have one of the following values:
• SECURITY DEFINER
• SECURITY INVOKER
The default value for this option is SECURITY INVOKER, which indicates that the
function will be executed with the privileges of the user who calls it. The SECURITY
DEFINER functions will be executed using the privileges of the user who created it.
For the SECURITY INVOKER functions, the user must have the permissions to execute
the CRUD operations in the function; otherwise, the function will raise an error. The
SECURITY INVOKER functions are very useful in defining triggers, or for promoting
the user to perform tasks only supported by the function.
To test these security parameters, let us create two dummy functions, and execute
them in different sessions, as follows:
psql -U postgres -h localhost -d car_portal
car_portal=# CREATE FUNCTION test_security_definer () RETURNS TEXT AS $$
SELECT 'current_user :'||current_user || ' session_user: ' || session_
user; $$ LANGUAGE SQL SECURITY DEFINER;
CREATE FUNCTION
car_portal=# CREATE FUNCTION test_security_invoker () RETURNS TEXT AS $$
SELECT 'current_user :'||current_user || ' session_user: ' || session_
user; $$ LANGUAGE SQL SECURITY INVOKER;
CREATE FUNCTION
car_portal=# SELECT test_security_definer();
test_security_definer
----------------------------------------------
current_user :postgres session_user: postgres
(1 row)
[ 229 ]
Server-Side Programming with PL/pgSQL
car_portal=# SELECT test_security_invoker();
test_security_invoker
----------------------------------------------
current_user :postgres session_user: postgres
(1 row)
car_portal=# \q
$ psql -U car_portal_app -h localhost -d car_portal
car_portal=> SELECT test_security_invoker();
test_security_invoker
----------------------------------------------------------
current_user :car_portal_app session_user: car_portal_app
(1 row)
car_portal=> SELECT test_security_definer();
test_security_definer
----------------------------------------------------
current_user :postgres session_user: car_portal_app
(1 row)
The two functions test_security_definer and test_security_invoker are
identical except for the security parameter. When the two functions are executed
by a postgres user, the result of the two functions is identical to current_user
:postgres session_user: postgres. This is simply because the one who created
the function and the one who called it is the same user.
When the user car_portal_app executes the two preceding functions, the result
of the test_security_definer function is current_user :postgres session_
user: car_portal_app. In this case, the session_user is car_portal_app, since it
has started the session using a psql client. However, the current_user who executes
the SELECT statement SELECT 'current_user :'||current_user || ' session_
user: ' || session_user; is postgres.
[ 230 ]