Introduction to PostgreSQL Stored Procedures
PostgreSQL stored procedures allow us to extend the database’s functionality by creating the user-defined functions using the 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. We can insert the commit and rollback statement in our procedure, also stored procedure is used to convert into other databases or other databases to PostgreSQL database. 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 is used to replace the existing stored procedure with the same name and create a new parameter list.
- Stored procedure name: Stored procedure name is defined as the name of the stored procedure which we are creating.
- Mode of argument: This parameter is defined as which mode we are using 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: This is defined as the name of the data type, which we have defined as 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: This parameter is defined as a set of configuration parameter which we have used at the time of stored procedure creation.
- Object file: This is nothing but the shared library, which contains 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 stored procedure in PostgreSQL is to create a user-defined function; it does not allow to execute of transaction under the function.
- To overcome the drawback of executing the transaction under the function, we have created a stored procedure.
- We have executed the transaction in a stored procedure by creating the stored procedure.
- To create a new stored procedure, we are using a create procedure statement in it.
- 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 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 is always ended 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.
- It is created using create a statement and call the stored procedure using the call keyword. Also, we have drop the stored procedure using a drop statement.
Examples
Given below are the examples mentioned:
We are using 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
This is a guide to PostgreSQL Stored Procedures. Here we discuss how stored procedures work in PostgreSQL with programming examples, respectively. You may also have a look at the following articles to learn more –
