Procedural Language Overview

• PostgreSQL allows user-defined functions to be written in a

variety of procedural languages. The database server has no
Introduction to PL/pgSQL built-in knowledge about how to interpret the function’s source
text. Instead, the task is passed to a handler that knows the
details of that particular language.
• PostgreSQL currently supports several standard procedural
languages
• PL/pgSQL
• PL/Tcl
• PL/Perl
• PL/Python
• PL/Java
• And many more

What is PL/pgSQL How PL/pgSQL works

• PL/pgSQL is the procedural extension to SQL with features of

programming languages
• PL/pgSQL is like every other “loadable, procedural language.”
• Data Manipulation and Query statements of SQL are included
within procedural units of code • When a PL function is executed, the fmgr loads the language
handler and calls it.
• Allows using general programming tools with SQL, for example:
loops, conditions, functions, etc. • The language handler then interprets the contents of the
pg_proc entry for the function (proargtypes, prorettype, prosrc).
• This allows a lot more freedom than general SQL, and is
lighter-weight than calling from a client program
How PL/pgSQL works How PL/pgSQL works

• On the first call of a function in a session, the call handler will • The PL/pgSQL statement tree is very similar to a PostgreSQL
“compile” a function statement tree. execution tree.

• SQL queries in the function are just kept as a string at this point.
• The call handler then executes that statement tree.

• On the first execution of a statement node, that has an SQL

• What might look to you like an expression is actually a SELECT query in it, that query is prepared via SPI.
query:

my_variable := some_parameter * 100;

• The prepared plan is then executed for every invocation of that
statement in the current session.

PL/pgSQL Environment Kinds of PL/pgSQL Blocks

The basic unit in any PL/pgSQL code is a BLOCK. All PL/pgSQL code
is composed of a single block or blocks that occur either sequentially
or nested within another block. There are two kinds of blocks:
• Anonymous blocks (DO)
• Generally constructed dynamically and executed only once by the
user. It is sort of a complex SQL statement
• Named blocks (Functions and Stored Procedures)
• Have a name associated with them, are stored in the database,
and can be executed repeatably, and can take in parameters
Structure of Anonymous Block Comments

DO $$
[ <<label>> ] • There are two types of comments in PL/pgSQL
DECLARE • -- starts a comment that extends to the end of the line
/* Declare section (optional). */ • /* multi-line comments */
BEGIN
/* Executable section (required). */ • Commenting is necessary to tell people what is intended and
why it was done a specific way
EXCEPTION
/* Exception handling section (optional). */
• Err on the side of too much commenting
END [ label ]
$$;

Variables Handling Variables

• Use variables for • Variables declared in the declarations section preceding a block
• Temporary storage of data are initialized to their default values every time the block is
• Manipulation of stored values entered, not only once per function call
• Re-usability
• Ease of maintenance
• Variables in a declaration section can shadow variables of the
• Declared in the declarative section within a block same name in an outer block. If the outer block is named with a
label, its variables are still available by specifying them as
v_last_name VARCHAR(15);
<label>.<varname>
Declarations %TYPE

Syntax • Declare variable according to :

identifier [CONSTANT] datatype [NOT NULL] [:= | = | DEFAULT expr]; • A database column definition
• Another previously declared variable
Examples identifier table.column_name%TYPE;
DECLARE
v_birthday DATE; Example
v_age INT NOT NULL = 21;
DECLARE
v_name VARCHAR(15) := 'Homer';
v_email [Link]%TYPE;
v_magic CONSTANT NUMERIC := 42;
v_my_email v_email%TYPE := 'rds-postgres-extensions-request@[Link]';
v_valid BOOLEAN DEFAULT TRUE;

%ROWTYPE Records

• A record is a type of variable similar to ROWTYPE, but with no

predefined structure
• Declare a variable with the type of a ROW of a table
identifier table%ROWTYPE; • The actual structure of the record is created when the variable is
first assigned
Example
DECLARE
v_user users%ROWTYPE;
• A record is not a true data type, only a place holder

DECLARE
r record;
Variable Scope Qualify an Identifier
DO $$
DO $$ << mainblock >>
DECLARE DECLARE
quantity integer := 30; quantity integer := 30;
BEGIN BEGIN
RAISE NOTICE 'Quantity here is %', quantity; -- 30 RAISE NOTICE 'Quantity here is %', quantity; --30
quantity := 50; quantity := 50;
-- Create a subblock -- Create a subblock
DECLARE DECLARE
quantity integer := 80; quantity integer := 80;
BEGIN BEGIN
RAISE NOTICE 'Quantity here is %', quantity; -- 80 RAISE NOTICE 'Quantity here is %', [Link]; --50
END; RAISE NOTICE 'Quantity here is %', quantity; --80
RAISE NOTICE 'Quantity here is %', quantity; -- 50 END;
END RAISE NOTICE 'Quantity here is %', quantity; --50
$$; END
$$;

RAISE Assigning Values

• Use the assignment operator ( := or = )

• Reports messages DECLARE
v_last_name VARCHAR := 'Smith';
• Can be seen by the client if the appropriate level is used v_date DATE;
RAISE NOTICE 'Calling cs_create_job(%)', v_job_id; BEGIN
v_last_name := lower(v_last_name);
v_date := to_date('2000-01-01', 'YYYY-MM-DD');
SELECT in PL/pgSQL INSERT / UPDATE / DELETE

• Retrieve data from the database with a SELECT statement

• Queries must return only one row DECLARE
• INTO clause is required v_forum_name [Link]%TYPE := 'Hackers';
BEGIN
DECLARE INSERT INTO forums (name)
v_first_name users.first_name%TYPE; VALUES (v_forum_name);
v_last_name users.last_name%TYPE;
BEGIN UPDATE forums
SELECT first_name, last_name SET moderated = true
INTO v_first_name, v_last_name WHERE name = v_forum_name;
FROM users END
WHERE user_id = 1;
END

