PL/Scheme

PL/Scheme is a PostgreSQL procedural language handler for Scheme programming language. PL/Scheme uses Chibi Scheme in the background as its Scheme interpreter. With lots of builtin SRFIs and complete R7RS compliancy of Chibi Scheme, PL/Scheme can power up PostgreSQL procedures in a Lisp style. PL/Scheme is not enabled by default, but it can be enabled for a specific database with CREATE EXTENSION plscheme.

Functions and Arguments

To create a function in the PL/Scheme language, use the standard CREATE FUNCTION syntax:

CREATE FUNCTION funcname (argument-types) RETURNS return-type
-- function attributes can go here
AS $$
  ; PL/Scheme function body goes here
$$ LANGUAGE plscheme;

The body of the function is ordinary Scheme code. In fact, the PL/Scheme glue code wraps it inside a Scheme subroutine. A PL/Scheme function is called in a scalar context, so it can’t return a list. You can return non-scalar values (arrays, records, and sets) by returning a reference, as discussed below.

In a PL/Scheme procedure, any return value from the Scheme code is ignored.

PL/Scheme also supports anonymous code blocks called with the DO statement:

DO $$
  ; PL/Scheme code
$$ LANGUAGE plscheme;

An anonymous code block receives no arguments, and whatever value it might return is discarded. Otherwise it behaves just like a function.

The syntax of the CREATE FUNCTION command requires the function body to be written as a string constant. It is usually most convenient to use dollar quoting (see Section 4.1.2.4) for the string constant. If you choose to use escape string syntax E'', you must double any single quote marks (') and backslashes (\) used in the body of the function (see Section 4.1.2.1).

Arguments and results are handled as in any other Scheme subroutine: arguments are passed as Scheme variables, and a result value is returned as the last expression evaluated in the function.

For example, a function returning the greater of two integer values could be defined as:

CREATE FUNCTION scheme_max (a integer, b integer) RETURNS integer
AS $$
  (if (> a b) a b)
$$ LANGUAGE plscheme;

Data Values

Generally speaking, the aim of PL/Scheme is to provide a “natural” mapping between the PostgreSQL and the Scheme worlds. This informs the data mapping rules described below.

Data Type Mapping

When a PL/Scheme function is called, its arguments are converted from their PostgreSQL data type to a corresponding Scheme type:

PostgreSQL boolean is converted to Scheme boolean.
PostgreSQL smallint and int are converted to Scheme fixnum. PostgreSQL bigint and oid are converted to fixnum in Scheme.
PostgreSQL real and double are converted to Scheme flonum.
PostgreSQL numeric is converted to Scheme flonum.
PostgreSQL bytea is converted to Scheme bytevector.
All other data types, including the PostgreSQL character string types, are converted to a Scheme string.
For nonscalar data types, see below.

When a PL/Scheme function returns, its return value is converted to the function’s declared PostgreSQL return data type as follows:

When the PostgreSQL return type is boolean, the return value will be evaluated for truth according to the Scheme rules.
When the PostgreSQL return type is bytea, the return value will be converted to bytes using the respective Scheme built-ins, with the result being converted to bytea.
For all other PostgreSQL return types, the return value is converted to a string using the Scheme built-in string, and the result is passed to the input function of the PostgreSQL data type.
For nonscalar data types, see below.

Note that logical mismatches between the declared PostgreSQL return type and the Scheme data type of the actual return object are not flagged; the value will be converted in any case.

NULL

If an SQL null value is passed to a function, the argument value will appear as () in Scheme. For example, the function definition of scheme_max shown in Functions and Arguments will return the wrong answer for null inputs. We could add STRICT to the function definition to make PostgreSQL do something more reasonable: if a null value is passed, the function will not be called at all, but will just return a null result automatically. Alternatively, we could check for null inputs in the function body:

CREATE FUNCTION scheme_max (a integer, b integer) RETURNS integer
AS $$
  (if (or (null? a) (null? b))
    '()
    (if (> a b) a b))
$$ LANGUAGE plscheme;

As shown above, to return an SQL null value from a PL/Scheme function, return the value (). This can be done whether the function is strict or not.

Arrays, Vectors

SQL array values are passed into PL/Scheme as a Scheme vector. To return an SQL array value out of a PL/Scheme function, return a Scheme vector:

CREATE OR REPLACE FUNCTION return_arr() RETURNS int[]
AS $$
  (vector 1 2 '() 3)
$$ LANGUAGE plscheme;

SELECT return_arr();
     return_arr
--------------------
 [0:3]={1,2,NULL,3}
(1 row)

Standard Modules

We can import standard modules provided by Scheme in PL/Scheme functions or anonymous code blocks, for example:

DO $$
  (import (scheme small)
          (srfi 1))
$$ LANGUAGE plscheme;

This import can affect the execution of PL/Scheme functions or anonymous code blocks in the current session.

Scheme Standard

The default language is the (scheme base) library from R7RS, which is mostly a superset of R5RS.

The reader defaults to case-sensitive, like R6RS and R7RS but unlike R5RS. The default configuration includes the full numeric tower: fixnums, flonums, bignums, exact rationals and complex numbers. This list includes standard libraries defined by R7RS:

(scheme base) - R7RS base library
(scheme case-lambda) - R7RS case-lambda library
(scheme char) - R7RS char library
(scheme complex) - R7RS complex numbers library
(scheme cxr) - R7RS CxR accessors library
(scheme eval) - R7RS eval library
(scheme file) - R7RS file library
(scheme inexact) - R7RS inexact numbers library
(scheme lazy) - R7RS lazy evaluation library
(scheme load) - R7RS load library
(scheme process-context) - R7RS process-context library
(scheme read) - R7RS read library
(scheme repl) - R7RS repl library
(scheme time) - R7RS time library
(scheme write) - R7RS write library
(scheme r5rs) - R5RS standard library, it is a wrapper of other libraries
(scheme small) - R7RS standard library, it is a wrapper of all above libraries

SRFI Module

A number of SRFIs are provided in Chibi Scheme. Note that SRFIs 0, 6, 23, 46 and 62 are built into the default environment so there’s no need to import them. This list includes popular SRFIs or SRFIs used in standard Chibi modules:

Chibi Module

Additional non-standard modules are put in the (chibi) module namespace.

Errors and Messages

Use the raise function to report messages and raise errors.

(raise level message)

The level option specifies the error severity. Allowed levels are debug, log, info, notice, warning, and exception. exception raises an error (which normally aborts the current transaction); the other levels only generate messages of different priority levels. Whether messages of a particular priority are reported to the client, written to the server log, or both is controlled by the log_min_messages and client_min_messages configuration variables. See Chapter 19 for more information.

In this example, we report a simple message at the notice level:

(raise "notice" "Notice me.")