An introduction into server side programming in PostgreSQL – 3 – PL/pgSQL, procedures

In the first to posts in this series (An introduction into server side programming in PostgreSQL – 1 – SQL functions, basics , An introduction into server side programming in PostgreSQL – 2 – SQL functions, sets, udts and polymorphic types ) we had a look at SQL functions and how you can use them to return data for everything that is a SQL statement. If you can do it in SQL, you should do it in SQL. If you can’t, well, then there is PL/pgSQL. For those of you who know Oracle, PL/pgSQL is very similar to PL/SQL. There are differences, of course. One of the differences is, that PL/pgSQL does not know the concept of a package. Let’s have a look, what you can do with PL/pgSQL at a very basic level in this post. The next post will go a step further and dig into more advanced use cases for PL/pgSQL.

When it comes to procedural languages in PostgreSQL you have two choices (since PostgreSQL 11) to implement logic in the database:

• Functions: Can have in and out parameters and must have a return type declared. There is no transaction control inside functions. Functions can be called in SQL statements.
• Procedures: Can have in and inout parameters but do not return any value. There can be transaction control inside procedures. Procedure are executed with the “call” statement and can not be used in SQL.

Because we already created many functions in the last two posts, we’ll start with procedures in this post. One one of the most simple procedure you can have is something like this:

postgres=# create or replace procedure p1()
           as 
           $$
           declare
           begin
             raise notice 'My first procedure';
           end; 
           $$ language plpgsql;

All this procedure does, is printing a simple text to the screen by using “raise”. Once you execute the procedure, you’ll see the message on the screen:

postgres=# call p1();
NOTICE:  My first procedure
CALL

Parameters with procedures can be either IN or INOUT (out is currently not supported).

postgres=# create or replace procedure p2(pv_text in text, pv_dummy inout text)
           as 
           $$
           declare
           begin
             raise notice 'My first procedure %', pv_text;
           end; 
           $$ language plpgsql;
CREATE PROCEDURE

Because you cannot just call a procedure with a SELECT statement, you need to wrap the call statement e.g. into an anonymous code block:

postgres=# do $$
           declare
             lv_dummy text := 'bbb';
           begin
             call p2(pv_text=>'aaa', pv_dummy=>lv_dummy);
             raise notice 'the second parameter is %', lv_dummy;
           end; $$;
NOTICE:  My first procedure aaa
NOTICE:  the second parameter is bbb
DO

I’ve said initially that you can have transaction control within procedures. Something like this is possible:

postgres=# create table t1 ( a int );
CREATE TABLE
postgres=# create or replace procedure p3()
           as 
           $$
           declare
             ln int := 1;
           begin
             insert into t1 values (ln);
             ln := ln + 1;
             commit;
             insert into t1 values (ln);
             rollback; 
           end;
           $$ language plpgsql;
CREATE PROCEDURE

Executing this procedure leads to only one row in the t1 table:

postgres=# call p3();
CALL
postgres=# select * from t1;
 a 
---
 1
(1 row)

If you are already in a transaction when you call the procedure, it will not work:

postgres=# begin;
BEGIN
postgres=*# call p3();
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function p3() line 7 at COMMIT
postgres=!# 

Savepoints can also not be used within a procedure:

postgres=# create or replace procedure p3()
           as 
           $$
           declare
             ln int := 1;
           begin
             insert into t1 values (ln);
             ln := ln + 1;
             savepoint sp1;
             insert into t1 values (ln);
             rollback to sp1; 
           end;
           $$ language plpgsql;
ERROR:  syntax error at or near "to"
LINE 11:              rollback to sp1; 

It seems a bit strange that “savepoint” is actually fine, but “rollback to” is not accepted as valid syntax. The documentation is not very clear about this, but in fact you can find it documented here. That means you can get this behavior if you do it like this (when others is of course not the right thing to do here, as you cannot be sure what actually happened):

postgres=# truncate t1;
TRUNCATE TABLE
postgres=# create or replace procedure p4()
           as 
           $$
           declare
             ln int := 1;
           begin
             insert into t1 values (ln);
             ln := ln + 1;
             begin
               insert into t1 values ('aaa');
             exception when others then null;
             end;
             commit;
           end;
           $$ language plpgsql;
CREATE PROCEDURE
postgres=# call p4();
CALL
postgres=# select * from t1;
 a 
---
 1
(1 row)

Calling one procedure from another procedure (or function) of course works and is supported:

postgres=# create or replace procedure p5()
           as $$
           begin
               call p4();
           end; $$ language plpgsql;
CREATE PROCEDURE
postgres=# call p5();
CALL

As with SQL functions you can use all the data types PostgreSQL is supporting, including data types you created on your own. Key point now: If you need transaction control, go for procedures as this is not supported with functions. In the next post we’ll look into PL/pgSQL functions and the control structures that come with PL/pgSQL.