Create a stored procedure in PostgreSQL

In this Postgresql tutorial, we will learn about “how to create a stored procedure in PostgreSQL” and we will cover the following topics.

  • What is stored procedure and its syntax?
  • How to define input and output arguments in stored procedure
  • The variables and constants in PL/pgSQL
  • Create stored procedure in postgresql using pgadmin
  • Create insert stored procedure in postgresql
  • Create select stored procedure in postgresql
  • Create stored procedure in postgresql 12
  • Create stored procedure in postgresql 10
  • Create stored procedure in postgresql 11
  • Create stored procedure in postgresql 9.6

PostgreSQL stored procedure and its syntax

We can extend the functionality of the Postgresql database using the various procedural languages, which is often referred to as a stored procedure.

With help of stored procedures, we can create our own functions that we can call or use wherever we need to use them again and again.

Before Postgresql version 11, stored procedures were created using CREATE FUNCTION statement, but with the Postgresql version 11, are created using CREATE PROCEDURE.

Procedure syntax for Postgresql version before 11:

CREATE FUNCTION function_name(argument1 type,argument2 type)
 RETURNS type AS
BEGIN
  staments;
END;
LANGUAGE 'language_name';

Procedure syntax for Postgresql version 11 or next:

CREATE PROCEDURE procedure_name(argument type)
 RETURNS type AS
BEGIN
  staments;
END;
LANGUAGE 'language_name';

  • function_name or procedure_name: It is the name of function or procedure that we want to create.
  • argument type: It is used to provide a list of arguments with its datatype like boolean, integer, etc into parenthesis of function or procedure.
  • Returns: It is used to specify the return type of the function or procedure.
  • BEGIN and END: Blocks of code are written between BEGIN and END.
  • Language: It is used to specify the procedural language of function or procedure, for Postgresql, it is the plpgsql.

To call created function or procedure, we can use:

SELECT * FROM function_name(val_1); -- for function

CALL procedure_name(val_1); -- for procedure

To call created function or procedure that returns a table, we can use this one:

SELECT function_name(val_1); -- for function

CALL procedure_name(val_1); -- for procedure

To call created function or procedure that is taken one of the variables from a column in a table, we can use this one:

SELECT function_name(val_1); -- for function

CALL procedure_name(val_1); -- for procedure

Also, read, PostgreSQL DATE Format + Examples

How to define input and output arguments in stored procedure?

In the above syntax, we define input argument and their type, and output argument is defined within the RETURN statement, but there is another way to define input and output arguments, that is using IN for input or OUT for output arguments.

When arguments are defined using IN and OUT, then we don’t use the RETURN statement. OUT argument is used when we need to return multiple values.

Let’s understand with an example.

CREATE FUNCTION sum_and_mul(IN x int,IN y int, OUT sum int, OUT mul int) AS $$
BEGIN
    sum := x + y;
    mul := x * y;
END;
$$ LANGUAGE plpgsql;

call the above function where we need to pass only IN argument.

SELECT * FROM sum_and_mul(2,2)

While calling a function or procedure, we need to provide only an input argument.

Create stored procedure in postgresql
Create stored procedure in postgresql

For Postgresql version 11 :

CREATE PROCEDURE sum_and_mul(INOUT x int,INOUT y int) AS $$
BEGIN
    x := x + y;
    y := x * y;
END;
$$ LANGUAGE plpgsql;

In the above code, INOUT in the parenthesis is working for both an input and output argument for CREATE PROCEDURE, and the Postgresql CREATE PROCEDURE does not support OUT for output argument like CREATE FUNCTION.

calling the above procedure where we need to pass only IN argument.

CALL sum_and_mul(2,2)
PostgreSQL stored procedure
PostgreSQL stored procedure

Read, PostgreSQL CASE with Examples

The variables and constants in PL/pgSQL

The variable holds a value whose value can vary through the life of function, before using the variable, we must declare it with data type using the following syntax:

CREATE FUNCTION function_name(arg1 type,arg2 type)
 RETURNS type AS
 DECLARE
  name_of_variable datatype [:= value];
BEGIN
  staments;
END;
LANGUAGE language_name;
CREATE PROCEDURE procedure_name(arg1 type,arg2 type)
 RETURNS type AS
 DECLARE
  name_of_variable datatype [:= value];
BEGIN
  staments;
END;
LANGUAGE language_name;

Constants can not be changed through the function, and it is declared like a variable but with CONSTANT keyword using the following syntax:

CREATE FUNCTION function_name(arg1 type,arg2 type)
 RETURNS type AS
 DECLARE
  name_of_variable CONSTANT datatype [:= value];
BEGIN
  staments;
END;
LANGUAGE language_name;
CREATE PROCEDURE procedure_name(arg1 type,arg2 type)
 RETURNS type AS
 DECLARE
  name_of_variable CONSTANT datatype [:= value];
BEGIN
  staments;
END;
LANGUAGE language_name;

Read PostgreSQL WHERE IN with examples

Create stored procedure in postgresql using pgadmin

Let’s create a stored procedure in Postgresql using pgAdmin using follow instructions.

Open pgAdmin and enter a password, if it asks for.

Browser section expands the icon > in front of Servers (1), Right-click on postgres database from Databases section under Servers (1) and click or select Query Tool option.

