PostgreSQL Stored Procedure with INOUT Parameters

Summary: in this tutorial, you will learn how to create PostgreSQL stored procedures with INOUT parameters.

Creating stored procedures with INOUT parameters

Sometimes, you may want to return values from stored procedures. To achieve this, you can use the create procedure statement with INOUT parameters.

Here’s the basic syntax for creating a stored procedure with INOUT parameters:

create or replace procedure sp_name(
    inout parameter type, ...
)
as
$$
   -- body
$$
language plpgsql;Code language: SQL (Structured Query Language) (sql)

Calling stored procedures with INOUT parameters

To call a stored procedure, you use the call statement without providing the INOUT parameters:

call sp_name();Code language: SQL (Structured Query Language) (sql)

If you call a stored procedure with INOUT parameters in an anonymous block, you need to pass arguments to the stored procedure call as follows:

do
$$
   declare 
      v_name1 type;
      v_name2 type;
   begin
      -- call the stored procedure with inout parameters
      call sp_name(v_name1, v_name2);

      -- process v_name1, v_name2
   end;
$$;Code language: SQL (Structured Query Language) (sql)

PostgreSQL Stored Procedures with INOUT parameter examples

Let’s take some examples of creating stored procedures with INOUT parameters. We’ll use the film table in the sample database for the demonstration:

1) Basic PostgreSQL stored procedures with INOUT parameter example

First, create a stored procedure that counts the number of rows from the film table:

create or replace procedure count_film(
    inout total_film int default 0
) 
as 
$$
begin
    select count(*) from film
    into total_film;
end;
$$
language plpgsql;Code language: SQL (Structured Query Language) (sql)

Second, call the stored procedure without providing the total_film parameter:

call count_film();Code language: SQL (Structured Query Language) (sql)

Output:

 total_film
------------
       1000
(1 row)Code language: SQL (Structured Query Language) (sql)

Third, call the stored procedure count_film() in an anonymous block:

do
$$
declare
   total_film int = 0;
begin
   call count_film(total_film);
   raise notice 'Total film: %', total_film;
end;
$$;Code language: SQL (Structured Query Language) (sql)

Output:

NOTICE:  Total film: 1000Code language: SQL (Structured Query Language) (sql)

2) Creating stored procedures with multiple INOUT parameters

First, create a new stored procedure that retrieves the film statistics including film count, total length, and average rental rate:

create or replace procedure film_stat(
   inout total_film int default 0,
   inout total_length int default 0,
   inout avg_rental_rate numeric(4,2) default 0
)
as
$$
begin
  select count(*) into total_film
  from film;
  
  select sum(length) into total_length
  from film;
  
  select round(avg(rental_rate),2) into avg_rental_rate
  from film;  
end;
$$
language plpgsql;Code language: SQL (Structured Query Language) (sql)

Second, call the stored procedure film_stat():

call film_stat();Code language: SQL (Structured Query Language) (sql)

Since all the parameters in the film_stat() stored procedure are the inout parameters, you don’t need to pass any parameters.

Output:

 total_film | total_length | avg_rental_rate
------------+--------------+-----------------
       1000 |       115272 |            2.98
(1 row)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the INOUT parameters to return values from stored procedures in PostgreSQL.