PERFORM Structure of Named Blocks

CREATE FUNCTION [ function_name ] ()

RETURNS [return_type] $$
• Evaluate an expression or query but discard the result [ <<label>> ]
DECLARE
/* Declare section (optional). */
• Frequently used when executing maintenance commands
BEGIN
BEGIN /* Executable section (required). */
PERFORM create_partition('moderation_log', '2016-06');
END EXCEPTION
/* Exception handling section (optional). */

END [ label ]
$$ LANGUAGE plpgsql;
Function Example Dollar Quoting

CREATE FUNCTION get_user_count() • The tag $$ denotes the start and end of a string
RETURNS integer
AS $$
• Optionally can have a non-empty tag as part of the quote
DECLARE
• $_$
v_count integer; • $abc$
BEGIN • Can be used to prevent unnecessary escape characters
SELECT count(*) throughout the string
INTO v_count $function$
FROM users; BEGIN
RETURN ($1 ~ $q$[\t\r\n\v\\]$q$);
RETURN v_count; END;
END $function$
$$ LANGUAGE plpgsql;

Function Parameters Default Parameters

• One or more parameters can be used
• Parameter names are optional, but highly recommended • Paramters can have a default value
CREATE FUNCTION get_user_name(varchar, p_last_name varchar)
• This essentially makes them optional parameters
RETURNS varchar AS $$ CREATE FUNCTION get_user_count(p_active boolean DEFAULT true)
DECLARE RETURNS integer AS $$
v_first_name varchar; DECLARE
v_name varchar; v_count integer;
BEGIN BEGIN
v_first_name := $1; SELECT count(*) INTO v_count
SELECT name INTO v_name FROM users FROM users
WHERE first_name = v_first_name AND last_name = p_last_name WHERE active = p_active;
LIMIT 1;
RETURN v_count;
RETURN v_name; END
END $$ LANGUAGE plpgsql;
$$ LANGUAGE plpgsql;
Assertions
• A convenient shorthand for inserting debugging checks
• Can be controlled by plpgsql.check_asserts variable
CREATE FUNCTION get_user_count(p_active boolean DEFAULT true)
RETURNS integer AS $$
DECLARE
PL/pgSQL Control Structures
v_count integer;
BEGIN
ASSERT p_active IS NOT NULL;

SELECT count(*) INTO v_count

FROM users
WHERE active = p_active;

RETURN v_count;
END
$$ LANGUAGE plpgsql;

Control the Flow IF Statements

IF-THEN
IF boolean-expression THEN
statements
• The logical flow of statements can be changed using conditional END IF;
IF statements and loop control structures
• Conditional Strucutres IF-THEN-ELSE
• Loop Structures
IF boolean-expression THEN
statements
ELSE
statements
END IF;
Nested IF Statements ELSIF Statements

• A sequence of statements based on multiple conditions

IF number = 0 THEN
IF boolean-expression THEN
result := 'zero';
IF boolean-expression THEN
ELSIF number > 0 THEN
statements
result := 'positive';
END IF;
ELSIF number < 0 THEN
ELSE
result := 'negative';
statements
ELSE
END IF;
-- the only other possibility is that number is null
result := 'NULL';
END IF;

CASE Statements Searched CASE Statements

• Used for complex conditionals • Each WHEN clause sequentially evaluated until a TRUE is
• Allows a variable to be tested for equality against a list of values evaluated
• Subsequent WHEN expressions are not evaluated
BEGIN
CASE status BEGIN
WHEN 'Pending' THEN RAISE NOTICE 'PENDING'; CASE
WHEN 'Accepted' THEN RAISE NOTICE 'ACCEPTED'; WHEN x BETWEEN 0 AND 10 THEN
WHEN 'Declined' THEN RAISE NOTICE 'DECLINED'; RAISE NOTICE 'Value is between zero and ten';
WHEN 'Blocked' THEN RAISE NOTICE 'BLOCKED'; WHEN x BETWEEN 11 AND 20 THEN
ELSE RAISE NOTICE 'UNKNOWN'; RAISE NOTICE 'Value is between eleven and twenty';
END CASE; END CASE;
END $$;
FOUND FOUND
• FOUND, which is of type boolean, starts out false within each DECLARE
PL/pgSQL function call v_first_name users.first_name%TYPE;
v_last_name users.last_name%TYPE;
• It is set by each of the following types of statements: BEGIN
SELECT first_name, last_name
• A SELECT INTO statement sets FOUND true if it returns a row, INTO v_first_name, v_last_name
false if no row is returned FROM users
• A PERFORM statement sets FOUND true if it produces (and WHERE user_id = 1;
discards) a row, false if no row is produced
• UPDATE, INSERT, and DELETE statements set FOUND true if at IF FOUND THEN
least one row is affected, false if no row is affected RAISE NOTICE 'User Found';
ELSE
• A FETCH statement sets FOUND true if it returns a row, false if no
RAISE NOTICE 'User Not Found';
row is returned.
• A FOR statement sets FOUND true if it iterates one or more times, END IF;
END
else false.

Loop Structures Unconstrained Loops

• Allows execution of its statements at least once, even if the

condition already met upon entering the loop
• Unconstrained Loop
LOOP
• WHILE Loop -- some computations
IF count > 0 THEN
EXIT; -- exit loop
• FOR Loop END IF;
END LOOP;

• FOREACH Loop LOOP

-- some computations
EXIT WHEN count > 0; -- same result as previous example
END LOOP;
CONTINUE WHILE Loops
CONTINUE [ label ] [ WHEN expression ];

