It's a procedural language that allows you to develop complex functions and stored procedures in PostgreSQL that may not be possible using plain SQL, e.g., control structures, loops, and complex computations.
This file contains the script to generate the database in postgres and the type of data to be inserted.
With this script we can insert some data.
In this project we have to use:
- Functions
- Procedures
- Cursors
- Triggers
The next function calculates the salary statistics for a table of employees. This function has three variables: "min_salary", "max_salary" and "avg_salary", which contain the minimum, maximum and average values of the salaries in the "employees" table.
The function begins with the BEGIN clause and ends with the END. The function calculates the salary statistics using the PostgreSQL MIN, MAX and AVG keywords to calculate the minimum, maximum and average salary in the "employees" table. The AVG function also uses ::NUMERIC(6,1) to convert the result into a number with a precision of 6 digits and 1 decimal place.
The INTO clause assigns the results of the query to the output variables "min_salary", "max_salary" and "avg_salary". If no employees are found in the table, an exception is thrown with a error message indicating that no employees were found in the table.
Finally, the function returns the values of the output variables "min_salary", "max_salary" and "avg_salary" using the RETURN clause.
CREATE OR REPLACE FUNCTION get_salary_stat(
OUT min_salary NUMERIC,
OUT max_salary NUMERIC,
OUT avg_salary NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
SELECT MIN(salary),
MAX(salary),
AVG(salary)::NUMERIC(6,1)
INTO min_salary, max_salary, avg_salary
FROM employees;
IF min_salary IS NULL OR max_salary IS NULL OR avg_salary IS NULL THEN
RAISE EXCEPTION 'No employees found'
USING HINT = 'Insert some data into employees table';
END IF;
RETURN;
END;
$$;
Run this command SELECT get_salary_stat();
Expected output with data:
Expected output use of HINT:
This function returns the set result of the columns "country_name" and "region_name" by means of an INNER JOIN. The function takes a "letter" parameter as input and returns the rows matching the country search pattern in the table "countries" and "regions".
The RETURNS TABLE clause defines the structure of the result table that the function will return. In this case, the result table comes from two columns: "country_name" and "region_name".
Within the body of the function, the RETURN QUERY clause is used to return a result set consisting of the columns of the "countries" table joined to the "regions" table based on the "region_id" field. The WHERE clause is used to find a pattern in the country_name column.
In short, this query returns the country in which our company has a branch office starting with the given entry and the name of the continent to which it belongs.
CREATE OR REPLACE FUNCTION location_data(
letter VARCHAR)
RETURNS TABLE (country_name VARCHAR, region_name VARCHAR)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
SELECT c.country_name, r.region_name
FROM countries c
INNER JOIN regions r ON c.region_id = r.region_id
WHERE c.country_name LIKE UPPER('%' || letter || '%');
END;
$$;
Expected output:
Procedure is a named block that does a specific task. PL/SQL procedure allows you to encapsulate complex business logic and reuse it in both database layer and application layer.
DECLARE is for put the variables used in the body section.
A record variable is a variable that contains only one row of a result set.
FOR,LOOP to run into each result and create the output.
CREATE OR REPLACE PROCEDURE get_employee_contact(
letter VARCHAR)
LANGUAGE plpgsql AS $$
DECLARE
employee_data record;
BEGIN
FOR employee_data in(
SELECT "first_name", "email", "phone_number" from employees
WHERE "first_name" LIKE '%' || letter || '%'
ORDER BY "first_name")
LOOP
RAISE NOTICE 'NAME: %, E-MAIL: %, PHONE: %', employee_data."first_name", employee_data."email", employee_data."phone_number";
END LOOP;
END;
$$;
Run this command CALL get_employee_contact();
Expected output:
In this case, this query returns all the contacts that matches the value entered.
A CURSOR allows you to encapsulate a query and process each individual row at a time.Typically, you use cursors when you want to divide a large result set into parts and process each part individually.
Query explanation:
This function that takes a parameter name of type VARCHAR and returns a TEXT value.
Inside the block, variables are defined to store the names of the employees and their dependents. These variables are named names, employee_name, employee_last_name, dependent_name, and dependent_last_name.
A CURSOR is defined that will select the first_name and last_name of employees, the first_name and last_name of their dependents.
The CURSOR needs to be OPEN and CLOSE and inside the ''CURSOR'' we use a FOR,LOOP to run through each result every result and create the output with the FETCH statement in order to combine the results of the query and put it in the TEXT variable used to construct the output.
CHR(10) is the ASCII value for start a new line.
CREATE OR REPLACE FUNCTION get_parent_son(
name VARCHAR)
RETURNS TEXT
AS
$$
DECLARE
names TEXT DEFAULT '';
employee_name VARCHAR;
employee_last_name VARCHAR;
dependent_name VARCHAR;
dependent_last_name VARCHAR;
employee_dependents_cursor CURSOR FOR
SELECT e.first_name, e.last_name, d.first_name, d.last_name
FROM employees e
INNER JOIN dependents d ON e.employee_id = d.employee_id
WHERE CONCAT(e.first_name, ' ', e.last_name) LIKE '%' || name || '%';
BEGIN
OPEN employee_dependents_cursor;
LOOP
FETCH employee_dependents_cursor INTO employee_name, employee_last_name, dependent_name, dependent_last_name;
EXIT WHEN NOT FOUND;
names := names || 'EMPLOYEE: ' || employee_name || ' ' || employee_last_name ||
' --> DEPENDENT: ' ||dependent_name || ' ' || dependent_last_name || ' ' || CHR(10);
END LOOP;
CLOSE employee_dependents_cursor;
RETURN names;
END;
$$ LANGUAGE plpgsql;
Expected output:
A PostgreSQL trigger is a function invoked automatically whenever an event such as insert, update, or delete occurs.
First we have to create the following tables, in which we are going to store the data that will be inserted or updated depending on the command we execute.
- The table employee_summer: is for store the employees hired during the summer season.
- the table employee_update: is for store changes in the tables.
CREATE TABLE employee_summer(
employee_id INT NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
hire_date TIMESTAMP(6) NOT NULL);
CREATE TABLE employee_update(
employee_id INT NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
hire_date TIMESTAMP(6) NOT NULL);
As its name indicates, to create this trigger we have to create a function where we set the parameters we are going to insert and specify in the RETURN clause the word TRIGGER. OLD and NEW represent the states of the row in the table before or after the triggering event.
CREATE OR REPLACE FUNCTION summer_employees_insert()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
BEGIN
INSERT INTO employee_summer (employee_id, first_name, last_name, hire_date)
VALUES (NEW.employee_id, NEW.first_name, NEW.last_name, NOW());
RETURN NEW;
END;
$$;
In the function below we specify the timing that cause the trigger to fire. It can be BEFORE or AFTER an event occurs,the event can be INSERT , DELETE, UPDATE or TRUNCATE in this case we use INSERT and UPDATE.
We use the ON keyword for specify the name of the table associated with the trigger.
A row-level trigger that is specified by the FOR EACH ROW clause and we use the above function to complete the trigger.
CREATE TRIGGER employee_insert_trigger
AFTER INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION summer_employees_insert();
Values to insert:
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id)
VALUES (207,'Will','Git','wgitz@sqltutorial.org','518.023.9771','1990-05-08',1,1300.00,205,9);
Expected output:
Check values in the table employee_summer:
This function works as the previous function, only the name is changed.
CREATE OR REPLACE FUNCTION employees_update()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
BEGIN
INSERT INTO employee_update (employee_id, first_name, last_name, hire_date)
VALUES (NEW.employee_id, NEW.first_name, NEW.last_name, NOW());
RETURN NEW;
END;
$$;
This trigger it's the same as the previous one, only the timing is change with AFTER and the event is UPDATE also the table which stores the data.
CREATE TRIGGER employee_update_trigger
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION employees_update();
CREATE TRIGGER
Values to update:
UPDATE employees SET last_name = 'Gilbert' WHERE employee_id = 207;
Check the values in the table employee_update:
This keyword ALTER is for RENAME, ENABLE ,DISABLE a trigger.
- RENAME:
ALTER TRIGGER employee_update_trigger
ON employees
RENAME TO new_employee_update_trigger;
Execute \dS employees
Expected output:
- ENABLE(default):
ALTER TABLE employees
ENABLE TRIGGER new_employee_update_trigger;
- DISABLE
ALTER TABLE employees
DISABLE TRIGGER new_employee_update_trigger;