Relational Database Design

PL/PGSQL Language
 PL/pgSQL is a procedural programming language for the
PostgreSQL database system.
 PL/pgSQL allows you to extend the functionality of the
PostgreSQL database server by creating server objects with
complex logic.
 PL/pgSQL is a portable language i.e. its functions are compatible
with all platforms that are operate postgresql database system.
 PL/pgSQL was designed to :
o Create user-defined functions
o Stored procedures
o Triggers
 Creating language to add PL/PGSQL :-

 CREATE LANGUAGE command is used to add PL/PGSQL language

to our database before using it.
 The use of CREATE LANGUAGE command first requires the creation
of the PL/PGSQL call handler, which is the function that actually
processes and interprets the PL/PGSQL code.
 Creating the PL/PGSQL call handler :-
create function plpgsql_call_handler()
returns language_handler
as ‘$libdir/plpgsql’;
language c;
 This will only create the function handler as output CREATE
FUNCTION
The LANGUAGE must also be added as follows ->
create trusted procedural language
plpgsql handler
plpgsql_call_handler;

The CREATE LANGUAGE will return you to a database

prompt upon successful
execution.
 PL/Pgsql: Language Structure
 The structure of a PL/PGSQL is very simple.
 Each portion of a code is designed to exist as a function.
 PL/PGSQL functions are case sensitive.
 All variables must be declared before being used and code segments
accept arguments when called and return arguments at their end.
 Commands to access plsql in Ubuntu
$sudo –i –u postgres
$psql
#create database sybca;
CREATE DATABASE
#\l (Show list of databases)
#\c sybca (To use sybca database)
 To create function
 Functions written in PL/pgSQL are defined to the server by
executing CREATE FUNCTION commands.
Syntax:
CREATE FUNCTION somefunc(integer, text) RETURNS integer AS $$
BEGIN
-------
function body
----------
END $$
LANGUAGE plpgsql;

 PL/pgSQL is a block-structured language.

 The complete text of a function body must be a block. A block is defined
as:
Syntax:
[ <<label>> ]
[ DECLARE declarations ]
BEGIN statements
END [ label ];
 PL/pgSQL is a blocked-structure language. It organize a program into
blocks.
 A block contains two parts: declaration and body. The declaration part
is optional while the body part is mandatory.
 Blocks can be nested. A nested block is a block placed inside the body
of another block.
 Each declaration and each statement within a block is terminated by a
semicolon.
 PL/pgSQL Sub blocks

 PL/pgSQL allows us to place a block

inside the body of another block.
 The block nested inside another block
is called a sub block. The block that
contains the sub block is referred to
as an outer block.
 We divide a large block into smaller
and more logical sub blocks.
 Example
create function add_no(a integer, integer)
returns integer as $$
BEGIN
Return a+b;
END
language plpgsql;
Run:
select add_no(2,4);
add_no
-------
6
Drop or Remove Function
Syntax:
drop function function_name;
Example:
drop function add_no;
 Comments
There are two types of comments :-
Single line comment :-
 The single line comment begin with two dashes (--) and have no end
character.
Eg –
--Write a program to add first n nos.
Block comment (multiple line comment ) :-
 It begins /* and ends with */
Eg-
/* Program which will display list of salary whose salary is above the
given limit of salary */
 The Single line comment can be nested within Block comments, But
Block comments can not be nested within other Block comments.
Statements and Expressions
 The PL/PGSQL is composed of Statements and Expressions.
Statements
 A statement performs an action with PL/PGSQL code. The
statement can be an assignment statements or the execution of a
query.
 The organization of statements mainly controls the order in which
operations are executed within the block.
 Every statement should end with a Semicolon.
Expressions
 Expressions are calculations which return their result.
 Eg – x=a+b;

Variables
Variables are used with PL/PGSQL code to store variable data to an
explicitly stated type.
 All the variables should be declared before use in DECLARE section.
 If the variable is not initialized then by default it will be assigned
NULL value.
 The basic Data Types which are supported are as follows:
1. boolean 6.real
2. text 7.date
3. varchar(size) 8.time
4. char(size)
5. Integer
 Syntax of declaration :- Var_name data type [ :=value];
Conditional Statements
 PL/pgSQL if statements to execute a command based on a
specific condition.
 The if statement determines which statements to execute
based on the result of a boolean expression.
 PL/pgSQL provides you with three forms of
the if statements.
 if then
 if then else
 if then elsif
PL/pgSQL if-then statement

Syntax:
if condition then
statements;
end if;
The if statement executes statements if a condition is true.
If the condition evaluates to false, the control is passed to
the next statement after the END if part.
PL/pgSQL if-then-else statement

 Syntax:
if condition then
statements;
else alternative-statements;
end if;

The if then else statement executes the statements in

the if branch if the condition evaluates to true otherwise
it executes the statements in the else branch.
 PL/pgSQL if-then-elsif Statement