• If no label is given, the next iteration of the innermost loop is WHILE condition LOOP
begun statement1..;
• If WHEN is specified, the next iteration of the loop is begun only END LOOP;

if expression is true. Otherwise, control passes to the statement • Repeats a sequence of statements until the controlling condition
after CONTINUE is no longer TRUE
• CONTINUE can be used with all types of loops; it is not limited to
use with unconstrained loops. • Condition is evaluated at the beginning of each iteration
WHILE NOT done LOOP
LOOP
-- some computations here
-- some computations
END LOOP;
EXIT WHEN count > 100;
CONTINUE WHEN count < 50;
-- some computations for count IN [50 .. 100]
END LOOP;

FOR Loops Looping Over Results

FOR <loop_counter> IN [REVERSE] <low bound>..<high bound> LOOP

-- some computations here
END LOOP; • For loops can directly use a query result
DECLARE
• Use a FOR loop to shortcut the test for the number of iterations. r record;
• Do not declare the counter; it is declared implicitly BEGIN
DO $$ FOR r IN SELECT email FROM users LOOP
BEGIN RAISE NOTICE 'Email: %', [Link];
FOR i IN 1..10 LOOP END LOOP;
RAISE NOTICE 'value: %', i; END
END LOOP;
END
$$;
Looping Over Results Looping Over Results

• Looping over dynamic SQL

• The last row is still accessible after exiting the loop • Re-planned each time it is executed
DECLARE DECLARE
r record; rec RECORD;
BEGIN sql text;
FOR r IN SELECT email FROM users LOOP BEGIN
RAISE NOTICE 'Email: %', [Link]; sql := 'SELECT email FROM users';
END LOOP; FOR rec IN EXECUTE sql LOOP
RAISE NOTICE 'Email: %', [Link]; RAISE NOTICE 'Email: %', [Link];
END END LOOP;
END

Looping Over Arrays Looping Over Arrays

• Uses the FOREACH statement • Use the SLICE syntax to iterate over multiple dimensions
DECLARE
DECLARE
users varchar[];
users varchar[] := ARRAY['Mickey', 'Donald', 'Minnie'];
v_dim varchar[];
v_user varchar;
BEGIN
BEGIN
users := ARRAY[ARRAY['Mickey', 'Donald'], ARRAY['Mouse', 'Duck']];
FOREACH v_user IN ARRAY users LOOP
FOREACH v_dim SLICE 1 IN ARRAY users LOOP
RAISE NOTICE 'User: %', v_user;
RAISE NOTICE 'Dimension: %', v_dim;
END LOOP;
END LOOP;
END
END
Nested Loops
• Nest loops to multiple levels
• Use labels to distinguish between blocks
• Exit the outer loop with the EXIT statement that references the
label
BEGIN Dynamic SQL
<<Outer_loop>>
LOOP
v_counter := v_counter + 1;
EXIT WHEN v_counter > 10; -- leaves both loops
<<Inner_loop>>
LOOP
EXIT Outer_loop WHEN total_done = 'YES';
-- leaves both loops
EXIT WHEN inner_done = 'YES';
-- leaves inner loop only
END LOOP Inner_loop;
END LOOP Outer_loop;
END

Dynamic SQL Dynamic SQL - CAUTION

• A programming methodology for generating and running SQL • There is no plan caching for commands executed via EXECUTE
statements at run time • The command is planned each time it is run

• Useful for: • Open to SQL injection attacks

• Ad-hoc query systems • All incoming parameters need to be validated
• DDL and database maitenance • Bind the parameters to the command instead of generating the
EXECUTE command-string [ INTO target ] [ USING expression [, ... ] ]; string
Execute Execute Into
CREATE FUNCTION get_connection_count(p_role varchar)
RETURNS integer
CREATE FUNCTION grant_select(p_table varchar, p_role varchar)
AS $$
RETURNS void AS
DECLARE
$$
v_count integer;
DECLARE
sql varchar;
sql varchar;
BEGIN
BEGIN
sql := 'SELECT count(*) FROM pg_stat_activity
sql := 'GRANT SELECT ON TABLE ' || p_table || ' TO ' || p_role;
WHERE usename = ''' || p_role || '''';
EXECUTE sql;
EXECUTE sql INTO v_count;
END
$$ LANGUAGE plpgsql;
RETURN v_count;
END
Note: Do not do this. Validate the parameters first. $$ LANGUAGE plpgsql;

Note: Do not do this. Validate the parameters first.

Execute Using

CREATE FUNCTION get_connection_count(p_role varchar)

RETURNS integer
AS $$
DECLARE
PL/pgSQL Cursors
v_count integer;
sql varchar;
BEGIN
sql := 'SELECT count(*) FROM pg_stat_activity
WHERE usename = $1';
EXECUTE sql INTO v_count USING p_role;

RETURN v_count;
END
$$ LANGUAGE plpgsql;
Cursors Cursor Flow

• Every SQL statement executed by PostgreSQL has an individual

cursor associated with it
• Implicit cursors: Declared for all DML and PL/pgSQL SELECT
statements
• Explicit cursors: Declared and named by the programmer

• Use CURSOR to individually process each row returned by a

multiple-row SELECT Statement

Declaring Cursors Opening Cursors

• A cursor must be declared as a variable

