How To Create A Stored Procedure With Parameter Modes IN INOUT OUT Modes...

How To Create A Stored Procedure With Parameter Modes IN INOUT OUT Modes In Postgresql || PL/pgSQL

-- purpose is to create a stored procedure with parameter mode (IN, INOUT) and call it

-- IN Only Input and it is default

-- OUT Only Output, only OUT is not allowed in Stored Procedure in PostgreSQL

-- INOUT Input + Output

In this video tutorial, you’ll learn how to create stored procedures in PostgreSQL using different parameter modes: IN, INOUT, and OUT. These parameter modes are crucial for controlling the flow of data within your stored procedures and can greatly enhance the flexibility and power of your database functions.

We start by explaining the purpose of each parameter mode:

• IN: This mode allows you to pass data into the procedure.
• OUT: This mode is used to return data from the procedure.
• INOUT: This mode allows a parameter to be passed in and modified within the procedure, then returned.

After covering the basics, we dive into practical examples where you’ll see how to implement these modes in a stored procedure using PL/pgSQL. You’ll also learn how to call these procedures from pgAdmin and SQL Shell (psql), ensuring you can apply these techniques in real-world scenarios.

This tutorial is designed for database administrators, developers, and anyone interested in advancing their PostgreSQL knowledge. By the end of this video, you’ll have a solid understanding of how to use parameter modes effectively within your stored procedures, allowing for more dynamic and reusable database functions.

PostgreSQL, stored procedure, parameter modes, IN parameter, OUT parameter, INOUT parameter, PLpgSQL, PostgreSQL tutorial, database management, SQL commands, pgAdmin

CREATE OR REPLACE PROCEDURE public.testing_procedure(p_num1 IN numeric,p_num2 IN numeric, p_sum INOUT numeric)

LANGUAGE 'plpgsql'

AS $BODY$

DECLARE

BEGIN

p_sum := p_num1 + p_num2;

END;

$BODY$;


CALL public.testing_procedure(13,10,-1);


CREATE OR REPLACE PROCEDURE public.testing_procedure(p_num1 IN numeric,p_num2 IN numeric, p_sum INOUT numeric,p_mult INOUT numeric)

LANGUAGE 'plpgsql'

AS $BODY$

DECLARE

BEGIN

p_sum := p_num1 + p_num2;

p_mult := p_num1 * p_num2;

END;

$BODY$;


CALL public.testing_procedure(3,0,-1,-1);