Introduction to PostgreSQL Procedure
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 the 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 the create procedure statement.
Syntax
Below is the syntax of creating a 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 can define the procedure, then they must have minimum usage privilege on the language.
- If a schema name is defined at the time of creating a new procedure, it is created in that specified schema; otherwise, it is created in the current schema.
- To change the old procedure’s definition, it is possible using CREATE or REPLACE PROCEDURE, But using this way, we cannot change arguments or their types of procedures.
- The user which creates the procedure will be the owner of this procedure. For creating a new procedure, the user must have to create privilege and usage privileges on argument types.
- When we issue creating and replacing procedure statements on the existing procedure, the ownership and permissions of that procedure remain the same; it doesn’t change.
PostgreSQL Procedure syntax Parameters
- 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 as a 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: The object file is the shared library that contains the compiled C procedure.
- Link Symbol: Procedure link symbol.
Working of PostgreSQL Procedures
It will allow writing procedures like other databases (ORACLE, MYSQL, and MSSQL). The procedure is almost working the same as the function, but the difference is that function returns a value, and the procedure doesn’t return value.
To display the list of created procedures in the 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 the PostgreSQL procedure and the 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
The Declaration section is optimal or no need to define,, but the body section is required to define in the PostgreSQL procedure. Body column is always ended with a 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 a 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 block body’s EXIT statement 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
Output:
- 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.
- The 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;
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
It was introduced in version 11. It is one of the important and desirable features in version 11. This feature is handy 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 the PostgreSQL procedure. You may also have a look at the following articles to learn more –