• Use the SCROLL keyword to move backwards through a cursor • The OPEN method to use is dependant on the way it was
declared
name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
DECLARE
OPEN cur2;
curs1 refcursor;
curs2 CURSOR FOR SELECT * FROM tenk1;
OPEN curs3(42);
curs3 CURSOR (key integer) FOR SELECT *
OPEN curs3 (key := 42);
FROM tenk1
WHERE unique1 = key;
Fetching Data Fetching Data
CREATE FUNCTION grant_select(p_role varchar)
RETURNS void AS $$
DECLARE
sql varchar;
r record;
• FETCH returns the next row tbl_cursor CURSOR FOR SELECT schemaname, relname
FROM pg_stat_user_tables;
FETCH curs2 INTO foo, bar, baz;
BEGIN
OPEN tbl_cursor;
• FETCH can also move around the cursor LOOP
FETCH tbl_cursor INTO r;
FETCH LAST FROM curs3 INTO x, y; EXIT WHEN NOT FOUND;
sql := 'GRANT SELECT ON TABLE ' || [Link] ||
'.' || [Link] || ' TO ' || p_role;
EXECUTE sql;
END LOOP;
CLOSE tbl_cursor;
END
$$ LANGUAGE plpgsql;

Returning Scalars
• Simplest return type
CREATE FUNCTION get_connection_count()
RETURNS integer AS $$
DECLARE
PL/pgSQL Returning Data v_count integer;
BEGIN
SELECT count(*) INTO v_count
FROM pg_stat_activity;

RETURN v_count;
END
$$ LANGUAGE plpgsql;

SELECT get_connection_count();
get_connection_count
----------------------
11
(1 row)
Returning Nothing Returning Sets
• Some functions do not need a return value
• This is usually a maintenance function of some sort such as
creating partitions or data purging • Functions can return a result set
• Starting in PostgreSQL 11, Stored Procedures can be used in
these cases • Use SETOF
• Return VOID • Use RETURN NEXT
CREATE FUNCTION purge_log() • RETURN NEXT does not actually return from the function
RETURNS void AS • Successive RETURN NEXT commands build a result set
$$
BEGIN
DELETE FROM moderation_log • A final RETURN exits the function
WHERE log_date < now() - '90 days'::interval;
END
$$ LANGUAGE plpgsql;

Returning Sets Returning Records

CREATE FUNCTION fibonacci(num integer)
RETURNS SETOF integer AS $$
• More complex structures can be returned
DECLARE CREATE FUNCTION get_oldest_session()
a int := 0; RETURNS record AS
b int := 1; $$
BEGIN DECLARE
IF (num <= 0) r record;
THEN RETURN; BEGIN
END IF; SELECT *
INTO r
RETURN NEXT a; FROM pg_stat_activity
LOOP WHERE usename = SESSION_USER
EXIT WHEN num <= 1; ORDER BY backend_start DESC
RETURN NEXT b; LIMIT 1;
num = num - 1;
SELECT b, a + b INTO a, b; RETURN r;
END LOOP; END
END; $$ LANGUAGE plpgsql;
$$ language plpgsql;
Returning Records Returning Records
• All tables and views automatically have corresponding type
• Using a generic record type requires the structure to be defined definitions so they can be used as return types
at run time CREATE FUNCTION get_oldest_session()
RETURNS pg_stat_activity AS $$
DECLARE
# SELECT * FROM get_oldest_session(); r record;
ERROR: a column definition list is required for functions ... BEGIN
LINE 1: SELECT * FROM get_oldest_session(); SELECT *
INTO r
FROM pg_stat_activity
SELECT * FROM get_oldest_session()
WHERE usename = SESSION_USER
AS (a oid, b name, c integer, d oid, e name, f text, g inet,
ORDER BY backend_start DESC
h text, i integer, j timestamptz, k timestamptz,
LIMIT 1;
l timestamptz, m timestamptz, n boolean, o text, p xid,
q xid, r text);
RETURN r;
END
$$ LANGUAGE plpgsql;

Returning Sets of Records Returning Sets of Records

• RETURN QUERY can be used to simplify the function

• Many times, a subset of the table data is needed
CREATE FUNCTION running_queries(p_rows int, p_len int DEFAULT 50)
• A view can be used to define the necessary structure RETURNS SETOF running_queries AS
$$
CREATE VIEW running_queries AS BEGIN
SELECT CURRENT_TIMESTAMP - query_start as runtime, pid, RETURN QUERY SELECT runtime, pid, usename, waiting,
usename, waiting, query substring(query,1,p_len) as query
FROM pg_stat_activity FROM running_queries
ORDER BY 1 DESC ORDER BY 1 DESC
LIMIT 10; LIMIT p_rows;
END
$$ LANGUAGE plpgsql;
OUT Parameters OUT Parameters
CREATE FUNCTION active_locks(OUT p_exclusive int, OUT p_share int) AS $$
DECLARE
r record;
BEGIN
p_exclusive := 0;
• Used to return structured information p_share := 0;
FOR r IN SELECT [Link]
FROM pg_locks l, pg_stat_activity a
• RETURNS is optional, but must be record if included WHERE [Link] = [Link]
AND [Link] = SESSION_USER
CREATE FUNCTION active_locks(OUT p_exclusive int, OUT p_share int) LOOP
IF [Link] = 'ExclusiveLock' THEN
p_exclusive := p_exclusive + 1;
ELSIF [Link] = 'ShareLock' THEN
p_share := p_share + 1;
END IF;
END LOOP;
END
$$ LANGUAGE plpgsql;

OUT Parameters Structured Record Sets

