How stored procedures work in PostgreSQL

Introduction to stored procedures

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.

Syntax

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_name
AS 
$$ BEGIN
    command1;
    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 supported languagesThese can be SQL, C, internal, or the name of any user-defined procedural language, e.g., pgsql or plpgsql, etc. following 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();

Code

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 plpgsql 
AS $$ 
BEGIN 
  INSERT 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;

Explanation

  • 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 all PostgreSQL versions before version 11

For earlier versions of PostgreSQL, we can use the following syntax:

CREATE [ OR REPLACE ] PROCEDURE procedure__name 
RETURNS return__type
AS $$
BEGIN
  statement(s);
END;
$$ LANGUAGE language__name;

Free Resources

Copyright ©2026 Educative, Inc. All rights reserved