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.

output data3

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.

Article Tags :

Databases

PostgreSQL