• Use OUT parameters and SETOF record
• TIP: Think in sets not loops when writing functions for better CREATE FUNCTION all_active_locks(OUT p_lock_mode varchar,
performance OUT p_count int)
• NOTE: Use “OR REPLACE” when updating functions RETURNS SETOF record AS $$
DECLARE
CREATE OR REPLACE FUNCTION active_locks(OUT p_exclusive int,
r record;
OUT p_share int)
BEGIN
AS $$
FOR r IN SELECT [Link], count(*) as k
BEGIN
FROM pg_locks l, pg_stat_activity a
SELECT sum(CASE [Link] WHEN 'ExclusiveLock' THEN 1 ELSE 0 END),
WHERE [Link] = [Link]
sum(CASE [Link] WHEN 'ShareLock' THEN 1 ELSE 0 END)
AND [Link] = SESSION_USER
INTO p_exclusive, p_share
GROUP BY 1
FROM pg_locks l, pg_stat_activity a
LOOP
WHERE [Link] = [Link]
p_lock_mode := [Link];
AND [Link] = SESSION_USER;
p_count := r.k;
RETURN NEXT;
END
END LOOP;
$$ LANGUAGE plpgsql;
RETURN;
...
Structured Record Sets Refcursors
• Can return a TABLE
CREATE FUNCTION all_active_locks()
RETURNS TABLE (p_lock_mode varchar, p_count int) AS $$
DECLARE
r record;
BEGIN
• A cursor can be returned for large result sets
FOR r IN SELECT [Link], count(*) as k
FROM pg_locks l, pg_stat_activity a
WHERE [Link] = [Link]
• The only way to return multiple result sets from a function
AND [Link] = SESSION_USER
GROUP BY 1 CREATE FUNCTION active_info(OUT p_queries refcursor,
LOOP OUT p_locks refcursor)
p_lock_mode := [Link];
p_count := r.k;
RETURN NEXT;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;

Refcursors
CREATE FUNCTION active_info(OUT p_queries refcursor,
OUT p_locks refcursor)
AS $$
BEGIN
OPEN p_queries FOR SELECT runtime, pid, usename, waiting,
Handling Meta Information and Exceptions
substring(query,1,50) as query
FROM running_queries
ORDER BY 1 DESC;

OPEN p_locks FOR SELECT [Link], count(*) as k

FROM pg_locks l, pg_stat_activity a
WHERE [Link] = [Link]
AND [Link] = SESSION_USER
GROUP BY 1;
END
$$ LANGUAGE plpgsql;
Meta Information Meta Information

CREATE OR REPLACE FUNCTION purge_log()

• Information about the last command run inside of a function $$
RETURNS void AS

DECLARE
• Several available values l_rows int;
• ROW_COUNT BEGIN
• RESULT_OID DELETE FROM moderation_log
• PG_CONTEXT WHERE log_date < now() - '90 days'::interval;

GET DIAGNOSTICS l_rows = ROW_COUNT;

GET DIAGNOSTICS variable { = | := } item [ , ... ]; RAISE NOTICE 'Deleted % rows from the log', l_rows;
END
$$ LANGUAGE plpgsql;

Exceptions Exceptions

• An exception is an identifier in PL/pgSQL that is raised during

execution • Use the WHEN block inside of the EXCEPTION block to catch
• It is raised when an error occurs or explicitly by the function specific cases
• It is either handled in the EXCEPTION block or propagated to
the calling environment • Can use the error name or error code in the EXCEPTION block
WHEN division_by_zero THEN ...
WHEN SQLSTATE '22012' THEN …
[DECLARE]
BEGIN • Use the special conditions OTHERS as a catch all
Exception/Error is Raised
EXCEPTION WHEN OTHERS THEN ...
Error is Trapped
END
Sample Error Codes Exceptions
CREATE OR REPLACE FUNCTION get_connection_count()
RETURNS integer AS $$
Code Name DECLARE
v_count integer;
22000 data_exception BEGIN
22012 division_by_zero SELECT count(*)

2200B escape_character_conflict INTO STRICT v_count

FROM pg_stat_activity;
22007 invalid_datetime_format
22023 invalid_parameter_value RETURN v_count;
2200M invalid_xml_document EXCEPTION
2200S invalid_xml_comment WHEN TOO_MANY_ROWS THEN
23P01 exclusion_violation RAISE NOTICE 'More than 1 row returned';
RETURN -1;
WHEN OTHERS THEN
RAISE NOTICE 'Unknown Error';
RETURN -1;
END
$$ LANGUAGE plpgsql;

Exception Information Exception Information

• SQLSTATE Returns the numeric value for the error code.

• SQLERRM Returns the message associated with the error

number.
• The details of an error are usually required when handling
DECLARE
v_count integer;
err_num integer; • Use GET STACKED DIAGNOSTICS to return the details
err_msg varchar;
BEGIN
GET STACKED DIAGNOSTICS variable { = | := } item [ , ... ];
...
EXCEPTION
WHEN OTHERS THEN
err_num := SQLSTATE;
err_msg := SUBSTR(SQLERRM,1,100);
RAISE NOTICE 'Trapped Error: %', err_msg;
RETURN -1;
END
Exception Information Propagating Exceptions

Diagnostic Item • Exceptions can be raised explicitly by the function

RETURNED_SQLSTATE CREATE OR REPLACE FUNCTION grant_select(p_role varchar)
COLUMN_NAME RETURNS void AS
CONSTRAINT_NAME $$

PG_DATATYPE_NAME DECLARE
sql varchar;
MESSAGE_TEXT r record;
TABLE_NAME tbl_cursor CURSOR FOR SELECT schemaname, relname
SCHEMA_NAME FROM pg_stat_user_tables;
PG_EXCEPTION_DETAIL BEGIN
PG_EXCEPTION_HINT IF NOT EXISTS (SELECT 1 FROM pg_roles
WHERE rolname = p_role) THEN
PG_EXCEPTION_CONTEXT RAISE EXCEPTION 'Invalid Role: %', p_role;
END IF;
...

