How to use triggers and stored procedures in PostgreSQL

http://www.openlogic.com/wazi/bid/340581/how-to-use-triggers-and-stored-procedures-in-postgresql


The PostgreSQL relational database management system (RDBMS) offers powerful mechanisms for the automated handling of data inserts and manipulations. Expand the functionality of your system with the usage of triggers and custom-developed stored procedures.
Stored procedures allow database engineers to enter code in a procedural language to create some functionality. The code can be executed directly or set to run when certain actions are triggered. Triggers are associated with tables, and can start before or after a specified event occurs. This means that once an operation such as INSERT, UPDATE, DELETE, or TRUNCATE is executed on the table, the trigger will run the corresponding procedure.
While the usage of stored procedures requires the understanding of additional programming syntax, it can be of great advantage for application programmers. Instead of manipulating database records in an application's code, they can program some algorithms directly in the database layer. This improves the loading speed of the application and significantly decreases the volume of data transfer from the database to the script's engine and back. On the down side, testing stored procedures is more complicated, since quality assurance engineers need to separate and run their tests under two different programming paradigms – the application's source code and the programming language used in the database stored procedures.
The default programming language for the PostgreSQL stored procedures is PL/pgSQL – SQL Procedural Language. PostgreSQL also has core support for TCL, Perl, and Python, and supports via external contributions PHP, Ruby, Java, and other popular languages.
To work with stored procedures and triggers, start by installing the latest stable version of PostgreSQL, so you can be confident that all the known issues and bugs in previous releases are resolved. Pick the correct RPM package for your architecture from the PostgreSQL packages download page and run the following commands to install the PostgreSQL RDBMS packages on your CentOS server:

wget http://yum.postgresql.org/9.3/redhat/rhel-6-i386/pgdg-centos93-9.3-1.noarch.rpm
rpm -ivH pgdg-centos93-9.3-1.noarch.rpm
yum install postgresql93-devel postgresql93-server postgresql93-contrib

Next, initialize the PostgreSQL cluster and start the server:

service postgresql-9.3 initdb
Initializing database:                                     [  OK  ]
/etc/init.d/postgresql-9.3 start
Starting postgresql-9.3 service:                           [  OK  ]

Then load the PostgreSQL command-line interface, create a new database, a database user, and grant the necessarily privileges:

su postgres
bash-4.1$ psql
postgres=# CREATE DATABASE resort;
CREATE DATABASE
postgres=# CREATE USER manager WITH PASSWORD 'MyStr0ngP@ss';
CREATE ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE resort TO manager;
GRANT

You will enter the database name, the database username, and the corresponding password in the connection string of your application's code.
Now populate your new database with some sample data. For this test case run the queries as shown below:

postgres=# \c resort;
You are now connected to database "resort" as user "postgres".

CREATE TABLE employees(id SERIAL PRIMARY KEY NOT NULL, username VARCHAR (10) UNIQUE NOT NULL, name VARCHAR (100) NOT NULL, email VARCHAR (200) UNIQUE NOT NULL, position TEXT NOT NULL);
CREATE TABLE
INSERT INTO employees(username, name, email, position) VALUES ('john','johnd@mywinterrestort.com','John D.','General Manager'), ('steven','stevenm@mywinterrestort.com','Steven M.','Night Shift Supervisor'), ('teresa', 'teresaa@mywinterrestort.com','Teresa A.','Receptionist'), ('roger', 'rogerd@mywinterrestort.com','Roger D.','Receptionist');
INSERT 0 4

CREATE TABLE clients(id SERIAL PRIMARY KEY NOT NULL, name TEXT NOT NULL, number_of_guests INTEGER NOT NULL);
CREATE TABLE
INSERT INTO clients(name, number_of_guests) VALUES ('Ian A.','4'), ('Sonia M.','2'), ('Tony P.','1'), ('Lora S.','6');
INSERT 0 4

CREATE TABLE apartments(id SERIAL PRIMARY KEY NOT NULL, name TEXT NOT NULL, number_of_beds INTEGER NOT NULL);
CREATE TABLE
INSERT INTO apartments(name, number_of_beds) VALUES ('Regular 1','2'), ('Regular 2','2'), ('Luxury','4'), ('Ultra Luxury','6');
INSERT 0 4

CREATE TABLE reservations_statuses(id INTEGER PRIMARY KEY NOT NULL, status VARCHAR (10) UNIQUE NOT NULL);
CREATE TABLE
INSERT INTO reservations_statuses(id, status) VALUES ('1','Pending'), ('2','Cancelled'), ('3','Paid');
INSERT 0 3

CREATE TABLE reservations(id SERIAL PRIMARY KEY NOT NULL, employee_id INT REFERENCES employees NOT NULL, client_id INT REFERENCES clients NOT NULL, apartment_id INT REFERENCES apartments NOT NULL, created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP, date_of_arrival DATE NOT NULL, nights_to_stay INTEGER NOT NULL, status_id INT REFERENCES reservations_statuses DEFAULT '1');
CREATE TABLE
INSERT INTO reservations(employee_id, client_id, apartment_id, created_on, date_of_arrival, nights_to_stay) VALUES ('3','2','1','2014-03-12 10:03:54','2014-May-08','4'), ('3','1','3','2014-03-16 18:23:54','2014-May-28','2'), ('4','4','4','2014-03-22 06:23:54','2014-July-12','7');
INSERT 0 3

