Next: Introspection, Previous: Result Transforms, Up: The (database postgres*) Modules [Contents][Index]
Sometimes it is convenient to focus on a single table, adding rows to it or drawing queries from it exclusively of any other tables in the database. Likewise with a view, except that only queries are possible. This chapter describes Guile-PG facilities for this kind of use.
To get started, load the postgres-table module:
(use-modules (database postgres-table))
Basically, the pgtable-manager procedure encapsulates specification,
query and mutation (including create/delete) of a PostgreSQL table; and the
pgtable-worker procedure encapsulates pgtable-manager.
Return a closure that manages a table specified by db-spec table-name defs.
db-spec can either be a string simply naming the database to use, a
string comprised of space-separated var=val pairs, an empty string,
or an already existing connection. Procedures for managing connections.
table-name is a string naming the table to be managed.
defs is an alist of column definitions of the form
(name type [option…]),
with name and type symbols and each option a string.
An old format w/o options is recognized also, but deprecated; support
for it will go away in a future release.
The closure accepts a single keyword (or symbol) arg choice and returns a procedure. Here are the accepted keywords along w/ the args (if any) taken by the returned procedure.
* #:drop * #:create * #:insert-values [data ...] * #:insert-col-values cols [data ...] * #:insert-alist alist * #:delete-rows where-condition * #:update-col cols data where-condition * #:update-col-alist alist where-condition * #:select outspec [rest-clauses ...] #:tuples-result->object-alist res #:tuples-result->alists res #:tuples-result->rows res #:trace-exec oport #:finish #:k var
The starred (*) procedures return whatever pg-exec returns for
that type of procedure. Arguments can be:
A keyword, one of #:table-name, #:col-defs, or
#:connection.
One or more Scheme objects.
Either a list of column names (symbols), or a single string of comma-delimited column names.
A prefix-style expression.
Either the result of compile-outspec, or a spec
that compile-outspec can process to produce such a result.
Zero or more prefix-style expressions.
A tuples result, as returned by pg-exec
(assuming no error occurred).
An output port to write the pg-exec command to
immediately prior to executing it. Use #f to disable tracing.
As a special case, #:finish closes the (internal) connection
and arranges for all future invocations of the closure to signal a
‘dead connection’ error.
Return a compiled outspec object from spec and defs,
suitable for passing to the select choice of pgtable-manager.
defs is the same as that for pgtable-manager. spec can
be one of the following:
#t, which means all columns (notionally equivalent to "*")
(type title expr), where:
#f
int4,
or #f to mean text, or #t to mean
use the type associated with the column named in expr,
or the pair (#t . name) to mean use the type associated
with column name
A "bad select part" error results if specified columns or types do not exist, or if other syntax errors are found in spec.
Take db-spec, table-name and defs (exactly the same as
for pgtable-manager) and return a procedure worker similar to
that returned by pgtable-manager except that the data choices
table-name, defs and pgdb
result in error (only those choices which return a procedure remain),
and more importantly, worker actually does the actions
(applying the chosen procedure to its args). For example:
(define M (pgtable-manager spec name defs))
(define W (pgtable-worker spec name defs))
(equal? ((M #:tuples-result->alists) ((M #:select) #t))
(W #:tuples-result->alists (W #:select #t)))
⇒ #t
This example is not intended to be wry commentary on the behavioral patterns of human managers and workers, btw.
The other procedures are useful for succinctly codifying queries, and translating the result en mass to Scheme objects. [NOTE: These will definitely migrate to other modules at some point before 1.0 release.]
Return string marked as preformatted. This inhibits certain types of processing when passed through the other procedures defined in this module. Repeated calls do not nest.
The rest of this chapter is an extended example showing some of the uses of
pgtable-manager.
(define m (pgtable-manager "ttn" "guile_pg_demo"
'((i serial)
(date timestamp)
(amount float4)
(catcode text)
(details *text))))
((m #:create))
⇒ #<PG-RESULT:COMMAND_OK:0:0>
(define add (m #:insert-col-values))
(define sel (m #:select))
(add '(date amount) (current-time) 1.98)
⇒ #<PG-RESULT:COMMAND_OK:0:0>
(add '(date amount) (current-time) 2.98)
⇒ #<PG-RESULT:COMMAND_OK:0:0>
(define (under n)
(sel '((integer #f (count *))) ;; outspec
#:where `(< amount ,n))) ;; prefix-style expression
(define result (under 2.00))
result
⇒ #<PG-RESULT:TUPLES_OK:1:1>
((m #:tuples-result->object-alist) result)
⇒ ((count 1))
((m #:drop))
⇒ (#<PG-RESULT:COMMAND_OK:0:0> #<PG-RESULT:COMMAND_OK:0:0>)
Two things to note in this example: (1) Both #:create and #:drop
invocations evaluate the operating thunk directly (double parentheses); and
(2) The drop returns a list of result values to accommodate the possibility
(shown here) of a serial column type, which requires an additional drop
of the associated PostgreSQL sequence used to implement the expected
serial behavior. (As of PostgreSQL 7.x, the sequence name is constructed like
so: TABLENAME_COLNAME_seq. For more info, see the PostgreSQL User Guide,
Chapter 3: Data Types.)
Next: Introspection, Previous: Result Transforms, Up: The (database postgres*) Modules [Contents][Index]