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