Calling Postgresql Stored Functions and Procedures

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

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.

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. S-SQL does not have a sub-dialect for plpgsql, so we need to use the text version:

(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;")

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 Procedures.

(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 has an s-sql :call operator as of version 1.33.10. Pre-version 1.33.10, you would simply use a sql string:

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

((2 2) (1 13))

As of version 1.33.10, you can use s-sql as you might expect:

(query (:call 'set_x_procedure1 1 13))

((2 2) (1 13))

or, using variables

(let ((a 1) (b 2) (p 'set_x_procedure1))
  (query (:call p a b 3)))

((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;
        $$ ;")