4.2 Synchronous Retrieval

This section describes the procedures pg-exec, pg-exec-params and pg-exec-prepared. See Parameters, for background info on the latter two.

Procedure: pg-exec conn statement ¶

Execute the SQL string statement on a given connection conn returning either a PG_RESULT object containing a pg-result-status or #f if an error occurred, in which case the error message can be obtained using pg-error-message, passing it the PG_CONN object on which the statement was attempted. Note that the error message is available only until the next call to pg-exec on this connection.

Procedure: pg-exec-params conn statement parms ¶

Like pg-exec, except that statement is a parameterized string, and parms is a parameter-vector.

Procedure: pg-exec-prepared conn stname parms ¶

Execute the statement named by stname (a string) on a given connection conn returning either a result object or #f. stname must match the name specified in some prior SQL PREPARE statement. parms is a parameter-vector.

Example

This example defines a procedure pg-exec/no-false which wraps pg-exec so that a misc-error is thrown instead of returning false. There are numerous other examples of pg-exec calls throughout this chapter.

(define (pg-exec/no-false conn sql)
  (or (pg-exec conn sql)
      (error (pg-error-message conn))))

Notes

The entire result set is returned at once from a call to to pg-exec. If a SELECT results in a very large number of tuples then this can be a problem because it requires a large amount of memory. In these cases it is better to DECLARE a cursor over the SELECT and retrieve small numbers of rows at a time using FETCH. These commands can only be issued between BEGIN TRANSACTION/END TRANSACTION pairs. See the PostgreSQL declare(l) and fetch(l) man pages for more details.