CREATE TABLE reservations_changes(id SERIAL PRIMARY KEY NOT NULL, employee_id INT REFERENCES employees NOT NULL,  reservation_id INT REFERENCES reservations NOT NULL, changed_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP, old_arrival_date TIMESTAMP,  old_status_id INT REFERENCES reservations NOT NULL, old_period_length INT NOT NULL);
CREATE TABLE

Stored procedures syntax

Once you have prepared your database for the tests you can write a simple stored procedure that counts the number of the reservations in the corresponding table:

CREATE FUNCTION reservations_count()
RETURNS integer 
AS 
$function_body$
declare
rescount integer;
BEGIN
   SELECT COUNT(*) INTO rescount FROM reservations;
   RETURN rescount;
END;
$function_body$
LANGUAGE plpgsql;

CREATE FUNCTION does just what it says, or you can extend the command by including an option to alter an existing function with the syntax CREATE OR REPLACE FUNCTION. The command is followed by the function's name and the lists of the arguments, if you have any to define. Specify the type of data that your function is to return. The body of the procedure should be surrounded by dollar-quoted string delimiters, so that you do not have to escape single quote marks and backslashes in the function's body. On the next two lines, declare the variable which you are going to use in your function and define its type. The BEGIN and END; statements surround the code that is to be executed when your function is called. In our case it contains a SELECT SQL query that counts the number of rows from the reservations table and stores them in the rescount variable, which it returns as the result of the function's execution. Finally, specify the used procedural language.
Upon successful creation of the stored procedure PostgreSQL will display the CREATE FUNCTION message at the command prompt.
Now test the function:

SELECT reservations_count();
 reservations_count
--------------------
    3
(1 row)

Triggers usage

Now let's switch gears and create a triggered procedure that logs the previous status of each reservation. We will set it to run on every update of a record in the reservations table:

CREATE FUNCTION reservations_log()
RETURNS trigger 
AS 
$$
declare 
BEGIN
 INSERT INTO reservations_changes (employee_id, reservation_id, old_arrival_date, old_status_id, old_period_length)
 VALUES (OLD.employee_id, OLD.id, OLD.date_of_arrival, OLD.status_id, OLD.nights_to_stay);
 RAISE NOTICE 'Employee changed reservation #%', OLD.id;
 RETURN NEW;
END;
$$
LANGUAGE plpgsql;

This code creates a function without arguments. The returned type is trigger. There are no variables to be declared. The code inserts the old values of the row being updated into the reservations_changes table, as a way of logging the old records. The NEW data variable from the RECORD type stores the updated database row from the reservation table. The RAISE NOTICE command is a part of the PostgreSQL messages report system. Here it displays a message with the ID of the updated reservation's record at the command prompt.
Next, create the trigger that is to be called automatically after each UPDATE query on the reservation table. This trigger will start the procedure developed above:

CREATE TRIGGER res_log
AFTER UPDATE ON reservations
FOR EACH ROW EXECUTE PROCEDURE reservations_log();

The creation of a trigger in PostgreSQL is confirmed by the CREATE TRIGGER message.
You can test the functionality of your procedure and the trigger you have set:

UPDATE reservations SET date_of_arrival='2014-09-22' WHERE id='1';
NOTICE:  Employee changed reservation #1
UPDATE 1

SELECT * FROM reservations;
 id | employee_id | client_id | apartment_id |     created_on      | date_of_arrival | nights_to_stay | status_id
----+-------------+-----------+--------------+---------------------+-----------------+----------------+-----------
  2 |           3 |         1 |            3 | 2014-03-16 18:23:54 | 2014-05-28      |              2 |         1
  3 |           4 |         4 |            4 | 2014-03-22 06:23:54 | 2014-07-12      |              7 |         1
  1 |           3 |         2 |            1 | 2014-03-12 10:03:54 | 2014-09-22      |              4 |         1
(3 rows)

SELECT * FROM reservations_changes;
 id | employee_id | reservation_id |         changed_on         |  old_arrival_date   | old_status_id | old_period_length
----+-------------+----------------+----------------------------+---------------------+---------------+-------------------
  1 |           3 |              1 | 2014-03-23 09:47:37.943072 | 2014-05-08 00:00:00 |             1 |                 4
(1 row)

Pros and cons of stored procedures

Web developers debate the benefit of stored procedures. On one hand, stored procedures can save you overhead on data transferred between the application you are programming and the database, and thereby improve performance. By using stored procedures you do not send unnecessary rows to your script and do not need to write logic to better handle the results. This might decrease the lines of the programming code you have to write, and you can use created procedures for different scripts. Also, stored procedures in PostgreSQL benefit from the database's high level of security.
On the other hand, developing stored procedures for your project requires the usage of an unfamiliar programming solution. Programmers may need to learn new syntax, and this might slow down the development process. Stored procedures can make testing and debugging more complicated. Finally, stored procedures make it more difficult to port a function's code to a different database engine.
Nevertheless, stored procedures and triggers are powerful tools for database programmers.