Syntax:
if condition_1 then
statement_1;
elsif condition_2 then
statement_2
...
elsif condition_n then
statement_n;
else
statement;
end if;
The if and if then else statements evaluate one condition.
if then elsif statement evaluates multiple conditions.
Loops

 It is another method of controlling the flow of functions.

 They use iteration in a number of different ways to
accomplish a particular tasks, which will use functionality of
a function to a greater extent.
 There are three loops in PL/PGSQL.
1. The basic loop
2. The while loop
3. The for loop
2.The while loop
 This is conditional loop.
 The while loop will loop through a block of statements until a
specified condition becomes false.
 Each time a while loop is entered, its condition will be evaluated
before the statement block is executed.
 If the result is true, then the statement block will be executed.

 Syntax :-
while condition loop
Statements;
end loop;
 3.For loop
 The for loop is a loop to iterate a statement block over a range of
integers that you specify.
 In for loop, an integer variable is stated first, which will track the
iteration of the loops until the range is given and finally a statement
block is provided.
 The integer variable created to track the loops iteration is destroyed
once the loop exists. So it does not have to be declared in the declare
section.
Syntax :-
for identifier in [reverse] expression1..expression2
loop
statements;
end loop;
 view
 A view can represent a subset of a real table, selecting certain columns
or certain rows from an ordinary table.
 A view can even represent joined tables.
 We can use them to restrict table access so that the users see only
specific rows or columns of a table.
 A view can contain all rows of a table or selected rows from one or
more tables.
 A view can be created from one or many tables, which depends on the
written PostgreSQL query to create a view.
 Views, which are kind of virtual tables, allow users to do the following
−
❑ Structure data
❑ Restrict access
❑ Summarize data
Creating Views

 The PostgreSQL views are created using the CREATE

VIEW statement.
 The basic CREATE VIEW syntax is as follows −
CREATE VIEW view_name AS SELECT column1,
column2..... FROM table_nameWHERE [condition];
CREATE VIEW COMPANY_VIEW AS SELECT ID, NAME,
AGE FROM COMPANY;
Or
SELECT * FROM COMPANY_VIEW;
Dropping Views

 To drop a view, simply use the DROP VIEW statement with

the view_name.
 The basic DROP VIEW syntax is as follows −
DROP VIEW view_name;
DROP VIEW COMPANY_VIEW;
Cursor in PostgreSQL
 A Cursor in PostgreSQL is used to process or store large Database
tables.
 A cursor is a pointer to this context area.
 PL/SQL controls the context area through a cursor.
 A cursor holds the rows (one or more) returned by a SQL
statement. The set of rows the cursor holds is referred to as
the active set.
 You can name a cursor so that it could be referred to in a program
to fetch and process the rows returned by the SQL statement, one
at a time.
 Suppose if a table has 10 million or billion rows. While performing
a SELECT operation on the table it will take some time to process
the result and most likely give an “out of memory” error and
the program will be terminated.
Cursor in PostgreSQL
 There are 2 types of Cursors: Implicit Cursors, and Explicit
Cursors. These are explained as following below.
 Implicit Cursors:
Implicit Cursors are also known as Default Cursors of SQL
SERVER. These Cursors are allocated by SQL SERVER when
the user performs DML operations.
 Explicit Cursors :
Explicit Cursors are Created by Users whenever the user
requires them. Explicit Cursors are used for Fetching data
from Table in Row-By-Row Manner.
 Working with an cursor includes the
following steps :

 Declaring the cursor for initializing the memory

 Opening the cursor for allocating the memory
 Fetching the cursor for retrieving the data
 Closing the cursor to release the allocated memory
 Syntax:
DECLARE [cursor_name] CURSOR FOR [query]
 Use DECLARE to declare a cursor
 [cursor_name] – Give any name to the cursor
 [query] – Give a query to the cursor
After declaring a cursor, we can get the data using FETCH. The
FETCH gets the next row(s) from the cursor. If no row
found, then it returns NULL.
Fetch Data from cursor
 There are total 6 methods to access data from cursor. They are as
follows :
FIRST is used to fetch only the first row from cursor table.
LAST is used to fetch only last row from cursor table.
NEXT is used to fetch data in forward direction from cursor table.
PRIOR is used to fetch data in backward direction from cursor
table.
ABSOLUTE n is used to fetch the exact nth row from cursor
table.
RELATIVE n is used to fetch the data in incremental way as well
as decremental way.
Syntax : FETCH NEXT/FIRST/LAST/PRIOR/ABSOLUTE
n/RELATIVE n FROM cursor_name
 Lets, create a sample table using the below commands for
