4.1 Parameters

The sections following this one revolve around describing the essential method of communicating with the PostgreSQL server, namely, by sending it a properly-constructed string representing a command or statement. However, …

The string is a stark data structure and everywhere it is passed there is much duplication of process. It is a perfect vehicle for hiding information.

— Alan Perlis

To reduce this inefficiency for heavily-repeated statements that may differ only slightly from one instance to the next, PostgreSQL 7.4 introduced libpq variants that make use of parameterized strings, which are normal strings with embedded placeholders (such as $1, $2, and so on) that can be sent to the server accompanied by typed parameters. These operate similarly to the PREPARE and EXECUTE SQL statements.

See Synchronous Retrieval. See Asynchronous Retrieval.

Even if a statement is not heavily-repeated (thus offering little performance improvement potential), using these variants can be advantageous anyway since they remove the need for tricky and error-prone sql-quoting.

Guile-PG wraps these variants differently from the way it does the other libpq functions, departing from close congruence to the C-language argument list in favor of a simplified, “more Schemey”, interface. The Scheme procedures take, in addition to the connection object and the command or statement (parameterized string), a vector of parameters, conventionally named parms.

Restrictions

Guile-PG support for parameters is in its infancy. All elements of parms must be strings otherwise a “bad parameter-vector element” error is signalled. Relatedly, the binary parameters are not supported; only text parameters (strings) are supported for now. Basically, Guile-PG support is a step up from not having parameters at all, but not (yet) fully generalized.

Here is an example of how to work within these restrictions:

(define C (pg-connectdb ...))

(define (sel s . args)
  (apply format #f (string-append "SELECT " s ";")
         args))

(define (straightforward-square n)
  (pg-exec C (sel "~A * ~A" n n)))

(define (parameterized-square n)
  (pg-exec-params C (sel "$1::integer * $1::integer")
                  (vector (number->string n)
                          (number->string n))))

(define (single result)
  (pg-getvalue result 0 0))

(equal? (single (straightforward-square 42))
        (single (parameterized-square 42)))
⇒ #t

In the future, it should be possible to specify integer in parms instead of in the parameterized string.