2 Quick Start

To access a PostgreSQL database from Guile, first load the postgres database module. This is done using the use-modules syntax:

(use-modules (database postgres))

The next step is to make a connection to the database using the pg-connectdb procedure. Then, having created a valid connection to a database server, we can use the pg-exec procedure to execute queries and the other pg- procedures to retrieve the results of the queries.

The following Guile session opens a connection and executes an SQL statement on the test database on the default host (either localhost or the host specified in the PGHOST environment variable.)

guile> (use-modules (database postgres))
guile> (define test (pg-connectdb "dbname=test"))
guile> test
#<PG-CONN:test::5432:>
guile> (define result (pg-exec test "SELECT * FROM PEOPLE"))
guile> result
#<PG-RESULT:TUPLES_OK:3:5>

The test object is a PG-CONN type representing the database connection. Displaying the PG-CONN object shows that the serial number of the connection is 1, that the database name is test on the default host (the hostname field is empty) on port number 5432 and that the default options were passed to the back-end. This object is passed to pg-exec which returns a PG-RESULT object representing the result of the SQL statement executed on the server. In this case, displaying the PG-RESULT object shows us that the serial number of the result is 2, that the return code from the database was PGRES_TUPLES_OK and that we have received 3 tuples with 5 fields.

Having obtained a result object from the database we can find out how many rows and how many columns are in the result. We can also find out the names of the columns:

guile> (pg-ntuples result)
3
guile> (pg-nfields result)
5
guile> (pg-fname result 0)
"surname"
guile> (pg-fname result 1)
"firstname"

To retrieve the data from the result we use the pg-getvalue procedure. This takes a result object along with the row and column numbers and returns a string containing that value. pg-getvalue always returns a string: it is up to the caller to convert this to whatever Scheme object they require.

guile> (pg-getvalue result 0 0)
"Bitdiddle"
guile> (pg-getvalue result 0 1)
"Ben"
guile> (pg-getvalue result 1 0)
"Ator"
guile> (pg-getvalue result 1 1)
"Eva Lu"

Guile-PG connections are closed when Guile’s garbage collector collects the discarded object. This only happens when the connection itself and all of the results of pg-exec are unreachable. So to close the connection we just rebind the results from that connection and the connection symbol itself:

guile> (define result '())
guile> (define test '())

Typically though the result and connection variables will be let bindings so the connection will usually be closed soon after the body of the let returns.