Exceptions
• TIP: Use exceptions only when necessary, there is a large
performance impact
• Sub transactions are created to handle the exceptions
CREATE FUNCTION t1() CREATE FUNCTION t2() PL/pgSQL Triggers
RETURNS void AS $$ RETURNS void AS $$
DECLARE DECLARE
i integer; i integer;
BEGIN BEGIN
i := 1; i := 1;
END EXCEPTION
$$ LANGUAGE plpgsql; WHEN OTHERS THEN
RETURN;
END
$$ LANGUAGE plpgsql;

Avg Time: 0.0017ms Avg Time: 0.0032ms

Triggers Use Cases
• Table Partitioning before PostgreSQL 10

• Automatically generate derived column values

• Code that gets executed when an event happens in the database
• INSERT, UPDATE, DELETE • Enforce complex constraints

• Event Triggers fire on DDL • Enforce referential integrity across nodes in a distributed
• CREATE, DROP, ALTER database

• Provide transparent event logging

• Provide auditing
• Invalidate cache entries

Structure Trigger Function

• Unlike other databases, a trigger is broken into two pieces

• Trigger • A function with no parameters that returns TRIGGER
• Trigger Function
CREATE FUNCTION trg() RETURNS trigger AS $$
CREATE TRIGGER name
BEGIN
{ BEFORE | AFTER | INSTEAD OF }
RETURN NEW;
{ event [ OR ... ] }
END;
ON table_name
$$ LANGUAGE plpgsql;
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE PROCEDURE function_name ( arguments )
Trigger Events Timing

• Insert • Before
• The trigger is fired before the change is made to the table
• Trigger can modify NEW values
• Update • Trigger can suppress the change altogether

• Delete • After
• The trigger is fired after the change is made to the table
• Truncate • Trigger sees final result of row

Frequency Trigger Overhead

• A firing trigger adds overhead to the calling transaction

• For Each Row • The percentage overhead can be found with a simple pgbench
• The trigger is fired once each time a row is affected
test:
• For Each Statement INSERT INTO trigger_test (value) VALUES (‘hello’);
• The trigger is fired once each time a statement is executed
\set keys :scale
\setrandom key 1 :keys
UPDATE trigger_test SET value = 'HELLO' WHERE key = :key;
Trigger Overhead Trigger Overhead

CREATE FUNCTION empty_trigger()

pgbench -n -t 100000 RETURNS trigger AS $$
-f [Link] postgres BEGIN
RETURN NEW;
pgbench -n -s 100000 -t 10000 END;
-f [Link] postgres $$ LANGUAGE plpgsql;

Inserts: 4510 tps CREATE TRIGGER empty_trigger

Updates: 4349 tps BEFORE INSERT OR UPDATE ON trigger_test
FOR EACH ROW EXECUTE PROCEDURE empty_trigger();

Trigger Overhead Arguments

pgbench -n -t 100000 • NEW

-f [Link] postgres • Variable holding the new row for INSERT/UPDATE operations in
row-level triggers
pgbench -n -s 100000 -t 10000
-f [Link] postgres
• OLD
Inserts: 4296 tps (4.8% overhead)
• Variable holding the old row for UPDATE/DELETE operations in
Updates: 3988 tps (8.3% overhead) row-level triggers
NEW vs OLD NEW vs OLD

CREATE OR REPLACE FUNCTION audit_trigger()

RETURNS TRIGGER AS $$
BEGIN
CREATE TABLE audit ( INSERT INTO audit
event_time timestamp NOT NULL, VALUES (CURRENT_TIMESTAMP,
user_name varchar NOT NULL, CURRENT_USER,
old_row json, row_to_json(OLD),
new_row json row_to_json(NEW));
);
RETURN NEW;
END;
$$
LANGUAGE plpgsql;

Arguments TG_OP
• TG_OP
• A string of INSERT, UPDATE, DELETE, or TRUNCATE telling for
which operation the trigger was fired
CREATE TABLE audit (
• TG_NAME event_time timestamp NOT NULL,
• Variable that contains the name of the trigger actually fired user_name varchar NOT NULL,
operation varchar NOT NULL,
old_row json,
• TG_WHEN new_row json
• A string of BEFORE, AFTER, or INSTEAD OF, depending on the );
trigger’s definition

• TG_LEVEL
• A string of either ROW or STATEMENT depending on the trigger’s
definition
TG_OP Arguments
CREATE OR REPLACE FUNCTION audit_trigger() RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'DELETE') THEN
• TG_TABLE_NAME
INSERT INTO audit VALUES
• The name of the table that caused the trigger invocation.
(CURRENT_TIMESTAMP, CURRENT_USER,TG_OP, row_to_json(OLD), null);
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
• TG_RELNAME
INSERT INTO audit VALUES
• The name of the table that caused the trigger invocation
(CURRENT_TIMESTAMP, CURRENT_USER,TG_OP,
row_to_json(OLD), row_to_json(NEW));
RETURN NEW;
• TG_RELID
ELSIF (TG_OP = 'INSERT') THEN
• The object ID of the table that caused the trigger invocation
INSERT INTO audit VALUES
(CURRENT_TIMESTAMP, CURRENT_USER,TG_OP, null, row_to_json(NEW));
RETURN NEW;
• TG_TABLE_SCHEMA
END IF;
• The name of the schema of the table that caused the trigger
RETURN NULL; invocation
END;
$$ LANGUAGE plpgsql;

TG_TABLE_NAME TG_TABLE_NAME

CREATE OR REPLACE FUNCTION audit_trigger() RETURNS TRIGGER AS $$

