Updated May 17, 2023
Introduction to PostgreSQL Stored Procedures
PostgreSQL stored procedures allow us to extend the database’s functionality by creating the user-defined functions using various languages; it is called a stored procedure in PostgreSQL. A stored procedure is beneficial and important to create our own user-defined functions after creating the function we are using later in applications. You can insert the commit and rollback statements in your procedure. Moreover, stored procedures serve the purpose of converting data between PostgreSQL and other databases. The stored procedure does not return any value, or it will return one or more result set.
Syntax of PostgreSQL Stored Procedures
Given below is the syntax:
1. Syntax of creating a stored procedure in PostgreSQL
CREATE [OR REPLACE] PROCEDURE stored_procedure_name
( [ [ mode_of_argument ] [argument_name] argument_type [ { DEFAULT | } default_expression ] [……………. ,]]}
{ LANGUAGE language_name | TRANSFORM { FOR TYPE type_name } [ …..,] | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
| SET config_parameter { TO value | = value | FROM CURRENT } | AS 'definition'
| AS 'object_file', 'link_symbol'
}2. Syntax of call stored procedure in PostgreSQL.
Call stored_procedure_name(parameter list); -- stored Procedure name which we have calling.3. Syntax of drop/delete stored procedure in PostgreSQL.
Drop procedure stored_procedure_name();Below is the parameter description:
- Create or replace procedure: This is defined as creating a new stored procedure. Replace keyword replaces the existing stored procedure with the same name and creates a new parameter list.
- Stored procedure name: The stored procedure name is the name given to the procedure being created.
- Mode of argument: This parameter defines which mode we use to create a new stored procedure.
- Argument name: This is defined as the name of the argument which we have used while creating a stored procedure.
- Argument type: The argument type refers to the data type assigned to a stored procedure variable.
- Language name: This parameter is defined as the name of the language we have used at stored procedure creation. Basically, we are using pl/pgsql language to create a stored procedure.
- Config parameter: We define this parameter as a set of configuration parameters used during the creation of a stored procedure.
- Object file: This is nothing but the shared library containing the compiled C procedure language.
- Call: This keyword is used to call the stored procedure in PostgreSQL. We can call a single stored procedure multiple time.
- Drop: Using the drop keyword, we have dropped the stored procedure.
How Stored Procedures work in PostgreSQL?
- The main use of stored procedure in PostgreSQL is to create a user-defined function; it does not allow to execute of transaction under the function.
- We have created a stored procedure to overcome the drawback of executing the transaction under the function.
- By building the stored procedure, we have carried out the transaction in a stored procedure.
- To build a new stored procedure, we are utilising a create procedure statement.
- We cannot use commit and rollback in the function; we have used this command in a stored procedure.
- It does not return any value; if we want to end the stored procedure, we use the RETURN keyword without expressions.
- If we want to return the value from the stored procedure, we need to use an output parameter in it. The final result of the output parameter will be returned to the caller who was calling the stored procedure.
- It contains the two-block sections, that are the declaration and body.
- The Declaration section is optimal or no need to define, but the body section must be defined in a stored procedure.
- The body section always ends with a semicolon (;) after the END keyword in a stored procedure.
- This block is an optimal label ($$) located at the beginning and ending of the stored procedure.
- To create a stored procedure, we use the CREATE statement, and to invoke or execute the stored procedure, we use the CALL keyword. Also, we have drop the stored procedure using a drop statement.
Examples
Given below are the examples mentioned:
We are using the proc_test table to describe an example of a stored procedure in PostgreSQL as follows.
Below is the data description of the proc_test table.
Code:
\d+ proc_test;
select * from proc_test;Output:
Example #1
Create stored procedure using transaction control.
The below example shows that create a new procedure in PostgreSQL are as follows. We have to create the procedure name as sp_test.
Code:
CREATE OR REPLACE PROCEDURE sp_test ()
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO proc_test (company_id, name, address, phone, country) VALUES (1, 'PQR', 'Mumbai', '1234567890', 'India');
INSERT INTO proc_test (company_id, name, address, phone, country) VALUES (2, 'ABC', 'Pune', '1234567890', 'India');
COMMIT;
INSERT INTO proc_test (company_id, name, address, phone, country) VALUES (3, 'XYZ', 'Pune', '1234567890', 'India');
ROLLBACK;
END;
$$;Output:
Example #2
Create stored procedure without using transaction control.
The below example shows that create a stored procedure without using the transaction control.
Code:
CREATE OR REPLACE PROCEDURE sp_test1 ()
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO proc_test (company_id, name, address, phone, country) VALUES (11, 'ABC', 'Mumbai', '1234567890', 'India');
INSERT INTO proc_test (company_id, name, address, phone, country) VALUES (12, 'PQR', 'Pune', '1234567890', 'India');
END;
$$;Output:
Example #3
Drop stored procedure.
We are dropping a stored procedure name as sp_test1.
Code:
\df
drop procedure sp_test1;Output:
Example #4
Call stored procedure.
We are calling a stored procedure name as sp_test.
Code:
call sp_test();
select * from proc_test;Output:
Recommended Articles
We hope that this EDUCBA information on “PostgreSQL Stored Procedures” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
