Previous: Asynchronous Retrieval, Up: Procedures for Retrieving Data [Contents][Index]
The result of a command or query placed by pg-exec satisfies
predicate pg-result?. It may encapsulate an error, row data, or other
status. See Procedures for Retrieving Data.
Guile-PG displays result objects in the following format:
#<PG-RESULT:STATUS:NTUPLES:NFIELDS>
status is the same as would be returned by
pg-result-status except without the PGRES_ (it is
redundant); ntuples and nfields are integers,
the same as would be returned by pg-ntuples and pg-nfields,
respectively. Everything else appears literally as shown here.
Return #t iff obj is a result object
returned by pg-exec.
Return information associated with result, on
fieldcode, a keyword, or #f if either
fieldcode is unrecognized or the field value is
not available for some reason. The type of the
return value depends on fieldcode:
#:severity#:sqlstate#:message-primary#:message-detail#:message-hint#:context#:source-fileA string. The value for #:message-primary is
typically one line, whereas for #:message-detail,
#:message-hint and #:context, it may run
to multiple lines. For #:sqlstate, it is always
five characters long.
#:statement-position#:source-lineAn integer. Statement position counts characters (not bytes), starting from 1.
#:source-functionA symbol.
Return the error message associated with result,
or the empty string if there was no error.
If the installation does not support
PQRESULTERRORMESSAGE, return the empty string.
The returned string has no trailing newlines.
Return the most-recent error message that occurred on the
connection conn, or an empty string.
For backward compatibility, if conn is actually
a result object returned from calling pg-exec,
delegate the call to pg-result-error-message
transparently (new code should call that procedure
directly).
Return the symbolic status of the result
returned by pg-exec.
PGRES_TUPLES_OKThe statement returned zero or more tuples. The number of fields and tuples
returned can be determined from calls to pg-nfields and
pg-ntuples respectively. The value of a given attribute (field)
of a given tuple can be determined by calling pg-getvalue.
PGRES_COMMAND_OKThe statement was a command (INSERT, UPDATE, DELETE
CREATE TABLE, DROP TABLE etc.),
which was executed without error. The number of tuples affected by the
command can be determined by a call to pg-cmdtuples.
PGRES_EMPTY_QUERYIt is not known under which circumstances this result-status is returned.
PGRES_COPY_OUTPGRES_COPY_INThe statement was a COPY <table> TO STDOUT
or COPY <table> FROM STDIN (respectively).
See Procedures for Copying Data.
PGRES_BAD_RESPONSEThis occurs when the libpq interface receives an unexpected response
from the back-end. It indicates a problem with PostgreSQL.
PGRES_NONFATAL_ERRORIt is not known under which circumstances this result-status is returned.
PGRES_FATAL_ERRORThe command was not executable for some reason. This is the returned status when a syntax error is detected in the command, for example.
This contrived example defines a procedure to insert a record into a given
table, returning the number of tuples inserted (always one) or #f if an
error occurred.
(define (insert-one-record conn table record)
(let ((result (pg-exec conn
(format #f "INSERT INTO ~A VALUES ~A"
table record))))
(and result
(eq? 'PGRES_COMMAND_OK (pg-result-status result))
(string->number (pg-cmdtuples result)))))
The procedure could be called as follows
(insert-one-record conn "people" "('Warbucks', 'Oliver')")
Return #t if result contains binary tuple
data, #f otherwise.
The procedures pg-fmod, pg-ftype, pg-fname,
pg-ftable, pg-ftablecol, pg-fformat, pg-ftype
and pg-fsize can all throw an Argument out of range error
if the specified field does not exist in the result.
Return the integer type-specific modification data for the given field (field number num) of result.
Return the number of tuples in result.
Return the number of fields in result.
Return the number of tuples in result affected by a
command. This is a string which is empty in the case of
commands like CREATE TABLE, GRANT, REVOKE
etc., which don’t affect tuples.
If the result is that of an SQL INSERT command,
return the integer OID of the inserted tuple, otherwise
#f.
Return a string containing the canonical lower-case name of the field number num in result. SQL variables and field names are not case-sensitive.
Return the integer field-number corresponding to field
fname (a string) if this exists in result, or -1
otherwise.
Return the OID of the table from which the field num was fetched in result.
Return the column number (within its table) of the column making up field num of result. Column numbers start at zero.
Return an integer indicating the format of field num of result. Zero (0) indicates textual data representation; while one (1) indicates binary.
Return a string containing the value of the attribute sfield, tuple stuple of result. It is up to the caller to convert this to the required type.
This example defines a procedure tuple->alist which returns a given
tuple as an alist, keyed on the field name. It’s not an especially efficient
procedure because it constructs the list of field-names afresh each time it’s
called.
(define (field-names result)
(map (lambda (field)
(pg-fname result field))
(iota (pg-nfields result))))
(define (get-values result tuple)
(map (lambda (field)
(pg-getvalue result tuple field))
(iota (pg-nfields result))))
(define (tuple->alist result tuple)
(map (lambda (n v) (cons (string->symbol n) v))
(field-names result)
(get-values result tuple)))
Field values can be extracted from the tuple using assq-ref, e.g.:
(assq-ref (tuple->alist result 0) 'firstname)
Using this procedure we can define a version of for-each which
iterates through the tuples returned from a given SELECT query:
(define (for-each-tuple proc result)
(let ((end (pg-ntuples result)))
(do ((tuple 0 (1+ tuple)))
((= tuple end))
(proc (tuple->alist result tuple)))))
This implementation of for-each-tuple inherits inefficiency from the
tuple->alist procedure.
The pg-getvalue procedure throws
out-of-range errors if either the tuple or field-number
arguments are out of range.
Return #t if the value of the attribute sfield, tuple
stuple of result, is NULL, #f otherwise.
Return the PostgreSQL internal integer representation of
the type of the given attribute. The integer is actually an
OID (object ID) which can be used as the primary key to
reference a tuple from the system table pg_type.
Throw misc-error if the field num is
not valid for the given result.
This defines a procedure field-type-name which returns the type
name of a given attribute in a string.
A more efficient implementation would be to define
a type of connection which, when opened, issued the query to retrieve the
pg_type system table once. An alist for looking up type names could
then be associated with each connection for faster subsequent access.
(define CONN (pg-connectdb ...))
(define (field-type-name result fnum)
(let ((result (pg-exec
CONN
(format #f "SELECT typname FROM pg_type WHERE oid = ~A"
(pg-ftype result fnum)))))
(or (and result
(eq? 'PGRES_TUPLES_OK (pg-result-status result))
(< 0 (pg-ntuples result))
(pg-getvalue result 0 0))
"")))
Return the size of a result field num in bytes,
or -1 if the field is variable-length.
Return the size in bytes of the value of the attribute sfield, tuple stuple of result.
Previous: Asynchronous Retrieval, Up: Procedures for Retrieving Data [Contents][Index]