Introduction to PostgreSQL Procedures
PostgreSQL procedure and its functionality are added to PostgreSQL 11. It has provided all functionalities of procedure in PostgreSQL 11. Before the invention of the PostgreSQL procedure, we were using PostgreSQL function. In the function, we cannot run a transaction. Inside the function body, we neither commit transactions nor open new ones. It will overcome this drawback from function, now we run transaction inside procedure code. Using the PostgreSQL stored procedure we can create our own custom function and reuse them in applications as part of different database workflows. While creating new procedures we need to specify create procedure statement.
Syntax
Below is the syntax of create procedure in PostgreSQL:
CREATE [OR REPLACE] PROCEDURE 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’
}
Syntax Description:
- First, specify the create procedure that defines create or replace old procedure. If users are able to define procedure then they must have minimum usage privilege on the language.
- If a schema name is defined at the time of the creation of a new procedure it is created in that specified schema otherwise it is created in the current schema.
- To change the definition of old procedure it is possible using CREATE or REPLACE PROCEDURE, But using this way we cannot change arguments or its types of procedures.
- The user which creates procedure it will be the owner of this procedure. For creating new procedure user must have to create privilege and usage privileges on argument types.
- When we issue to create and replace procedure statements on the existing procedure, the ownership and permissions of that procedure remain the same it doesn’t change.
PostgreSQL Procedure syntax Parameters
- Procedure Name: Name of procedure.
- Mode of argument: Mode of and argument. It currently supports IN and INOUT argument mode.
- Argument name: Name of an argument.
- Argument type: Argument type can be a data type of procedure arguments.
- Default expression: Expression is used default value if a specific parameter was not specified.
- Language name: Name of language that we implement the procedure.
- Configuration parameter: Set of configuration parameter like value, definition etc.
- Value: Create a procedure statement that will be executed after applying value to the procedure.
- Definition: Definition of the procedure.
- Object file: Object file is the shared library contains compiled C procedure.
- Link Symbol: Procedure link symbol.
Working of PostgreSQL Procedures
PostgreSQL procedure will allow writing procedures like other databases (ORACLE, MYSQL, and MSSQL). The procedure is almost working the same as function but the difference is that function returns value and procedure doesn’t return value.
To display the list of created procedures in PostgreSQL database using the following commands:
Postgres# \df
Transaction control allows commit and rollback inside the PostgreSQL procedure. But before the version 11 PostgreSQL function does not allow to commit and rollback inside the function, this is the main difference between PostgreSQL procedure and PostgreSQL function. We can execute a PostgreSQL procedure using the “call” statement. We can alter and drop procedures using alter and drop statements. The working is essentially a set of commands to be executed in a particular order or sequence. It was allowed to operate across all the transactions.
It has two block sections.
- Declaration
- Body
Declaration section is optimal or no need to define but body section is required to define in PostgreSQL procedure. Body column is always ended with semicolon (;) after the END keyword. PostgreSQL procedure block has an optimal label ($$) located at the beginning and ending of the procedure.
Examples to Implement PostgreSQL Procedures
Below is the example of creating new procedure as follows:
Example #1 – Create Procedure
Code:
CREATE OR REPLACE PROCEDURE testing ()
LANGUAGE plpgsql
AS $$
DECLARE
BEGIN
CREATE TABLE customer (cust_id INT GENERATED BY DEFAULT AS IDENTITY, cust_name VARCHAR (50) NOT NULL, cust_balance DEC (15, 2) NOT NULL, PRIMARY KEY (cust_id));
INSERT INTO customer (cust_id, cust_name, cust_balance) VALUES (1,'ABC', 50000);
INSERT INTO customer (cust_id, cust_name, cust_balance) VALUES (2,'PQR', 60000);
COMMIT;
CREATE TABLE customer_rollback (cust_id INT GENERATED BY DEFAULT AS IDENTITY, cust_name VARCHAR (50) NOT NULL, cust_balance DEC (15, 2) NOT NULL, PRIMARY KEY (cust_id));
INSERT INTO customer_rollback (cust_id, cust_name, cust_balance) VALUES (3,'XYZ', 50000);
INSERT INTO customer_rollback (cust_id, cust_name, cust_balance) VALUES (4,'ABC', 60000);
ROLLBACK;
END $$;
Output:
- In the above example, the name of the stored procedure is testing. We have used language for a stored procedure in pl/pgsql.
- Transaction control also provides another language like PL/TCL, PL/Python, and PL/Perl, etc.
Syntax of the above control language is as follows:
1. PL/Python
plpy.commit()
plpy.rollback()
2. PL/Tcl
Commit
rollback
3. PL/Perl
spi_commit()
spi_rollback()
- we use block labels in case we want to specify in the EXIT statement of the block body or if we want to qualify the names of variables declared in this block.
- It declares section, where we declare all variables used within the body section.
- We can also alter the procedure using the alter statements.
Example #2 – Declaration of variables
Code:
DECLARE
Id INTEGER: = 1;
name VARCHAR (50):= 'ABC';
city VARCHAR (50) := 'NewYork';
amount NUMERIC(11, 2) := 20000.5;
BEGIN
- The declaration section is where we declare all variables used within the body section. Every statement in the declaration section was terminated with a semicolon.
- PostgreSQL procedure body section is where we place the actual code. Every statement in the body section also terminated with a semicolon.
Example #3 – Calling PostgreSQL Procedure
For the execution, we need to call the same using a call statement.
Syntax:
# Call procedure_name();
Code:
Postgres# call testing();
Output:
Code:
select * from customer;
Output :
Example #4 – Drop Procedure in PostgreSQL
We can drop the procedure using the below command.
Syntax:
# drop procedure procedure_name();
Code:
Postgres# drop procedure testing();
Output:
Conclusion
PostgreSQL procedure was introduced in version 11. It is one of the important and desirable features in version 11. This feature is very useful in database migration from other databases into PostgreSQL. Transaction control language is used in this procedure to commit and rollback the transactions.
Recommended Articles
This has been a guide to PostgreSQL Procedures. Here we discuss the introduction, working, and different examples of PostgreSQL procedure. You may also have a look at the following articles to learn more –
