1<?xml version="1.0" encoding="UTF-8"?>
2
3<section id="postgresql"><title>PostgreSQL Database Access</title>
4
5<para>This package offers an &ffi-pac;-based interface to
6 &postgresql-link;.</para>
7<para>The package <quote role="package">SQL</quote>
8 (nicknamed <quote role="package">POSTGRES</quote>
9 and <quote role="package">POSTGRESQL</quote>) is &case-sensitive-k;,
10 so you would write <code>(sql:PQconnectdb ...)</code>
11 when you need to call <function role="pq">PQconnectdb</function>.</para>
12
13<simpara>When this module is present, &features-my;
14 contains the symbol <constant>:POSTGRESQL</constant>.</simpara>
15
16<para>See <filename role="clisp-cvs">modules/postgresql/test.tst</filename>
17 for sample usage.</para>
18
19<para>Additionally, some higher level functionality is available (defined
20 in <filename role="clisp-cvs">modules/postgresql/sql.lisp</filename>):</para>
21
22<variablelist>
23<varlistentry id="sql:pq-finish"><term><code>(sql:pq-finish
24   &conn-r;)</code></term>
25 <listitem><simpara><function role="pq">PQfinish</function> the &conn-r;
26   and mark it as invalid</simpara></listitem></varlistentry>
27<varlistentry id="sql:pq-clear"><term><code>(sql:pq-clear
28   &res-r;)</code></term>
29 <listitem><simpara><function role="pq">PQclear</function> the &res-r;
30   and mark it as invalid</simpara></listitem></varlistentry>
31<varlistentry><term><code>(sql:sql-error &conn-r; &res-r; &fmt-r;
32   &rest-amp; &args-r;)</code></term>
33 <listitem><simpara>finalize &conn-r; and &res-r; and &signal; an
34   appropriate &error-t;</simpara></listitem></varlistentry>
35<varlistentry id="sql:sql-connect"><term><code>(sql:sql-connect
36   &key-amp; host port options tty name login password)</code></term>
37 <listitem><simpara>call <function role="pq">PQsetdbLogin</function> and
38   return the &conn-r;</simpara></listitem></varlistentry>
39<varlistentry id="sql:with-sql-connection"><term><code>(sql:with-sql-connection
40   (&var-r; &rest-amp; &option-r;s &key-amp; &log-r; &allow-other-keys-amp;)
41   &body-amp; &body-r;)</code></term>
42 <listitem><procedure><step><simpara>bind <varname>*sql-log*</varname>
43     to the &log-r; argument</simpara></step>
44   <step><simpara>call <function>sql:sql-connect</function> on
45     &option-r;s and bind &var-r; to the result</simpara></step>
46   <step><simpara>execute &body-r;</simpara></step>
47   <step><simpara>call <function>sql:pq-finish</function> on
48     &var-r;</simpara></step></procedure></listitem></varlistentry>
49<varlistentry><term><code>(sql:sql-transaction &conn-r; &command-r;
50   &status-r; &optional-amp; (clear-p &t;))</code></term>
51 <listitem><simpara>execute the &command-r; via &conn-r;;
52   if the status does not match &status-r;, &err-sig;;
53   if <replaceable>clear-p</replaceable> is
54   non-&nil; <function>sql:pq-clear</function> the &res-r;;
55   otherwise return it</simpara></listitem></varlistentry>
56<varlistentry><term><code>(sql:with-sql-transaction (&res-r; &conn-r;
57   &command-r; status) &body-amp; &body-r;)</code></term>
58 <listitem><simpara>execure the &body-r; on the &res-r; of &command-r;,
59   then <function>sql:pq-clear</function> the &res-r;
60</simpara></listitem></varlistentry>
61<varlistentry id="sql:sql-login"><term><code>sql:*sql-login*</code></term>
62 <listitem><simpara>the default <replaceable>login</replaceable>
63   argument to <function>sql:sql-connect</function>
64   (initially set to <literal role="data">"postgres"</literal>)
65</simpara></listitem></varlistentry>
66<varlistentry id="sql:sql-password"><term><code>sql:*sql-password*</code></term>
67 <listitem><simpara>the default &pass-r;
68   argument to <function>sql:sql-connect</function>
69   (initially set to <literal role="data">"postgres"</literal>)
70</simpara></listitem></varlistentry>
71<varlistentry id="sql:sql-log"><term><code>sql:*sql-log*</code></term>
72 <listitem><simpara>when non-&nil;, should be a &stream-t;;
73   <function>sql:sql-connect</function>
74   and <function>sql:sql-transaction</function>
75   will write to it (initially set to &nil;)
76</simpara></listitem></varlistentry></variablelist>
77
78<warning><para>Since <function role="pq">PQfinish</function>
79  and <function role="pq">PQclear</function>
80  cannot be called on the same pointer twice, one needs to track their
81  validity (<function>sql:sql-connect</function>
82  and <function>sql:sql-transaction</function> take care of that).
83  See <xref linkend="ex-dffi-validity"/>.</para></warning>
84
85<para>For <function role="pq">PQtrace</function>, see
86 <xref linkend="stdio"/>.</para>
87
88</section>
89