examples:
 CREATE TABLE students (
student_id serial PRIMARY KEY,
full_nameVARCHAR NOT NULL,
branch_id INT );
INSERT INTO students ( student_id, full_name, branch_id ) VALUES
(1, 'M.S Dhoni', NULL),
(2, 'SachinTendulkar', 1),
(3, 'R. Sharma', 1),
(4, 'S. Raina', 1),
(5, 'B. Kumar', 1),
(6, 'Y. Singh', 2),
(7, 'Virender Sehwag ', 2),
(8, 'Ajinkya Rahane', 2),
(9, 'Shikhar Dhawan', 2),
(10, 'Mohammed Shami', 3),
(11, 'Shreyas Iyer', 3),
(12, 'Mayank Agarwal', 3),
(13, 'K. L. Rahul', 3),
(14, 'Hardik Pandya', 4),
(15, 'Dinesh Karthik', 4),
(16, 'Jasprit Bumrah', 7),
(17, 'KuldeepYadav', 7),
(18, 'Yuzvendra Chahal', 8),
(19, 'Rishabh Pant', 8),
(20, 'Sanju Samson', 8);
we can declare our cursor by using :
BEGIN;
DECLARE my_cursor CURSOR FOR SELECT * FROM students;
We can fetch the data by using :
FETCH 10 FROM my_cursor;
FETCH PRIOR FROM my_cursor;
FETCH PRIOR FROM my_cursor;
 The above query will give you row 9 and 8
 FETCH 6 FROM my_cursor;
Closing the Cursor

 Closing the cursor means releasing the allocated memory.

 For example, we will close the above-opened cursor as
follows −
CLOSE cursor_name;
Error and Exception Handling
 Any error occurring in a PL/pgSQL function aborts execution of the
function. Errors can be trapped and recovered by using a Begin block
with an Exception clause.
 Syntax:
Declare
Declarations
Begin
Statements
Exception
When condition then
Handler statements
End;
 If no errors occur, this form of block simply executes all the statements,
and then control passes to the next statement. But if error occurs within
the statements, further processing of the statements is stopped and
control passes to the exception list.
Raise Statement
 It is used to raise errors, report messages and exceptions during a
PL/pgSQL function’s execution.
 This statement can raise built in exceptions such as
division_by_zero, no_data_found exception , too_many_rows
exception, multiple exceptions etc.
 Syntax: RAISE level ''format string'' [, expression [, ...]];
Where,
• level: The level option specifies the severity of an error. Level can
be:
1. DEBUG: DEBUG level statements send the specified text as a
message to the PostgreSQL log and the client program if the client
is running in debug mode.
Raise Statement
2. NOTICE: This level statement sends the specified text as a
message to the client program.
3. EXCEPTION: This statement sends the specified text as error
message. It causes the current transaction to be aborted.
format: The format is a string that specifies the message. The
format uses percentage (%) placeholders that will be substituted
by the next arguments. The number of placeholders must match
the number of arguments; otherwise PostgreSQL will report the
following errormessage
example 1: ERROR: too many parameters specified for RAISE.
example 2: ERROR: control reached end of function without
RETURN
create or replace function raise_test(integer,integer)
returns void as '
DECLARE
x1 alias for $1;
x2 alias for $2;
div INTEGER;
BEGIN
RAISE DEBUG ''The raise_test() function begins from here.'';
if x2 != 0 then div = x1 / x2;
raise notice ''Division of % and % is : %'', x1, x2, div;
else
RAISE EXCEPTION ''Transaction aborted due to division by zero'';
end if;
END;'
language 'plpgsql';
First raise statement gives a debug level message and sends specified text to
PostgreSQL log.
The second statements displays an error and throws an exception, which
causes the function to end.
PostgreSQL - TRIGGERS

 A PostgreSQL trigger is a function invoked automatically

whenever an event associated with a table occurs. An event could
be any of the
following: INSERT, UPDATE, DELETE or TRUNCATE.
 A trigger is a special user-defined function associated with a table.
 PostgreSQL trigger can be specified to fire
 Before the operation is attempted on a row (before constraints are
checked and the INSERT, UPDATE or DELETE is attempted)
 After the operation has completed (after constraints are checked and
the INSERT, UPDATE, or DELETE has completed)
 Instead of the operation (in the case of inserts, updates or deletes on a
view)
 The basic syntax of creating a trigger is as follows −
 CREATE TRIGGER trigger_name
[BEFORE|AFTER|INSTEAD OF] event_name ON
table_name [ -- Trigger logic goes here.... ];
 event_name could be INSERT,
DELETE, UPDATE, and TRUNCATE database operation on the
mentioned table table_name.
 You can optionally specify FOR EACH ROW after table
name.
 The following is the syntax of creating a trigger on an
UPDATE operation on one or more specified columns of a
table as follows −
 CREATE TRIGGER trigger_name [BEFORE|AFTER]
UPDATE OF column_name ON table_name [ -- Trigger
logic goes here.... ];

Listing and Dropping TRIGGERS

Listing TRIGGERS
SELECT * FROM pg_trigger;
Listing TRIGGERS on a particular table
If you want to list the triggers on a particular table, then use AND clause
with table name as follows :
SELECT tgname FROM pg_trigger, pg_class WHERE
tgrelid=pg_class.oid AND relname='company';
Dropping TRIGGERS
 The following is the DROP command, which can be used to drop an
existing trigger −
DROP TRIGGER trigger_name ON table_name;
Example:
drop trigger trig_pet on person;