An Overview of the New Stored Procedures in PostgreSQL 11

As you may know in all the versions up to PostgreSQL 10, it was not possible to create a procedure in PostgreSQL. In PostgreSQL 11, PROCEDURE was added as a new schema object which is a similar object to FUNCTION, but without a return value.

Over the years many people were anxious to have the functionality and it was finally added in PostgreSQL 11. Traditionally, PostgreSQL has provided all the means to write functions (which were called as stored procedures) however, in a function you cannot run transactions. All you can really use is exceptions, which are basically savepoints. Inside a function body you cannot just commit a transaction or open a new one. The new CREATE PROCEDURE will change all that and provide a functionality to run transactions inside procedural code.

Benefits of Using Stored Procedures

• Transaction control allowing us to COMMIT and ROLLBACK inside procedures.
• Very helpful for Oracle to PostgreSQL migration, the new procedure functionality can be a significant time saver.
• As you can see there are a couple of similarities between CREATE FUNCTION and CREATE PROCEDURE so things should be really easy for most end users.

How to Use Stored Procedure in PostgreSQL

Use CREATE PROCEDURE to create a new procedure in PostgreSQL 11, it will allow you to write procedure just like other databases. PROCEDURE is almost the same as FUNCTION without a return value. PROCEDURE is created with the CREATE PROCEDURE statement in PostgreSQL 11. Unlike the CREATE FUNCTION statement, there are no RETURNS clause, ROWS clause etc.

Syntax

postgres=# h CREATE PROCEDURE
Command:     CREATE PROCEDURE
Description: define a new procedure
Syntax:
CREATE [ OR REPLACE ] PROCEDURE
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...

Example

CREATE PROCEDURE procedure1(INOUT p1 TEXT) 
AS $$
BEGIN
    RAISE NOTICE 'Procedure Parameter: %', p1 ;
END ;
$$
LANGUAGE plpgsql ;

Execute PROCEDURE in PostgreSQL

To execute PROCEDURE in PostgreSQL, use the CALL statement instead of SELECT statement. This is one of the differences between PROCEDURE and FUNCTION.

postgres=# CALL procedure1 (' CREATE PROCEDURE functionality supported in PostgreSQL 11! ');    
NOTICE:  Procedure Parameter:  CREATE PROCEDURE functionality supported in PostgreSQL 11!       
                              p1                                                                
--------------------------------------------------------------                                  
  CREATE PROCEDURE functionality supported in PostgreSQL 11!                                    
(1 row)

You can also specify parameter name in the CALL statement. This is another way to execute the PROCEDURE.

postgres=# CALL procedure1 (p1=>'CREATE PROCEDURE functionality supported in PostgreSQL 11!');
NOTICE:  Procedure Parameter: CREATE PROCEDURE functionality supported in PostgreSQL 11!        
                             p1                                                                 
------------------------------------------------------------                                    
 CREATE PROCEDURE functionality supported in PostgreSQL 11!                                     
(1 row)

Display List of Created PROCEDURE

You can check the definition of created PROCEDURE from psql command i.e ‘df’. The psql command ‘df’ is also used to display the definition of created FUNCTION.

The PROCEDURE shows the Type column as “proc” and if it is FUNCTION then the Type column changed to “func”.

In the below list of functions, we have created one PROCEDURE so the Type column changed to “proc”.

postgres=# df
                          List of functions
Schema |    Name    | Result data type | Argument data types | Type
--------+------------+------------------+---------------------+------
public | procedure1 |                  | INOUT p1 text       | proc
(1 row)

Here, we can create one FUNCTION to check the Type column.

CREATE FUNCTION function1(INOUT p1 TEXT) 
AS $$
BEGIN
    RAISE NOTICE 'Function Parameter: %', p1 ;
END ;
$$
LANGUAGE plpgsql ;

Execute the FUNCTION using SELECT command.

postgres=# SELECT function1('CREATE PROCEDURE functionality supported in PostgreSQL 11!');     
NOTICE:  Function Parameter: CREATE PROCEDURE functionality supported in PostgreSQL 11!        
                         function1                                                             
------------------------------------------------------------                                   
 CREATE PROCEDURE functionality supported in PostgreSQL 11!                                    
(1 row)

Now you can check the Type column and see the difference. For the FUNCTION function1, the Type column changed to “func”. You can see one more difference here, PROCEDURE is almost the same as FUNCTION without a return value.

postgres=# df  
                          List of functions                                                                                                                      
 Schema |    Name    | Result data type | Argument data types | Type          
--------+------------+------------------+---------------------+------         
 public | function1  | text             | INOUT p1 text       | func          
 public | procedure1 |                  | INOUT p1 text       | proc          
(2 rows)

Display PROCEDURE Definition in PostgreSQL

Use ‘sf’ to display the definition of created PROCEDURE.

postgres=# sf procedure1                                                    
CREATE OR REPLACE PROCEDURE public.procedure1(INOUT p1 text)                 
 LANGUAGE plpgsql                                                            
AS $procedure$                                                               
BEGIN                                                                        
 RAISE NOTICE 'Procedure Parameter: %', p1 ;                                 
END ;                                                                        
$procedure$

Transaction Control in PROCEDURE

Transaction control allowing us to COMMIT and ROLLBACK inside procedures. CREATE FUNCTION does not support transaction inside the function. This is the main difference between FUNCTION and PROCEDURE in PostgreSQL.

Let’s create a simple stored procedure that handles transactions.

CREATE OR REPLACE PROCEDURE transaction_test() 
LANGUAGE plpgsql 
AS $$
DECLARE
BEGIN
  CREATE TABLE committed_table (id int);
  INSERT INTO committed_table VALUES (1);
  COMMIT;
  CREATE TABLE rollback_table (id int);
  INSERT INTO rollback_table VALUES (1);
  ROLLBACK;
END $$;

Execute the PROCEDURE using CALL statement.

postgres=# CALL transaction_test();                                                      
CALL 

Check the execution result.

postgres=# d                                                                         
              List of relations                                                       
 Schema |      Name       | Type  |  Owner                                            
--------+-----------------+-------+----------                                         
 public | committed_table | table | postgres                                          
(1 row)                                                                               

postgres=# SELECT * FROM committed_table;
id
----
  1
(1 row)

In this blog we have seen transaction control for CREATE PROCEDURE using PL/pgSQL language but transaction control is also provided in other languages like PL/Python, PL/Tcl, PL/Perl.

The syntax for transaction control in other languages is as follow:

• PL/Python
  • plpy.commit()
  • plpy.rollback()
• PL/Tcl
  • Commit
  • rollback
• PL/Perl
  • spi_commit()
  • spi_rollback()

Conclusion

CREATE PROCEDURE is definitely one of the important and desirable feature in PostgreSQL 11. This feature is very useful for Oracle to PostgreSQL migration and many different use cases and many people surely welcome it.