Create stored procedure in postgresql using pgadmin
Create stored procedure in postgresql using pgadmin

After doing the above steps, A Query Editor Panel will appear where we can execute the Postgresql query, Let’s create a stored procedure that returns the square of any number using the below code.

Create stored procedure in postgresql using pgadmin
CREATE PROCEDURE square(INOUT x int) AS $$
BEGIN
    x := x^x;
END;
$$ LANGUAGE plpgsql;



CALL square(2);
Create stored procedure in postgresql using pgadmin
Create stored procedure in postgresql using pgadmin

From the above output, we have created a stored procedure named square to calculate the square of any numbers using pgAdmin.

Read PostgreSQL WHERE with examples

Create insert stored procedure in postgresql

In Postgresql, we can create an insert stored procedure that we can call again and again to insert data into a table.

Let’s first create a database named procedure_database.

CREATE DATABASE procedure_database; -- creating database

\c procedure_database -- connecting to procedure_database
Create insert stored procedure in postgresql
Create insert stored procedure in postgresql

Now create a new table named procedure_tbl.

CREATE TABLE procedure_tbl(id int, procedure_name varchar, execution_time float);
Create insert stored procedure in postgresql
Create insert stored procedure in postgresql

Insert the following data using a stored procedure named sample_ins.

CREATE PROCEDURE sample_ins(_id int, _procd_name varchar, _exe_time float) AS $$
BEGIN
   INSERT INTO procedure_tbl VALUES(_id,_procd_name,_exe_time);
END;
$$ LANGUAGE plpgsql;

CALL sample_ins(1,'Robin scheduling',48);
CALL sample_ins(2,'Robin scheduling',92);

SELECT * FROM procedure_tbl
Create insert stored procedure in postgresql
Create insert stored procedure in postgresql

From the above output, we have created a stored procedure named sample_ins to insert data into a table named procedure_tbl.

Read PostgreSQL list databases

Create select stored procedure in postgresql

In Postgresql, we are going to create a stored procedure that will fetch data from a table.

we are going to view that data from a table named procedure_tbl, that we created in the above sub-section of this tutorial.

CREATE FUNCTION selt_() RETURNS varchar
AS $$
DECLARE
proc_name varchar; --declaring the variable named proc_name as varchar
BEGIN 
    SELECT procedure_name INTO proc_name FROM procedure_tbl;
    RETURN proc_name;
END;
$$
LANGUAGE plpgsql;

SELECT * FROM selt_(); -- calling the selt_ procedure
Create select stored procedure in postgresql
Create select stored procedure in postgresql

From the above output, we have created the selt_ procedure that returns procedure_name column data from proceudre_tbl.

Read PostgreSQL Data Types

Create stored procedure in postgresql 11 and 12

As I mentioned above, that CREATE PROCEDURE command will work in PostgreSQL versions 11 and 12.

Let’s create a stored procedure named cubed that is going to return a cube of any number.

CREATE PROCEDURE cubed(INOUT x int) AS $$
BEGIN
    x := x^3;
END;
$$ LANGUAGE plpgsql;



CALL cubed(2);

In the above code, we have created the stored procedure named cubed that returns cube of any number and invoked this procedure using CALL cubed(2), which returns result 8.

Create stored procedure in postgresql 11 and 12
Create stored procedure in postgresql 11 and 12

Read Postgresql date add

Create stored procedure in postgresql 9.6 and 10

We are going to run the above-stored procedure in Postgresql 9.6 and 10, but here we will use the command CREATE FUNCTION because these are older versions, so CREATE PROCEDURE will not work in this Postgresql version.

Let’ create the procedure named cubed again in Postgresql version 9.6, but it will also work on Postgresql version 10.

CREATE FUNCTION cubed(INOUT x int) AS $$
BEGIN
    x := x^3;
END;
$$ LANGUAGE plpgsql;


SELECT * FROM cubed(2);
Create stored procedure in postgresql 9.6 and 10
Create stored procedure in postgresql 9.6 and 10
Create stored procedure in postgresql 9.6 and 10
Create stored procedure in postgresql 9.6 and 10

Let’s check with one more example, here we will create the procedure that will fetch cities names of the USA from a table us_citites.

CREATE OR REPLACE FUNCTION usa() RETURNS table(citi varchar) AS $$
BEGIN
   RETURN QUERY SELECT city FROM us_cities;
END;
$$ LANGUAGE plpgsql;


SELECT citi from usa();
Postgresql procedure usa
Postgresql procedure usa

In the above output, cities like New York, Los Angles, Chicago, etc. These are the cities of the USA that we have fetched using the procedure usa().

You may like the following PostgreSQL tutorials:

So in this tutorial, we have learned about “Create a stored procedure in PostgreSQL” and covered the following topics.

  • What is stored procedure and its syntax?
  • How to define input and output arguments in stored procedure
  • The variables and constants in PL/pgSQL
  • Create stored procedure in postgresql using pgadmin
  • Create insert stored procedure in postgresql
  • Create select stored procedure in postgresql
  • Create stored procedure in postgresql 12
  • Create stored procedure in postgresql 10
  • Create stored procedure in postgresql 11
  • Create stored procedure in postgresql 9.6