Calling Postgresql Stored Functions

Postgresql Stored Functions and Procedures

Postgresql (Not Postmodern) stored functions and procedures are confusing. We can start by setting up a very simple table t1 and insert a couple of values: 

(query "create table t1 (id int not null primary key, x int);")
(query "insert into t1 values (1,1)")
(query "insert into t1 values (2,2)")

Overview of the differences between Postgresql Stored Functions and Postgresql Stored Procedures

Functions:

1) can be called inside a query (select func() from foo)

2) generally return a result

3) must return a single set

4) are scoped to a transaction

5) cannot commit and rollback transactions

6) are called using a select

Procedures:

1) can not be called inside a query

2) typically don’t return results except for maybe error code. There is limited capabilities in Postgresql 13 to return values using INOUT parameters (more below).

3) can commit and rollback transactions

4) can flush the transaction (essentially a COMMIT; followed by a BEGIN;) within the procedure. This is the most important part; it allows for various kinds of things that are dangerous or impossible with functions (for example, a routine that never terminates..

5) are called using call

The rule of thumb: if you don't need to manage transactions from within, you probably want to use a function instead of a procedure.

Stored Functions

The Postgresql documentation for stored functions can be found here. Functions are atomic in Postgres and automatically run inside their own transaction unless called within an outer transaction. They always run inside a single transaction and succeed or fail completely. Consequently, one cannot begin or commit transactions within the function. And commands like VACUUM, CREATE DATABASE, or CREATE INDEX CONCURRENTLY which do not run in a transaction context are not allowed.

Variation 1 with RETURNS SETOF T1

First we create a postgresql stored function which updates a specified row and returns the id and x values for that row, then call that function, first in plain sql then in s-sql. Note that in this variation, the second line of the function specifies that it returns a setof t1 

(query "CREATE OR REPLACE FUNCTION set_x_function1 (fromid INT, newx INT)
         RETURNS setof t1 as $$
         BEGIN
         UPDATE t1 SET x=newx WHERE id=fromid;
         RETURN QUERY select t1.id, t1.x from t1 where t1.id=fromid;
         END;
         $$ LANGUAGE plpgsql;")

(query "SELECT id, x from set_x_function1(1, 3)")
((1 3))

(query "select * from t1") ; confirming the change happened in the database
((2 2) (1 3))

Now calling the function using the s-sql dsl. We will show two variations and then separately a third variation which has some issues between Postgresql and Postmodern. 

(query (:select 'id 'x :from (:set_x_function1 1 4)))
((1 4))

Variation 2 with RETURNS TABLE

This is effectively the same as Variation 1's use of SETOF tablename, but declares the table structure inline instead of referencing an existing object, so joins and such will still work.

We are going to vary the function slightly here, specifically in that second line, then call the function using a plain sql query and then an s-sql query. Also note that the calling query needs to be referencing the column names in the table being returned.: 

(query "CREATE OR REPLACE FUNCTION set_x_function2 (fromid INT, newx INT)
        RETURNS table (tid int, tx int) as $$
        BEGIN
        UPDATE t1 SET x=newx WHERE id=fromid;
        RETURN QUERY select t1.id, t1.x from t1 where t1.id=fromid;
        END;
        $$ LANGUAGE plpgsql;")

(query "SELECT tid, tx from set_x_function2(1, 16)")
((1 16))

(query (:select 'tid 'tx :from (:set_x_function2 1 15)))
((1 15))

Out of curiosity, what would happen if we just selected the calling function rather than specifying the columns that we want? 

(query (:select (:set_x_function 1 17)))

(("(1,17)"))

We got back a sublist containing a string rather sublist containing two integers, probably not what we wanted.

Now consider the related but not quite the same Postgresql Stored Procudeures.

Stored Procedures

The Postgresql documentation for procedures is found here and you can notice that the format is slightly different.. SQL procedures can begin and end transactions.

Similar to the stored function we just looked at, lets create a postgresql stored procedure which simply updates the x field in a specified row and we won't worry about getting a return value yet. 

(query "CREATE OR REPLACE PROCEDURE set_x_procedure1 (fromid INT, newx INT)
        LANGUAGE plpgsql
        AS $$
        BEGIN
          UPDATE t1 SET x=newx WHERE id=fromid;
          COMMIT;
        END;
        $$ ;")

If you were going to call this procedure from psql it would look like: 

call set_x_procedure1(1,3);

select * from t1;
 id | x
----+---
  2 | 2
  1 | 3
(2 rows)

Postmodern does not have a call function in s-sql, so a plain vanilla query would look familiar from the above: 

(query "call set_x_procedure1(1,13)")

(query "select * from t1")
((2 2) (1 13))

We change the parameter list to the stored procedure slightly to make fromid as an inout parameter. Calling the procedure will now return all parameters with INOUT specified: 

(query "CREATE OR REPLACE PROCEDURE set_x_procedure2 (INOUT fromid INT, newx INT)
        LANGUAGE plpgsql
        AS $$
        BEGIN
          UPDATE t1 SET x=newx WHERE id=fromid;
          COMMIT;
        END;
        $$ ;")

(query "call set_x_procedure2(1,11)" :single)
1

In the following example, we just demonstrate that you can apply INOUT to multiple parameters 

(query "CREATE OR REPLACE PROCEDURE set_x_procedure2 (INOUT fromid INT, INOUT newx INT)
        LANGUAGE plpgsql
        AS $$
        BEGIN
          UPDATE t1 SET x=newx WHERE id=fromid;
          COMMIT;
        END;
        $$ ;")

(query "call set_x_procedure2(1,11)")
((1 11))