BEGIN
CREATE TABLE audit ( IF (TG_OP = 'DELETE') THEN
event_time timestamp NOT NULL, INSERT INTO audit
user_name varchar NOT NULL, VALUES (CURRENT_TIMESTAMP, CURRENT_USER,TG_OP,
operation varchar NOT NULL, TG_TABLE_NAME, row_to_json(OLD), null);
table_name varchar NOT NULL, RETURN OLD;
old_row json, ELSIF (TG_OP = 'UPDATE') THEN
new_row json INSERT INTO audit
); VALUES (CURRENT_TIMESTAMP, CURRENT_USER,TG_OP,
TG_TABLE_NAME, row_to_json(OLD), row_to_json(NEW));
RETURN NEW;
...
Arguments Trigger Use Cases

• Table Partitioning
• TG_NARGS • Splitting what is logically one large table into smaller physical
• The number of arguments given to the trigger procedure in the pieces
CREATE TRIGGER statement

• TG_ARGV[]
• Used to:
• Increase performance
• The arguments from the CREATE TRIGGER statement • Archive data
• Storage tiering

Table Partitioning before PostgreSQL 10 Table Partitioning before PostgreSQL 10

• The trigger function will move the row to the correct child table
• Create child tables for each partition CREATE OR REPLACE FUNCTION partition_audit_trigger()
RETURNS TRIGGER AS $$
CREATE TABLE audit_2014 (
BEGIN
CHECK ( event_time >= DATE '2014-01-01'
EXECUTE 'INSERT INTO audit_' ||
AND event_time < DATE '2015-01-01')
to_char(NEW.event_time, 'YYYY') ||
) INHERITS (audit);
' VALUES ($1, $2, $3, $4, $5, $6)'
USING NEW.event_time, NEW.user_name, [Link],
CREATE TABLE audit_2015 (
NEW.table_name, NEW.old_row, NEW.new_row;
CHECK ( event_time >= DATE '2015-01-01'
AND event_time < DATE '2016-01-01')
RETURN NULL;
) INHERITS (audit);
END;
$$
LANGUAGE plpgsql;
Table Partitioning before PostgreSQL 10 Execution Performance
• Performance is much better if dynamic SQL is not used
CREATE OR REPLACE FUNCTION partition_audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
• A trigger needs to be added to the parent table IF ( NEW.event_time >= DATE '2015-01-01' AND
NEW.event_time < DATE '2016-01-01' ) THEN
CREATE TRIGGER partition_audit_trigger INSERT INTO audit_2015 VALUES (NEW.*);
BEFORE INSERT ON audit ELSIF ( NEW.event_time >= DATE '2014-01-01' AND
FOR EACH ROW NEW.event_time < DATE '2015-01-01' ) THEN
EXECUTE PROCEDURE INSERT INTO audit_2014 VALUES (NEW.*);
partition_audit_trigger(); ELSE
RAISE EXCEPTION 'Date out of range. Fix
partition_audit_trigger() function!';
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Moving Partitions Moving Partitions

CREATE FUNCTION move_partition_audit_trigger() RETURNS TRIGGER AS $$
DECLARE
• If the column used for the partition key changes, the row may start_date DATE;
need to be moved to a different partition end_date DATE;
BEGIN
CREATE TRIGGER move_partition_audit_trigger start_date := TG_ARGV[0];
BEFORE UPDATE end_date := TG_ARGV[1];
ON audit_2014
FOR EACH ROW EXECUTE PROCEDURE IF ( NEW.event_time IS DISTINCT FROM OLD.event_time ) THEN
move_partition_audit_trigger('2014-01-01', '2015-01-01'); IF (NEW.event_time < start_date OR NEW.event_time >= end_date) THEN
EXECUTE 'DELETE FROM ' || TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME ||
CREATE TRIGGER move_partition_audit_trigger ' WHERE ctid = $1' USING [Link];
BEFORE UPDATE INSERT INTO audit VALUES (NEW.*);
ON audit_2015 RETURN null;
FOR EACH ROW EXECUTE PROCEDURE END IF;
move_partition_audit_trigger('2015-01-01', '2016-01-01'); END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Moving Partitions Trigger Use Cases
• Only fire the trigger if the partition key changes
CREATE TRIGGER move_partition_audit_trigger
BEFORE UPDATE
ON audit_2014 • Calculate columns
FOR EACH ROW • Calculate complex values
WHEN (NEW.event_time IS DISTINCT FROM OLD.event_time) • Extract values from complex structures
EXECUTE PROCEDURE • Enforce derived values when using denormalization
move_partition_audit_trigger('2014-01-01', '2015-01-01'); • Used to:
• Increase performance
CREATE TRIGGER move_partition_audit_trigger • Simplify queries
BEFORE UPDATE
ON audit_2015
FOR EACH ROW
WHEN (NEW.event_time IS DISTINCT FROM OLD.event_time)
EXECUTE PROCEDURE
move_partition_audit_trigger('2015-01-01', '2016-01-01');

Extract JSON Extract JSON

$ head -n 5 [Link]
{ ”_id” : ”01001”, ”city” : ”AGAWAM”,
CREATE OR REPLACE FUNCTION extract_data_trigger()
”loc” : [ -72.622739, 42.070206 ], ”pop” : 15338, ”state” : ”MA” }
RETURNS TRIGGER AS $$
{ ”_id” : ”01002”, ”city” : ”CUSHMAN”,
BEGIN
”loc” : [ -72.51564999999999, 42.377017 ], ”pop” : 36963, ”state” : ”MA” }
NEW.zip_code := [Link]->>'_id';
{ ”_id” : ”01005”, ”city” : ”BARRE”,
[Link] := [Link]->>'state';
”loc” : [ -72.10835400000001, 42.409698 ], ”pop” : 4546, ”state” : ”MA” }
{ ”_id” : ”01007”, ”city” : ”BELCHERTOWN”,
RETURN NEW;
”loc” : [ -72.41095300000001, 42.275103 ], ”pop” : 10579, ”state” : ”MA” }
END;
{ ”_id” : ”01008”, ”city” : ”BLANDFORD”,
$$ LANGUAGE plpgsql;
”loc” : [ -72.936114, 42.182949 ], ”pop” : 1240, ”state” : ”MA” }
CREATE TABLE zips ( CREATE TRIGGER extract_data_trigger
zip_code varchar PRIMARY KEY, BEFORE UPDATE OR INSERT ON zips
state varchar, FOR EACH ROW EXECUTE PROCEDURE extract_data_trigger();
data json
);
Trigger Use Cases Cache Invalidation

