Creating stored procedures with INOUT parameters

Sometimes, you may want to return values fromstored procedures. To achieve this, you can use thecreate procedure statement withINOUT parameters.

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

create or replace procedure sp_name(    inout parametertype, ...)as$$   -- body$$language plpgsql;

Calling stored procedures with INOUT parameters

To call a stored procedure, you use thecall statement without providing theINOUT parameters:

call sp_name();

If you call a stored procedure withINOUT parameters in ananonymous block, you need to pass arguments to the stored procedure call as follows:

do$$   declare      v_name1type;      v_name2type;   begin      -- call the stored procedure with inout parameters      call sp_name(v_name1, v_name2);      -- process v_name1, v_name2   end;$$;

PostgreSQL Stored Procedures with INOUT parameter examples

Let’s take some examples of creating stored procedures withINOUT parameters. We’ll use thefilm 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 thefilm table:

create or replace procedure count_film(    inout total_filmint default 0)as$$begin    select count(*)from film    into total_film;end;$$language plpgsql;

Second, call the stored procedure without providing thetotal_film parameter:

call count_film();

Output:

total_film------------       1000(1 row)

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

do$$declare   total_filmint = 0;begin   call count_film(total_film);   raise notice'Total film: %', total_film;end;$$;

Output:

NOTICE:  Total film:1000

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_filmint default 0,   inout total_lengthint default 0,   inout avg_rental_ratenumeric(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;

Second, call the stored procedurefilm_stat():

call film_stat();

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

Output:

total_film | total_length | avg_rental_rate------------+--------------+-----------------       1000 |       115272 |            2.98(1 row)

Summary

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

Last updated onApril 19, 2024

On this page

• Creating stored procedures with INOUT parameters
• Calling stored procedures with INOUT parameters
• PostgreSQL Stored Procedures with INOUT parameter examples
• Summary