How do you create and use stored procedures and functions in PostgreSQL?

Stored procedures and functions are database objects that are used to encapsulate and execute a series of database operations. In PostgreSQL, stored procedures and functions can be created and used to simplify complex operations or business logic that require multiple SQL statements executed sequentially.

To create a stored procedure or function in PostgreSQL, you need to follow these steps:

1. Connect to your PostgreSQL database using a PostgreSQL client such as psql or pgAdmin.

2. Create a function using the CREATE FUNCTION statement. This statement includes the name of the function, list of parameters, and the function body. Below is an example:

CREATE FUNCTION get_employee_salary(emp_id INT) RETURNS NUMERIC AS $$
DECLARE
    salary NUMERIC;
BEGIN
    SELECT salary INTO salary FROM employees WHERE id = emp_id;
    RETURN salary;
END;
$$ LANGUAGE plpgsql;

In this example, the function ‘get_employee_salary‘ takes an input parameter ‘emp_id‘ of type ‘INT‘ and returns ‘NUMERIC‘ data type. The function retrieves the salary of an employee with the given ‘id‘ from the ‘employees‘ table and returns it.

3. Once the function is created, you can call it using the SELECT statement. Below is an example:

SELECT get_employee_salary(1001);

This will return the salary of the employee with ‘id‘ 1001.

Now let’s look at how to create a stored procedure:

1. Connect to your PostgreSQL database using a PostgreSQL client such as psql or pgAdmin.

2. Create a stored procedure using the CREATE PROCEDURE statement. This statement includes the name of the procedure, list of parameters, and the procedure body. Below is an example:

CREATE PROCEDURE update_employee_salary(emp_id INT, new_salary NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees SET salary = new_salary WHERE id = emp_id;
END;
$$;

In this example, the stored procedure ‘update_employee_salary‘ takes two input parameters ‘emp_id‘ and ‘new_salary‘, both of which are of type ‘INT‘ and ‘NUMERIC‘ respectively. The procedure updates the salary of an employee with the given ‘id‘ to the new salary value.

3. Once the procedure is created, you can call it using the CALL statement. Below is an example:

CALL update_employee_salary(1001, 75000.00);

This will update the salary of the employee with ‘id‘ 1001 to 75000.00.

In summary, stored procedures and functions in PostgreSQL make it possible to encapsulate complex database operations or business logic. They can be created and used easily using PostgreSQL client tools such as psql or pgAdmin, and the PL/pgSQL language provides a powerful and flexible way to define them.