CREATE FUNCTION remove_cache_trigger()

• Cache invalidation RETURNS TRIGGER AS $$
• Remove stale entries from a cache BEGIN
• The database tracks all data so is the single source of truth DELETE from myredis_cache
• Used to: WHERE key = [Link]::varchar;
• Simplify cache management
• Remove application complexity RETURN NEW;
END;
Note: Foreign Data Wrappers simplify this process significantly $$ LANGUAGE plpgsql;

Note: ON (action) CASCADE contraints can simplify this too. CREATE TRIGGER remove_cache_trigger
AFTER UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE PROCEDURE remove_cache_trigger();

Cache Invalidation - Async Event Triggers

• The latency of updating the cache may not be an acceptable as

part of the main transaction • Event triggers fire for DML commands (CREATE, ALTER, DROP,
etc)
CREATE FUNCTION remove_cache_trigger()
RETURNS TRIGGER AS $$
BEGIN • They are not tied to a single table
PERFORM pg_notify(TG_TABLE_NAME, [Link]::varchar);

RETURN NEW; • They are global to a database

END;
$$ LANGUAGE plpgsql;
Event Triggers Event Trigger Events

CREATE OR REPLACE FUNCTION notice_ddl()

RETURNS event_trigger AS
• ddl_command_start
$$
BEGIN • ddl_command_end
RAISE NOTICE 'DDL Fired: % %', tg_event, tg_tag;
END;
$$ LANGUAGE plpgsql; • table_rewrite
CREATE EVENT TRIGGER notice_ddl
ON ddl_command_start • sql_drop
EXECUTE FUNCTION notice_ddl();

ddl_command_start ddl_command_end

• Fired just before the command starts

• This is before any information is known about the command • Fired after the command ends

• Fires for all event trigger command tags • Fires for all event trigger command tags

• Does not fire for shared objects such as databases and roles • The objects have been affected so the details of the command
can be obtained
• Does not fire for commands involving event triggers
sql_drop table_rewrite

• Fired just before ddl_command_end fires

• The objects have already been removed so they are not accessible • Fired just before the table is rewritten by the command

• Only fired for commands that rewrites an object

• Only fired for commands that drop an object
• CLUSTER and VACUUM FULL do not fire the event
• The objects have been affected so the details of the command
can be obtained

Event Tags Event Trigger Functions

• ALTER POLICY • CREATE TABLE

• ALTER SCHEMA • CREATE TABLE AS
• ALTER SEQUENCE • CREATE VIEW
• ALTER TABLE • DROP INDEX A set of functions to help retrieve information from event triggers
• CREATE EXTENSION • DROP TABLE
• CREATE FUNCTION • DROP VIEW • pg_event_trigger_ddl_commands
• CREATE INDEX • GRANT
• CREATE SEQUENCE • REVOKE • pg_event_trigger_dropped_objects
• pg_event_trigger_table_rewrite_oid
The full list is available in the documentation
• pg_event_trigger_table_rewrite_reason

[Link]
Understanding pg_event_trigger_ddl_commands Understanding pg_event_trigger_dropped_objects
• Returns a line for each DDL command executed
• Only valid inside a ddl_command_end trigger • Returns a line for each object dropped by the DDL executed
• Only valid inside a sql_drop trigger
Column Type
classid oid Column Type Column Type
objid oid
objsubid integer classid oid object_type text
command_tag text objid oid schema_name text
object_type text objsubid integer object_name text
schema_name text original bool object_identity text
object_identity text normal bool address_names text[]
in_extension bool is_temporary bool address_args text[]
command pg_ddl_command

Understanding rewrite functions Using Event Trigger Functions

CREATE OR REPLACE FUNCTION stop_drops()
RETURNS event_trigger AS
$$
• Only valid inside a table_rewrite trigger DECLARE
l_tables varchar[] := '{sales, inventory}';
BEGIN
• pg_event_trigger_table_rewrite_oid IF EXISTS(SELECT 1
FROM pg_event_trigger_dropped_objects()
• Returns the OID of the table about to be rewritten
WHERE object_name = ANY (l_tables)) THEN
RAISE EXCEPTION 'Drops of critical tables are not permitted';
• pg_event_trigger_table_rewrite_reason END IF;
END;
• Returns the reason code of why the table was rewritten $$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER stop_drops

ON sql_drop
EXECUTE FUNCTION stop_drops();
Things to Remember

• Triggers are part of the parent transaction

• The trigger fails, the main transaction fails PL/pgSQL Best Practices
• The main transaction rolls back, the trigger call never happened
• If the trigger takes a long time, the whole transaction timing is
affected

• Triggers can be difficult to debug

• Especially cascaded triggers

Programming Practices Naming Conventions

• Follow good programming practices • Create and follow a consistent naming convention for objects
• Indent code consistantly • PostgreSQL is case insensitive so init cap does not work, use ”_”
• Comment code liberly to seperate words in names
• Code reuse/modularity practices are different than other • Prefix all parameter names with something like “p_”
programming languages • Prefix all variable names with something like “v_”
• Deep call stacks in PL/pgSQL can be performance intensive
Performance

• Avoid expensive constructs unless necessary

• Dynamic SQL
• EXCEPTION blocks