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’,’[email protected]’);
CALL add_data(2, ‘John’,’Rambo’,’[email protected]’);
If everything is ok, if we do a select in the accounts table, we will see the new rows inserted:
Select * from accounts
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’;
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.
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();
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.
