Procedural SQL

Procedural SQL includes the imperative programming style associated with computational steps in the function calls.
SQL functions: parameterized views that generalize the regular notion of views by allowing parameters.
Supports loops, if-then-else and assignment statements.

Creating a procedure:

Syntax: Here is the syntax of creating a function.

create function <function_name> (<argument_name> <domain_of_argument>)
    returns <return_type_domain>
    begin
        declare <variable_name> <domain_of_variable>
        ..... //some query to store the result into the variable.
        .....
    return <variable_name>
    end

create is a ddl command for creating.
function is the keyword used to signify that a function is being created. It takes an argument specified with its domain type.
returns keyword is used to signify the domain returned by the function.
begin is used to begin the body of the procedure.
return keyword before the end is used to actually return the result of the function.
end keyword is used to end the procedure.

Define a function that, given the name of a department, returns the count of the number of instructors in that department:

create function dept_count(dept_name varchar(20))
returns integer
    language 'plpgsql'  
    as $BODY$
    #variable_conflict use_column
    declare d_count integer;
    begin
       select count(*) into d_count
       from instructor
       where instructor.dept_name = dept_name;
    return d_count;
    end
    $BODY$

dept_count is the name of the function that is created.
The function takes dept_name of type varchar(20) as the argument and returns an integer.
language 'plpgsql' is used to convey that we are using Procedural SQL in Postregsql.
as $$ specifies the block within which the actual procedure definition starts.
variable_conflict use_column is a special plpgsql command that prevents a clash between the parameter names being used in the function and the argument names actually passed in it.
In the begin-end clause, a variable named d_count of type integer is declared.
The select query is used to store the count of the instructors in the dept_name taken as the argument, into the variable d_count. into keyword is used for that.
The variable d_count containing the stored result is returned by the function.
Using the function in the following query:
Find the department names and budget of all departments with more than 12 instructors:

select dept_name, budget
from department
where dept_count(dept_name) > 12

Here dept_count(dept_name) function is called in the where clause. The function returns the number of instructors in the department dept_name and that number is compared with 12 in the where clause. If it is greater than 12, the dept_name and budget corresponding to that row is extracted, otherwise not.

Note : Here dept_name is the same as the name of the parameter in the defined function above. Postgresql raises an error if the clash is not resolved. That is the reason why #variable_conflict use_column is used.

Deleting a Procedure:

SYNTAX

drop function <function_name> (<argument list>)

The above procedure can be deleted by:

drop function dept_count(dept_name varchar(20))

Loops:

while loop syntax

while <boolean expression> do
<sequence of statements>
end while

The sequence of statements will run until the boolean expression is true. repeat loop syntax

repeat
sequence of statements
until boolean expression
end repeat;

The sequence of statements will run until the boolean expression is false. for loop syntax

if-then-else and case statements:

if-then-else syntax

if <boolean expression> then
    sequence of statements;
elseif <boolean expression> then 
    sequence of statements;
else
    sequence of statements;
end if

where * elseif is an optional clause. * else is a default clause.

Case statement syntax

case <variable>
when <value1> then
    sequence of statements;
when <value2> then
    sequence of statements;
else
    sequence of statements;
end case

where * if variable takes the particular value in the when then clause, the corresponding sequence of statements are executed.

Exceptions

declare out_of_classroom_seats condition
declare exit handler for out_of_classroom_seats
begin
    ...
    signal out of classroom seats
    ...
end

The handler here is exit – causes enclosing begin . . . end to be terminate and exit.

Triggers

a set of actions that are performed in response to an insert, update, or delete operation on a specified table.

Creating a trigger:

Syntax:

CREATE TRIGGER <triggername> 
   {BEFORE | AFTER |INSTEAD OF} { INSERt | DELETE | UPDATE | TRUNCATE }
   ON <tablename>
   [FOR [EACH] { ROW | STATEMENT }]
       EXECUTE PROCEDURE trigger_function

A trigger for some event (insert/delete/update/trincate) named \ is created on the table named \ either for each row or for each statement which makes it a row level or a statement level trigger accordingly, which is then executed as a procedure either before the calling of a function / after the function call or instead of the function call.

Before triggers

Run before an update, or insert.
Values that are being updated or inserted can be modified before the database is actually modified.

After triggers

Run after an update, insert, or delete.

Row level triggers

Executed whenever a row is affected by the event on which the trigger is defined.
Example: Let Employee be a table with 100 rows. Suppose an update statement is executed to increase the salary of each employee by 10%. Any row level update trigger configured on the table Employee will affect all the 100 rows in the table during this update.

Statement level triggers

Perform a single action for all rows affected by a statement, instead of executing a separate action for each affected row.
Uses referencing old table or referencing new table to refer to temporary tables called transition tables containing the affected rows.

Dropping a trigger