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.

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)

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.

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 PROCEDURE square(INOUT x int) AS $$
BEGIN
    x := x^x;
END;
$$ LANGUAGE plpgsql;



CALL square(2);

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

Now create a new table named procedure_tbl.

CREATE TABLE procedure_tbl(id int, procedure_name varchar, execution_time float);

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

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

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.

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);

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();

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:

• Postgresql date between two dates
• How to create a view in PostgreSQL
• PostgreSQL Min With Examples
• Postgresql create user with password
• How to set user password in PostgreSQL
• Postgresql ilike case insensitive

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