SUMMARY: This article provides ten examples of stored procedures in PostgreSQL.

1. Inserting data using a procedure

2. Displaying a message on the screen

3. Using transaction control 

4. Using columns data types

5. Raising notices, warnings, and INFO messages

6. Raising exceptions

7. Traversing values in a table using a FOR loop

8. Using SECURITY INVOKER

9. Using SECURITY DEFINER

10. Setting configuration parameters

 

A stored procedure is a set of structured queries and the statements such as control statements and declarations. Here are ten examples of stored procedures that can be useful in different situations.

For more details on creating procedures, refer to the PostgreSQL documentation:

https://www.postgresql.org/docs/12/sql-createprocedure.html

 

1. Inserting data using a procedure

CREATE OR REPLACE PROCEDURE genre_insert_data("GenreId" integer, "Name" character varying)

LANGUAGE SQL

AS $$

INSERT INTO public."Genre" VALUES ("GenreId", "Name");

$$;

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 

CREATE OR REPLACE PROCEDURE display_message (INOUT msg TEXT)

AS $$

BEGIN

RAISE NOTICE 'Procedure Parameter: %', msg ;

END ;

$$

LANGUAGE plpgsql ;

 

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 

CREATE OR REPLACE PROCEDURE control_transaction()

LANGUAGE plpgsql

AS $$

DECLARE

BEGIN

  CREATE TABLE test1 (id int);

  INSERT INTO test1 VALUES (1);

  COMMIT;

  CREATE TABLE test2 (id int);

  INSERT INTO test2 VALUES (1);

  ROLLBACK;

END $$;

 

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.

 

Reference Links:

https://www.postgresql.org/docs/12/sql-createprocedure.html