January 24, 2023
A stored procedure is a set of structured queries and statements such as control statements and declarations. Here are ten examples of stored procedures that can be useful in different situations.
1. Inserting data using a procedure
procedure_demo=# CREATE OR REPLACE PROCEDURE genre_insert_data("GenreId" integer, "Name" character varying)
procedure_demo-# LANGUAGE SQL
procedure_demo-# AS $$
procedure_demo$# INSERT INTO public."Genre" VALUES ("GenreId", "Name");
procedure_demo$# $$;
CREATE PROCEDURE
procedure_demo=# CALL genre_insert_data(26,'Pop');
CALL
procedure_demo=# select * from public."Genre" where "GenreId" = 26;
GenreId | Name
---------+------
26 | Pop
(1 row)
2. Displaying a message on the screen
procedure_demo=# CREATE OR REPLACE PROCEDURE display_message (INOUT msg TEXT)
procedure_demo-# AS $$
procedure_demo$# BEGIN
procedure_demo$# RAISE NOTICE 'Procedure Parameter: %', msg ;
procedure_demo$# END ;
procedure_demo$# $$
procedure_demo-# LANGUAGE plpgsql ;
CREATE PROCEDURE
procedure_demo=# call display_message('This is my test case');
NOTICE: Procedure Parameter: This is my test case
msg
----------------------
This is my test case
(1 row)
3. Using transaction control
procedure_demo=# CREATE OR REPLACE PROCEDURE control_transaction()
procedure_demo-# LANGUAGE plpgsql
procedure_demo-# AS $$
procedure_demo$# DECLARE
procedure_demo$# BEGIN
procedure_demo$# CREATE TABLE test1 (id int);
procedure_demo$# INSERT INTO test1 VALUES (1);
procedure_demo$# COMMIT;
procedure_demo$# CREATE TABLE test2 (id int);
procedure_demo$# INSERT INTO test2 VALUES (1);
procedure_demo$# ROLLBACK;
procedure_demo$# END $$;
CREATE PROCEDURE
procedure_demo=#
procedure_demo=# call control_transaction();
CALL
procedure_demo=# select relname from pg_class where relname like '%test%';
relname
---------
test1
(1 row)
Here we can see that data before commit is available, but then the data that is not committed and is rolled back gets dropped from the database.
4. Using column data types
procedure_demo=# CREATE OR REPLACE PROCEDURE genre_id_max() LANGUAGE plpgsql AS $$
procedure_demo$# DECLARE
procedure_demo$# id "Genre"."GenreId"%type;
procedure_demo$# BEGIN
procedure_demo$# select max("GenreId") into id from public."Genre";
procedure_demo$# RAISE NOTICE 'Maximum of GenreId is : %', id ;
procedure_demo$# END;
procedure_demo$# $$ ;
CREATE PROCEDURE
procedure_demo=# call genre_id_max();
NOTICE: Maximum of GenreId is : 26
CALL
5. Raising notices, warnings, and INFO messages
procedure_demo=# CREATE OR REPLACE PROCEDURE raise_warning() AS $$
procedure_demo$# DECLARE
procedure_demo$# warn INT := 10;
procedure_demo$# BEGIN
procedure_demo$# RAISE NOTICE 'value of warn : % at %: ', warn, now();
procedure_demo$# warn := warn + 10;
procedure_demo$# RAISE WARNING 'value of warn : % at %: ', warn, now();
procedure_demo$# warn := warn + 10;
procedure_demo$# RAISE INFO 'value of warn : % at %: ', warn, now();
procedure_demo$# END;
procedure_demo$# $$
procedure_demo-# LANGUAGE plpgsql;
CREATE PROCEDURE
procedure_demo=#
procedure_demo=# call raise_warning();
NOTICE: value of warn : 10 at 2019-12-03 16:25:34.339094+05:30:
WARNING: value of warn : 20 at 2019-12-03 16:25:34.339094+05:30:
INFO: value of warn : 30 at 2019-12-03 16:25:34.339094+05:30:
CALL
procedure_demo=#
6. Raising exceptions
procedure_demo=# CREATE OR REPLACE PROCEDURE genre_id_exception() LANGUAGE plpgsql AS $$
procedure_demo$# DECLARE
procedure_demo$# id "Genre"."GenreId"%type;
procedure_demo$# BEGIN
procedure_demo$# select max("GenreId") into id from public."Genre";
procedure_demo$# RAISE EXCEPTION 'Maximum of GenreId is : %', id USING HINT = 'Test For Raising exception.';
procedure_demo$# END;
procedure_demo$# $$ ;
CREATE PROCEDURE
procedure_demo=# call genre_id_exception();
ERROR: Maximum of GenreId is : 26
HINT: Test For Raising exception.
CONTEXT: PL/pgSQL function genre_id_exception() line 6 at RAISE
procedure_demo=#
7. Traversing values in a table using a FOR loop
procedure_demo=# CREATE OR REPLACE PROCEDURE genre_traverse() LANGUAGE plpgsql AS $$
procedure_demo$# DECLARE
procedure_demo$# genre_rec record;
procedure_demo$# BEGIN
procedure_demo$# for genre_rec in (select "GenreId","Name" from public."Genre" order by "GenreId")
procedure_demo$# loop
procedure_demo$# RAISE NOTICE 'Genre Id is : % , Name is : %', genre_rec."GenreId",genre_rec."Name";
procedure_demo$# end loop;
procedure_demo$# END;
procedure_demo$# $$ ;
CREATE PROCEDURE
procedure_demo=#
procedure_demo=# call genre_traverse();
NOTICE: Genre Id is : 1 , Name is : test
NOTICE: Genre Id is : 2 , Name is : Jazz
NOTICE: Genre Id is : 3 , Name is : Metal
NOTICE: Genre Id is : 4 , Name is : Alternative & Punk
NOTICE: Genre Id is : 5 , Name is : Rock And Roll
NOTICE: Genre Id is : 6 , Name is : Blues
NOTICE: Genre Id is : 7 , Name is : Latin
NOTICE: Genre Id is : 8 , Name is : Reggae
NOTICE: Genre Id is : 9 , Name is : Pop
NOTICE: Genre Id is : 10 , Name is : Soundtrack
NOTICE: Genre Id is : 11 , Name is : Bossa Nova
NOTICE: Genre Id is : 12 , Name is : Easy Listening
NOTICE: Genre Id is : 13 , Name is : Heavy Metal
NOTICE: Genre Id is : 14 , Name is : R&B/Soul
NOTICE: Genre Id is : 15 , Name is : Electronica/Dance
NOTICE: Genre Id is : 16 , Name is : World
NOTICE: Genre Id is : 17 , Name is : Hip Hop/Rap
NOTICE: Genre Id is : 18 , Name is : Science Fiction
NOTICE: Genre Id is : 19 , Name is : TV Shows
NOTICE: Genre Id is : 20 , Name is : Sci Fi & Fantasy
NOTICE: Genre Id is : 21 , Name is : Drama
NOTICE: Genre Id is : 22 , Name is : Comedy
NOTICE: Genre Id is : 23 , Name is : Alternative
NOTICE: Genre Id is : 24 , Name is : Classical
NOTICE: Genre Id is : 25 , Name is : Opera
NOTICE: Genre Id is : 26 , Name is : Pop
CALL
procedure_demo=#
8. Using SECURITY INVOKER
SECURITY INVOKER indicates that the procedure is to be executed with the privileges of the user that calls it. That is the default.
procedure_demo=# CREATE OR REPLACE PROCEDURE genre_traverse() LANGUAGE plpgsql SECURITY INVOKER
procedure_demo-# AS $$
procedure_demo$# DECLARE
procedure_demo$# genre_rec record;
procedure_demo$# BEGIN
procedure_demo$# for genre_rec in (select "GenreId","Name" from public."Genre" order by "GenreId")
procedure_demo$# loop
procedure_demo$# RAISE NOTICE 'Genre Id is : % , Name is : %', genre_rec."GenreId",genre_rec."Name";
procedure_demo$# end loop;
procedure_demo$# END;
procedure_demo$# $$ ;
CREATE PROCEDURE
procedure_demo=# create user test;
CREATE ROLE
procedure_demo=# \c procedure_demo test
You are now connected to database "procedure_demo" as user "test".
procedure_demo=> call genre_traverse();
ERROR: permission denied for table Genre
CONTEXT: SQL statement "(select "GenreId","Name" from public."Genre" order by "GenreId")"
PL/pgSQL function genre_traverse() line 5 at FOR over SELECT rows
procedure_demo=>
9. Using SECURITY DEFINER
SECURITY DEFINER specifies that the procedure is to be executed with the privileges of the user that owns it. A SECURITY DEFINER procedure cannot execute transaction control statements (for example, COMMIT and ROLLBACK, depending on the language).
In this example we have created a stored procedure with the user “postgres” and called it by using a “test” user who does not have access to the table.
procedure_demo=# CREATE OR REPLACE PROCEDURE genre_traverse() LANGUAGE plpgsql SECURITY DEFINER
procedure_demo-# AS $$
procedure_demo$# DECLARE
procedure_demo$# genre_rec record;
procedure_demo$# BEGIN
procedure_demo$# for genre_rec in (select "GenreId","Name" from public."Genre" order by "GenreId")
procedure_demo$# loop
procedure_demo$# RAISE NOTICE 'Genre Id is : % , Name is : %', genre_rec."GenreId",genre_rec."Name";
procedure_demo$# end loop;
procedure_demo$# END;
procedure_demo$# $$ ;
CREATE PROCEDURE
procedure_demo=#
procedure_demo=#
procedure_demo=# \c procedure_demo test
You are now connected to database "procedure_demo" as user "test".
procedure_demo=> call genre_traverse();
NOTICE: Genre Id is : 1 , Name is : test
NOTICE: Genre Id is : 2 , Name is : Jazz
NOTICE: Genre Id is : 3 , Name is : Metal
NOTICE: Genre Id is : 4 , Name is : Alternative & Punk
NOTICE: Genre Id is : 5 , Name is : Rock And Roll
NOTICE: Genre Id is : 6 , Name is : Blues
NOTICE: Genre Id is : 7 , Name is : Latin
NOTICE: Genre Id is : 8 , Name is : Reggae
NOTICE: Genre Id is : 9 , Name is : Pop
NOTICE: Genre Id is : 10 , Name is : Soundtrack
NOTICE: Genre Id is : 11 , Name is : Bossa Nova
NOTICE: Genre Id is : 12 , Name is : Easy Listening
NOTICE: Genre Id is : 13 , Name is : Heavy Metal
NOTICE: Genre Id is : 14 , Name is : R&B/Soul
NOTICE: Genre Id is : 15 , Name is : Electronica/Dance
NOTICE: Genre Id is : 16 , Name is : World
NOTICE: Genre Id is : 17 , Name is : Hip Hop/Rap
NOTICE: Genre Id is : 18 , Name is : Science Fiction
NOTICE: Genre Id is : 19 , Name is : TV Shows
NOTICE: Genre Id is : 20 , Name is : Sci Fi & Fantasy
NOTICE: Genre Id is : 21 , Name is : Drama
NOTICE: Genre Id is : 22 , Name is : Comedy
NOTICE: Genre Id is : 23 , Name is : Alternative
NOTICE: Genre Id is : 24 , Name is : Classical
NOTICE: Genre Id is : 25 , Name is : Opera
NOTICE: Genre Id is : 26 , Name is : Pop
CALL
procedure_demo=>
10. Setting configuration parameters
The effects of a SET LOCAL command for a variable are restricted to the procedure inside which the command is executed; the configuration parameter's prior value is restored after exiting the procedure. However, a simple SET command (without LOCAL) overrides the SET clause, much as it would do for a previous SET LOCAL command. The effects of the configuration will persist after procedure exit, unless the current transaction is rolled back.
procedure_demo=# CREATE OR REPLACE PROCEDURE datestyle_change() LANGUAGE plpgsql SET datestyle TO postgres, dmy
procedure_demo-# AS $$
procedure_demo$# BEGIN
procedure_demo$# RAISE NOTICE 'Current Date is : % ', now();
procedure_demo$# END;
procedure_demo$# $$ ;
CREATE PROCEDURE
procedure_demo=# call datestyle_change();
NOTICE: Current Date is : Tue 03 Dec 18:09:06.824398 2019 IST
CALL
procedure_demo=# select now();
now
----------------------------------
2019-12-03 18:09:12.930919+05:30
(1 row)
procedure_demo=#
Here, we changed the current datestyle (ISO, MDY) to “postgres, dmy” using SET, and the configuration is still effective outside the procedure.
Related Content
BLOG POST
How to secure PostgreSQL
BLOG POST
Oracle to PostgreSQL Migration Guide
TUTORIAL
How to use Coalesce in PostgreSQL
BLOG POST
How to Monitor PostgreSQL Like a Pro!
BLOG POST
PostgreSQL Vs. MySQL