12 Result Transforms

To support handling of query results in the Scheme domain, Guile-PG provides various procedures to walk the result object, to extract Scheme objects from the result object, and to collect these in idiomatic data structures (such as the association list).

To get started, load the postgres-resx module:

(use-modules (database postgres-resx))

The most basic procedure is for-each-tuple, useful for quick searches on the unconverted result strings.

Procedure: for-each-tuple proc result ¶

Apply proc to each tuple in result. Return #t to indicate success, or #f if either the tuple count or the field count is zero. The tuple is the list formed by mapping pg-getvalue over the fields. Thus, proc should take as many arguments as there are columns in result.

The rest of the procedures in this chapter combine conversion of the raw result strings to Scheme objects, with different collection methods. See Octet Molding/Mashing. (For access to the unconverted result strings, use the procedure identity as the objectifier, which in effect does no conversion at all.)

The degenerate collection method is no collection at all! (A common idiom is to SELECT EXPR; to get a result consisting of one row and one column.)

Procedure: object<-result result objectifier ¶

Return result tuple 0 field 0, converted using objectifier.

For other collection methods, we include examples to clarify. The examples are based upon a result object that can briefly described as:

• two tuples each with two fields
• field names: "date" and "note"
• first tuple’s date: "2003-01-14 00:00:00-08"
• first tuple’s note: "now", "the present"
• second tuple’s date: "2003-01-13 00:00:00-08"
• second tuple’s note: "yesterday", "the past"

The defs (see Column Definitions) for the examples are:

(define defs '((date timestamp) (note *text)))

Actually the note field in its raw form involves curly braces and extra quoting, but we omit that to reduce clutter.

The result-field->object-list procedure is useful for single-column queries.

Procedure: result-field->object-list result fn objectifier ¶

For result field number fn, map objectifier. Return a list whose length is the number of tuples in result.

Example

(define (1-col result fn type)
  (let ((objectifier (type-objectifier type)))
    (result-field->object-list result fn objectifier)))

(1-col result 0 'timestamp)
⇒ (1042531200 1042444800)

(1-col result 1 '*text)
⇒ (("now" "the present") ("yesterday" "the past"))

The result->object-FOO procedures have the same signature but return the data in different ways.

Procedure: result->object-alist result objectifiers ¶

Return an alist from extracting result using objectifiers. Each key (a symbol) is a field name obtained by pg-fname, and the associated value is a list of objects coverted by one of the objectifier procedures from the list objectifiers.

Procedure: result->object-alists result objectifiers ¶

Process result using objectifiers like result->object-alist, but return a list of alists instead, each corresponding to a tuple in result.

Procedure: result->object-rows result objectifiers ¶

Return a list from extracting result using objectifiers. Each element of the list is a sublist representing one row.

Example

(use-modules ((database postgres-col-defs) #:select (objectifiers)))

(define odefs (objectifiers defs))

(result->object-alist result odefs)
⇒
((date 1042531200 1042444800)
 (note ("now" "the present") ("yesterday" "the past")))

(result->object-alists result odefs)
⇒
(((date . 1042531200) (note "now" "the present"))
 ((date . 1042444800) (note "yesterday" "the past")))