How to Write a Normal Select Procedure in PostgreSQL?
PostgreSQL is an open-source relational database management system with a variety of features that allow users to operate database operations and improve the speed of queries.
One of these options is the ability to write stored procedures, the SQL code that logically embeds logic for a more organized and maintained program. Here we should discuss the fundamental form of the SELECT procedure in PostgreSQL and how to state queries correctly.
Understanding Stored Procedures
Stored procedures in PostgreSQL are blocks of statement codes that can accept input data. They can execute database operations and return result sets. The procedures of this type are saved and run by a constant server, which means that traffic is decreased and security is enhanced. Fromm writing of stored procedures produces code reuse, high maintainability level, and increased SQL query performance.
Writing a Basic SELECT Procedure
So then, we are going to explore the SELECT procedure in Postgres. We’ll create a procedure that retrieves employee information from a hypothetical “employees” table based on a specified department ID.
Step 1: Create Example Table
Let’s create a table for example named “employees” and populate it with some sample data. Then, we’ll write a SELECT procedure to retrieve employee records.
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
employee_name VARCHAR(100),
department_id INTEGER
);
INSERT INTO employees (employee_name, department_id) VALUES
('John Doe', 1),
('Jane Smith', 2),
('Michael Johnson', 1),
('Emily Davis', 2),
('Chris Wilson', 1);
Step 1: Define the Procedure
First, define the procedure by specifying its name, input parameters (if any), and return type. In this example, we’ll name our procedure “get_employees_by_department” and pass a department ID as an input parameter.
CREATE OR REPLACE PROCEDURE get_employees_by_department(dept_id INTEGER)
LANGUAGE SQL
AS $$
Step 2: Write the SQL Logic
Next, write the SQL logic within the procedure body to perform the desired database operations. In our case, we’ll use a SELECT statement to retrieve employee records based on the provided department ID.
DECLARE
employees_cursor CURSOR FOR
SELECT * FROM employees WHERE department_id = dept_id;
employee_record employees%ROWTYPE;
BEGIN
OPEN employees_cursor;
LOOP
FETCH employees_cursor INTO employee_record;
EXIT WHEN NOT FOUND;
-- Process each employee record (e.g., print or return)
-- Example: PRINT employee_record.employee_name;
END LOOP;
CLOSE employees_cursor;
END;
$$;
Step 3: Finalize the Procedure
Finally, complete the procedure definition by closing the procedure block and specifying the appropriate access privileges.
CREATE OR REPLACE PROCEDURE get_employees_by_department(dept_id INTEGER)
LANGUAGE SQL
AS $$
DECLARE
employees_cursor CURSOR FOR
SELECT * FROM employees WHERE department_id = dept_id;
employee_record employees%ROWTYPE;
BEGIN
OPEN employees_cursor;
LOOP
FETCH employees_cursor INTO employee_record;
EXIT WHEN NOT FOUND;
-- Print employee details
RAISE NOTICE 'Employee ID: %, Name: %, Department ID: %',
employee_record.employee_id,
employee_record.employee_name,
employee_record.department_id;
END LOOP;
CLOSE employees_cursor;
END;
$$;
Executing the Procedure
To execute the SELECT procedure, simply call it with the appropriate parameter value(s). For example:
CALL get_employees_by_department(1);
This will retrieve employee records belonging to department ID 1 and perform the specified logic within the procedure.
Examples of Normal Select Procedure in PostgreSQL
Example 1: Procedure to Select All Columns from the “employees” Table
CREATE OR REPLACE PROCEDURE select_all_employees()
LANGUAGE plpgsql
AS $$
BEGIN
EXECUTE 'SELECT * FROM employees;';
END;
$$;
Example 2: Procedure to Select Specific Columns with a Condition
CREATE OR REPLACE PROCEDURE select_employees_by_department(dept_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
EXECUTE 'SELECT employee_id, first_name, last_name FROM employees WHERE department_id = $1;' USING dept_id;
END;
$$;
Example 3: Procedure to Use Aggregate Functions
CREATE OR REPLACE PROCEDURE count_employees_per_department()
LANGUAGE plpgsql
AS $$
BEGIN
EXECUTE 'SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;';
END;
$$;
To execute these procedures, you can call them like regular functions:
-- Call the procedure to select all employees
CALL select_all_employees();
-- Call the procedure to select employees by department
CALL select_employees_by_department(10);
-- Call the procedure to count employees per department
CALL count_employees_per_department();
These procedures encapsulate the corresponding queries and provide a convenient way to retrieve information from the database. By calling them with the appropriate parameters, you can efficiently select all employees, filter employees by department, and count employees per department.
Conclusion
Writing a basic SELECT procedure in PostgreSQL enables you to encapsulate custom database logic, promote code reusability, and enhance query performance. By following the steps outlined in this article, you can create stored procedures tailored to your specific requirements and streamline database operations effectively. As you become more familiar with PostgreSQL stored procedures, you’ll unlock even greater potential for optimizing your database workflows and driving business value.
Contact Us