Daniel Calbimonte
postgres query

PSQL stored procedures overview and examples

February 14, 2023 by

In this article, we will learn how to create PostgreSQL stored procedures using PSQL.

Introduction to Psql stored procedure

PSQL is the PostgreSQL terminal. If you are familiar with SQL Server, it is similar to the sqlcmd.

The article will contain the following sections:


I am assuming that PostgreSQL is already installed. If it is not, you can follow our article below:

This article supports Windows, but most of the instructions will work on other operating systems as well.

Starting psql to create a stored procedure

To start psql, you need to go to the Windows Menu and select PostgreSQL [version]>SQL Shell (plsql).

SQL Shell menu

The SQL Shell (psql) will ask for the PostgreSQL server name which is the localhost by default. Also, it will ask for the database name. By default, the Postgres database is installed.

In addition, you need to enter the Port number. The default value is 5432.

Finally, the SQL shell will ask for the user’s name and password. By default, the Postgres user is the super user by default and the password is set during the PostgreSQL installation.

SQL Shell login information

If you are not sure about the port used, you can go to the Menu and go to PostgreSQL [version]>PgAdmin to check.


Right-click a database and select the Query Tool option.

PostgreSQL Query Tool

Also, run a query to check the port used using the pg_settings and execute the query.

postgres query

SELECT * FROM pg_settings WHERE name = ‘port’;

The query will display the port used.

pg_settins port information

Hello world example using a psql stored procedure

We will return the PLSQL (SQL Shell) and try a simple example. In this example, we will create a simple stored procedure named hello world.

CREATE PROCEDURE hello_world()
LANGUAGE plpgsql
AS $$
RAISE NOTICE ‘Hello world’;

The syntax is similar to T-SQL. Note that you can select the programming language used. The most common languages used are plpgsql and SQL. However, other languages could be included as well.

We also have the dollar-quoting ($$) after the word AS and at the end of the procedure. We use the dollar quoting to include the body of the procedure without escaping single quotes.

To raise a message, we are using the RAISE NOTICE statement.

RAISE is used to report messages or errors. You can RAISE messages at different levels like DEBUG, LOG, NOTICE, WARNING, etc.

Note that you need a semicolon after the RAISE NOTICE message and another semicolon at the end of the CREATE PROCEDURE statement after the last dollar-quoting.

To call the stored procedure, you can use the CALL statement like this.

call hello_world();

If you are a SQL Server user, we can say that call is similar to the execute (exec) command in T-SQL. Another difference between SQL Server and Postgres is that the stored procedures do not require parenthesis in T-SQL while Postgres requires them.

If everything is OK, the procedure will show a hello world message.

postgresql stored procedure hello world example

Example using a psql stored procedure with variables

The next example is similar to the previous hello_world procedure, but in this example, we will use variables. Let’s take a look at the example.

CREATE PROCEDURE hello(name varchar(30))
LANGUAGE plpgsql
AS $$
RAISE NOTICE ‘Hello %’,name;

The name of the stored procedure is hello. The language used is plpgsql. In this procedure, we are using a variable named name and the data type is varchar(30).

There are some data types similar to SQL Server, but we have other ones different like these:




Autoincremental integer (4 bytes)


A closed geometric path


It is Binary JSON data


For lines in a plane


For circles on a plane


A text search document

For more information about data types in PostgreSQL, refer to this link:

The other difference between plpgsql and T-SQL is the way to concatenate variables.

In T-SQL we usually use the CONCAT function or the + operator to concatenate strings with variables. On the other hand, PLPGSQL uses a syntax similar to C#, C++, and other programming languages.

RAISE NOTICE ‘Hello %’,name;

It uses the % for the variable and then you specify a comma after the single quote and then the name of the variable after.

To call the stored procedure, we use the CALL statement.

call hello(‘Sqlshack’);

In this example, the stored procedure receives a name as a variable and the output is the following:

stored procedure with variables

Example using a psql stored procedure to insert data

The next example will show how to insert data using a stored procedure. To do that we will create a table first.

CREATE TABLE accounts (
user_id integer PRIMARY KEY,
firstname VARCHAR ( 50 ) NOT NULL,
lastname VARCHAR ( 50 ) NOT NULL,

The table name is accounts, the primary key is user_id of type integer and then we have firstname and lastname of type varchar of 50 characters. Finally, we have the email column of type varchar of 255 characters.

The stored procedure used to insert data is the following:

CREATE PROCEDURE add_data(id integer,firstname varchar(50),lastname varchar(50), email varchar(255) )
AS $$
INSERT INTO accounts VALUES (id,firstname,lastname,email);

Note that the variables are written inside the parenthesis and separated by commas.

CREATE PROCEDURE add_data(id integer,firstname varchar(50),lastname varchar(50), email varchar(255) )

Note that the language is SQL. This is because we do not need special commands to insert data. Just simple SQL statements are enough.


Finally, we use the variable in the INSERT statement.

INSERT INTO accounts VALUES (id,firstname,lastname,email);

To insert 2 rows in the accounts table, we will use the following CALL statements.

CALL add_data(1, ‘Daniel’,’Smith’,’dsmith@gmail.com’);
CALL add_data(2, ‘John’,’Rambo’,’jrambo@gmail.com’);

If everything is ok, if we do a select in the accounts table, we will see the new rows inserted:

Select * from accounts

verify the data inserted in postgresql using a stored procedure

Example to modify a psql stored procedure

In T-SQL we use the CREATE OR ALTER statement to create the stored procedure or modify it if it already exists. In Postgres, we use CREATE OR REPLACE instead.

The following example illustrates the usage of the CREATE OR REPLACE statements.

LANGUAGE plpgsql
AS $$
RAISE NOTICE ‘Hello sqlshack’;

In this example, we are replacing the existing hello_world procedure with a new code. The code will say Hello sqlshack instead of hello word. If we call the procedure, the message has changed.

call the hello world procedure in psql

How to drop a psql stored procedure

There is no challenge to dropping PostgreSQL stored procedure. It is the same procedure as dropping in T-SQL with the difference that the semicolon is required, and the procedures have parenthesis.

Drop procedure hello_world();

How to delete data using a psql stored procedure

Finally, we will show how to use the delete statement in psql using a stored procedure.

CREATE OR REPLACE PROCEDURE DeleteAccounts(MyId integer)
LANGUAGE plpgsql
AS $$
DELETE from accounts WHERE user_id=MyId;
END $$;

This time we are using the create or replace statement and the procedure name is DeleteAccounts.

Also, the variable used is MyId which is an integer. The procedure will receive an integer id number and delete the value of the table accounts that match that ID.

CREATE OR REPLACE PROCEDURE DeleteAccounts(MyId integer)

The language will be plpgsql and we will use the variable in the delete statement

DELETE from accounts WHERE user_id=MyId;

Finally, we will call the procedure. In this example, we will remove the row with the user_id equal to 2 in the accounts table.

CALL DeleteAccounts(2);

If we verify the data, column 2 was removed successfully.

select * from accounts;

Verify deleted data with postgres


In this article, we learned how to create a procedure, and how to work with variables. We saw the main difference between SQL Server and PostgreSQL to create stored procedures. The main differences are the dollar-quoting ($$) the use of semicolons (;) and the way to concatenate strings with variables. Also, Postgres can handle different languages in the stored procedures.

Daniel Calbimonte
Latest posts by Daniel Calbimonte (see all)