Example of Stored Procedures in PostgreSQL

A stored procedure is a set of Structured Query Language (SQL) statements with an assigned name, which is stored in a relational database management system as a group, so it can be reused as many time is called. Aditional is not mandatory that the stored procedure have an output.

I am going to provide an example to understand how it works, in this example, we have one table in which we save the enrollment of some students to one group of some subjects.

CREATE TABLE inscription(
    cod_student INTEGER NOT NULL,
    cod_subject SMALLINT NOT NULL,
    group SMALLINT NOT NULL
);

We need to create a stored procedure that allows us to know which is the group with the least amount of students enrolled to enrol the new student that we are providing.

In this case, we have 2 groups for the subject 1001,

select cod_subject, group, count(*) 
  from inscription i2  
  where cod_subject = 1001

And we will create our stored procedure like this.

create or replace procedure assign_group(
   student int,
   subject int
)  
language plpgsql    
as $$
declare
    chosenGroup int;
begin

    chosenGroup := (select grupo from (select cod_a,grupo,count(*) 
                from inscribe i2  
                    where cod_a = asignatura group by cod_a , grupo 
                order by count(*) asc limit 1 ) gru_men);


    insert into inscription(cod_student , cod_subject , group) 
               values (student, subject, chosenGroup );

    commit;
end;$$

So let's define the syntax:

1. specify the name of the stored procedure after the create procedure keywords.
2. define parameters for the stored procedure. A stored procedure can accept zero or more parameters.
3. specify plpgsql as the procedural language for the stored procedure (you could use other procedural languages for the stored procedure).
4. use the dollar-quoted string constant syntax to define the body of the stored procedure.

Finally, when I call the procedure (call assign_group(1457855, 1001)) the new student will be enrolled in group 2 because has the least amount of students.

I hope this will be useful and Thank you so much for reading.