Stored procedures are a set of SQL commands used to perform some operation. Typically, repeated operations are packed into a stored procedure for repeated use and to save time. Stored procedures can be called whenever they are needed.
In addition to standard SQL syntax, stored procedures provide support for various languages in PostgreSQL. We can also increase customization by passing arguments to a stored procedure.
The syntax of creating a stored procedure was updated in version 11. The following is the updated syntax to create a stored procedure.
CREATE [ OR REPLACE ] PROCEDURE name_of_procedure([argmode argname argtyped {DEFAULT | =} default_expr ] [, ...])LANGUAGE lang_nameAS$$ BEGINcommand1;command2;...END $$;
The CREATE PROCEDURE command creates a stored procedure in the PostgreSQL database.
name_of_procedure is the name used to call the procedure.
After the name_of_procedure, the arguments can be passed inside the parathesis ( ). The parameters of an argument are as follows:
argmode is the mode of the argument.argname is the name of the argument.argtype is the type of the argument.{DEFAULT | =} default_expr is used to set the default value of the parameter.We can select the
LANGUAGE keyword.
After the AS statement, the commands to execute are packed between the ＄＄ BEGIN and END ＄＄ statements.
To call a stored procedure, use the following command:
CALL procedure__name();
Let’s have a complete code example.
/* Creating StudentRecord table */CREATE TABLE StudentRecord (RegID SERIAL PRIMARY KEY,Name varchar(100) NOT NULL,Age varchar(3),Gender varchar(10),City varchar(100));/* Inserting records in StudentRecord table */INSERT INTO StudentRecord(Name, Age, Gender , City)VALUES('George','20', 'Male', 'London'),('Emma','22', 'Female', 'Manchester'),('Harry','15', 'Male', 'Cambridge'),('Ava','17', 'Female', 'Manchester'),('Olivia','25', 'Female', 'Manchester'),('Thomas','23', 'Male', 'Cambridge');/* Displaying all records from the table */SELECT * FROM StudentRecord;/* Creating stored procedure to insert data */CREATE PROCEDURE DataIn(IN _name text,IN _age text DEFAULT NULL,IN _gender text DEFAULT NULL,IN _city text DEFAULT NULL)LANGUAGE plpgsqlAS $$BEGININSERT INTO StudentRecord(Name, Age, Gender , City)VALUES(_name, _age, _gender, _city);END$$;/* Calling stored procedure to insert record */CALL DataIn('Ali', '18', 'Male', 'Lahore');/* Displaying all records to verify insertion */SELECT * FROM StudentRecord;
Lines 2-8: We create a table StudentRecord using the CREATE TABLE query.
Lines 11-19: We insert some records using the INSERT query.
Line 22: We use the SELECT query to see the records of the table.
Lines 25-37: We create the stored procedure DataIn to insert data into the table.
Line 40: We call the procedure DataIn to insert data into the StudentRecord table.
Line 43: We use the SELECT query to verify the added record in the table using the stored procedure.
For earlier versions of PostgreSQL, we can use the following syntax:
CREATE [ OR REPLACE ] PROCEDURE procedure__nameRETURNS return__typeAS $$BEGINstatement(s);END;$$ LANGUAGE language__name;
Free Resources