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:
- Starting psql to create a stored procedure
- Hello world example using a psql stored procedure
- Example using a psql stored procedure with variables
- Example using a psql stored procedure to insert data
- Example to modify a psql stored procedure
- How to drop a psql stored procedure
- How to delete data using a psql stored procedure
Requirements
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).
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.
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.
Also, run a query to check the port used using the pg_settings and execute the query.
SELECT * FROM pg_settings WHERE name = ‘port’;
The query will display the port used.
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 $$
BEGIN
RAISE NOTICE ‘Hello world’;
END
$$;
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.
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 $$
BEGIN
RAISE NOTICE ‘Hello %’,name;
END
$$;
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:
Name | Description |
serial | Autoincremental integer (4 bytes) |
Polygon | A closed geometric path |
Jsonb | It is Binary JSON data |
Line | For lines in a plane |
Circle | For circles on a plane |
Tsvector | 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:
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,
email VARCHAR ( 255 ) UNIQUE 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) )
LANGUAGE SQL
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.
LANGUAGE SQL
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
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.
CREATE OR REPLACE PROCEDURE hello_world()
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE ‘Hello sqlshack’;
END
$$;
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.
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 $$
BEGIN
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;
Conclusion
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.
He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023