chickadee » postgresql » query

Execute QUERY on connection CONN and return a result object. The result object can be read out by several procedures, ranging from the low-level value-at to the high-level row-fold. See the High-level API and Low-level result API sections below for information on how to read out result objects.

QUERY can either be a string or a symbol. If it is a string, it should contain an SQL query to execute. This query can contain placeholders like $1, $2 etc, which refer to positional arguments in PARAMS. For example:

(use postgresql)

(let ((conn (connect '((dbname . test)))))
  (row-values (query conn "SELECT $1::text, 2::int2" "hello")))
 => ("hello" 2)

If QUERY is a symbol, it must match the name of a prepared statement you created earlier. The number of parameters passed as PARAMS must match the number of placeholders used when the statement was prepared.

To actually create a prepared statement, you can simply use the query procedure with an SQL PREPARE statement. The placeholders in that statement are deferred until execute time. It allows no parameters to be sent at preparation time, which is a limitation in the PostgreSQL protocol itself. You could use escape-string if you really must pass in dynamic values when preparing the statement.

(use postgresql)

(let ((conn (connect '((dbname . test)))))
  ;; Can't pass any arguments here:
  (query conn "PREPARE mystmt (text, int) AS SELECT $1, $2")
  ;; They are deferred until statement execution time:
  (row-values (query conn 'mystmt "hi" 42)))
 => ("hi" 42)  ; a list of all values at row 0 (see "row-values" below)

(let ((conn (connect '((dbname . test)))))
  ;; If we absolutely need dynamic values, we can escape them manually:
  (query conn (sprintf "PREPARE mystmt (text) AS SELECT $1, '~A', 'bar'"
		       (escape-string conn "foo")))
  (row-values (query conn 'mystmt "hi")))
 => ("hi" "foo" "bar")

As you can see from the examples above, PostgreSQL types are automatically converted to corresponding Scheme types. This can be extended to support your own user-defined types, see the section about type-conversion below for more information on how to do that.