PostgreSQL Procedures

Updated May 3, 2023

Introduction to PostgreSQL Procedure

PostgreSQL Procedures and its functionality are added to PostgreSQL 11. It has provided all functionalities of the procedure in PostgreSQL 11. Before the PostgreSQL procedure’s invention, we used the PostgreSQL function. In the process, 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 transactions inside the procedure code. Using the PostgreSQL stored procedure, we can create our custom functions 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 for 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 the old procedure. If users can explain the procedure, then they must have minimum usage privileges on the language.
• If a schema name is defined when 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 who creates the procedure will be the owner of this procedure. For creating a new procedure, the user must 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 an argument. It currently supports IN and INOUT argument modes.
• Argument name: Name of an argument.
• Argument type: Argument type can be a data type of procedure argument.
• Default expression: Expression is used as a default value if a specific parameter is not specified.
• Language name: Name of the language in which we implement the procedure.
• Configuration parameter: Set of configuration parameters 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 a 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 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. A set of commands that must be executed in a particular order or sequence constitutes the system’s working. The system is designed to operate across all 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. The body column always ends with a semicolon (;) after the END keyword. PostgreSQL procedure block has an optimal label ($$) located at the beginning and end of the procedure.

Examples to Implement PostgreSQL Procedures

Below is an 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 a 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:

• In the declaration section, we declare all variables used within the body section. Every statement in the declaration section was terminated with a semicolon.
• The PostgreSQL method body section contains the actual code, and we used semicolons to end each sentence in the body section.

Example #3 – Calling PostgreSQL Procedure

For the execution, we need to call the same using a call statement.

Syntax:

# Call procedure_name();
Postgres# call testing();

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

The developers introduced this important and desirable feature in version 11. It proves handy in migrating databases from other systems into PostgreSQL. This procedure utilizes transaction control language to commit and rollback transactions.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL Procedures” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. Oracle Procedures
2. BETWEEN in Oracle
3. Oracle Triggers
4. Natural Join in Oracle