File: cl-postgres.html

package info (click to toggle)
cl-postmodern 20211113.git9d4332f-3
links: PTS, VCS
area: main
in suites: bookworm
size: 3,524 kB
sloc: lisp: 22,909; sql: 76; makefile: 2
file content (1427 lines) | stat: -rw-r--r-- 59,124 bytes
parent folder | download | duplicates (2)
<!DOCTYPE html>
<html lang="en">
<head>
<!-- 2021-08-03 Tue 10:58 -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Cl-Postgres Reference Manual</title>
<meta name="generator" content="Org mode">
<meta name="author" content="Sabra Crolleton">
<style type="text/css">
 <!--/*--><![CDATA[/*><!--*/
  .title  { text-align: center;
             margin-bottom: .2em; }
  .subtitle { text-align: center;
              font-size: medium;
              font-weight: bold;
              margin-top:0; }
  .todo   { font-family: monospace; color: red; }
  .done   { font-family: monospace; color: green; }
  .priority { font-family: monospace; color: orange; }
  .tag    { background-color: #eee; font-family: monospace;
            padding: 2px; font-size: 80%; font-weight: normal; }
  .timestamp { color: #bebebe; }
  .timestamp-kwd { color: #5f9ea0; }
  .org-right  { margin-left: auto; margin-right: 0px;  text-align: right; }
  .org-left   { margin-left: 0px;  margin-right: auto; text-align: left; }
  .org-center { margin-left: auto; margin-right: auto; text-align: center; }
  .underline { text-decoration: underline; }
  #postamble p, #preamble p { font-size: 90%; margin: .2em; }
  p.verse { margin-left: 3%; }
  pre {
    border: 1px solid #ccc;
    box-shadow: 3px 3px 3px #eee;
    padding: 8pt;
    font-family: monospace;
    overflow: auto;
    margin: 1.2em;
  }
  pre.src {
    position: relative;
    overflow: auto;
    padding-top: 1.2em;
  }
  pre.src:before {
    display: none;
    position: absolute;
    background-color: white;
    top: -10px;
    right: 10px;
    padding: 3px;
    border: 1px solid black;
  }
  pre.src:hover:before { display: inline; margin-top: 14px;}
  /* Languages per Org manual */
  pre.src-asymptote:before { content: 'Asymptote'; }
  pre.src-awk:before { content: 'Awk'; }
  pre.src-C:before { content: 'C'; }
  /* pre.src-C++ doesn't work in CSS */
  pre.src-clojure:before { content: 'Clojure'; }
  pre.src-css:before { content: 'CSS'; }
  pre.src-D:before { content: 'D'; }
  pre.src-ditaa:before { content: 'ditaa'; }
  pre.src-dot:before { content: 'Graphviz'; }
  pre.src-calc:before { content: 'Emacs Calc'; }
  pre.src-emacs-lisp:before { content: 'Emacs Lisp'; }
  pre.src-fortran:before { content: 'Fortran'; }
  pre.src-gnuplot:before { content: 'gnuplot'; }
  pre.src-haskell:before { content: 'Haskell'; }
  pre.src-hledger:before { content: 'hledger'; }
  pre.src-java:before { content: 'Java'; }
  pre.src-js:before { content: 'Javascript'; }
  pre.src-latex:before { content: 'LaTeX'; }
  pre.src-ledger:before { content: 'Ledger'; }
  pre.src-lisp:before { content: 'Lisp'; }
  pre.src-lilypond:before { content: 'Lilypond'; }
  pre.src-lua:before { content: 'Lua'; }
  pre.src-matlab:before { content: 'MATLAB'; }
  pre.src-mscgen:before { content: 'Mscgen'; }
  pre.src-ocaml:before { content: 'Objective Caml'; }
  pre.src-octave:before { content: 'Octave'; }
  pre.src-org:before { content: 'Org mode'; }
  pre.src-oz:before { content: 'OZ'; }
  pre.src-plantuml:before { content: 'Plantuml'; }
  pre.src-processing:before { content: 'Processing.js'; }
  pre.src-python:before { content: 'Python'; }
  pre.src-R:before { content: 'R'; }
  pre.src-ruby:before { content: 'Ruby'; }
  pre.src-sass:before { content: 'Sass'; }
  pre.src-scheme:before { content: 'Scheme'; }
  pre.src-screen:before { content: 'Gnu Screen'; }
  pre.src-sed:before { content: 'Sed'; }
  pre.src-sh:before { content: 'shell'; }
  pre.src-sql:before { content: 'SQL'; }
  pre.src-sqlite:before { content: 'SQLite'; }
  /* additional languages in org.el's org-babel-load-languages alist */
  pre.src-forth:before { content: 'Forth'; }
  pre.src-io:before { content: 'IO'; }
  pre.src-J:before { content: 'J'; }
  pre.src-makefile:before { content: 'Makefile'; }
  pre.src-maxima:before { content: 'Maxima'; }
  pre.src-perl:before { content: 'Perl'; }
  pre.src-picolisp:before { content: 'Pico Lisp'; }
  pre.src-scala:before { content: 'Scala'; }
  pre.src-shell:before { content: 'Shell Script'; }
  pre.src-ebnf2ps:before { content: 'ebfn2ps'; }
  /* additional language identifiers per "defun org-babel-execute"
       in ob-*.el */
  pre.src-cpp:before  { content: 'C++'; }
  pre.src-abc:before  { content: 'ABC'; }
  pre.src-coq:before  { content: 'Coq'; }
  pre.src-groovy:before  { content: 'Groovy'; }
  /* additional language identifiers from org-babel-shell-names in
     ob-shell.el: ob-shell is the only babel language using a lambda to put
     the execution function name together. */
  pre.src-bash:before  { content: 'bash'; }
  pre.src-csh:before  { content: 'csh'; }
  pre.src-ash:before  { content: 'ash'; }
  pre.src-dash:before  { content: 'dash'; }
  pre.src-ksh:before  { content: 'ksh'; }
  pre.src-mksh:before  { content: 'mksh'; }
  pre.src-posh:before  { content: 'posh'; }
  /* Additional Emacs modes also supported by the LaTeX listings package */
  pre.src-ada:before { content: 'Ada'; }
  pre.src-asm:before { content: 'Assembler'; }
  pre.src-caml:before { content: 'Caml'; }
  pre.src-delphi:before { content: 'Delphi'; }
  pre.src-html:before { content: 'HTML'; }
  pre.src-idl:before { content: 'IDL'; }
  pre.src-mercury:before { content: 'Mercury'; }
  pre.src-metapost:before { content: 'MetaPost'; }
  pre.src-modula-2:before { content: 'Modula-2'; }
  pre.src-pascal:before { content: 'Pascal'; }
  pre.src-ps:before { content: 'PostScript'; }
  pre.src-prolog:before { content: 'Prolog'; }
  pre.src-simula:before { content: 'Simula'; }
  pre.src-tcl:before { content: 'tcl'; }
  pre.src-tex:before { content: 'TeX'; }
  pre.src-plain-tex:before { content: 'Plain TeX'; }
  pre.src-verilog:before { content: 'Verilog'; }
  pre.src-vhdl:before { content: 'VHDL'; }
  pre.src-xml:before { content: 'XML'; }
  pre.src-nxml:before { content: 'XML'; }
  /* add a generic configuration mode; LaTeX export needs an additional
     (add-to-list 'org-latex-listings-langs '(conf " ")) in .emacs */
  pre.src-conf:before { content: 'Configuration File'; }

  table { border-collapse:collapse; }
  caption.t-above { caption-side: top; }
  caption.t-bottom { caption-side: bottom; }
  td, th { vertical-align:top;  }
  th.org-right  { text-align: center;  }
  th.org-left   { text-align: center;   }
  th.org-center { text-align: center; }
  td.org-right  { text-align: right;  }
  td.org-left   { text-align: left;   }
  td.org-center { text-align: center; }
  dt { font-weight: bold; }
  .footpara { display: inline; }
  .footdef  { margin-bottom: 1em; }
  .figure { padding: 1em; }
  .figure p { text-align: center; }
  .equation-container {
    display: table;
    text-align: center;
    width: 100%;
  }
  .equation {
    vertical-align: middle;
  }
  .equation-label {
    display: table-cell;
    text-align: right;
    vertical-align: middle;
  }
  .inlinetask {
    padding: 10px;
    border: 2px solid gray;
    margin: 10px;
    background: #ffffcc;
  }
  #org-div-home-and-up
   { text-align: right; font-size: 70%; white-space: nowrap; }
  textarea { overflow-x: auto; }
  .linenr { font-size: smaller }
  .code-highlighted { background-color: #ffff00; }
  .org-info-js_info-navigation { border-style: none; }
  #org-info-js_console-label
    { font-size: 10px; font-weight: bold; white-space: nowrap; }
  .org-info-js_search-highlight
    { background-color: #ffff00; color: #000000; font-weight: bold; }
  .org-svg { width: 90%; }
  /*]]>*/-->
</style>
<link rel="stylesheet" type="text/css" href="style.css" />
<style>pre.src{background:#343131;color:white;} </style>
<script type="text/javascript">
// @license magnet:?xt=urn:btih:e95b018ef3580986a04669f1b5879592219e2a7a&dn=public-domain.txt Public Domain
<!--/*--><![CDATA[/*><!--*/
     function CodeHighlightOn(elem, id)
     {
       var target = document.getElementById(id);
       if(null != target) {
         elem.classList.add("code-highlighted");
         target.classList.add("code-highlighted");
       }
     }
     function CodeHighlightOff(elem, id)
     {
       var target = document.getElementById(id);
       if(null != target) {
         elem.classList.remove("code-highlighted");
         target.classList.remove("code-highlighted");
       }
     }
    /*]]>*///-->
// @license-end
</script>
</head>
<body>
<div id="content">
<header>
<h1 class="title">Cl-Postgres Reference Manual</h1>
</header><nav id="table-of-contents">
<h2>Table of Contents</h2>
<div id="text-table-of-contents">
<ul>
<li><a href="#connecting">Connecting</a>
<ul>
<li><a href="#class-database-connection">class database-connection</a></li>
<li><a href="#function-open-databasec">function open-database (database user password host &amp;optional (port 5432) (use-ssl :no) (service "postgres")(application-name "") use-binary)</a></li>
<li><a href="#function-close-database">function close-database (database-connection)</a></li>
<li><a href="#function-reopen-database">function reopen-database (database-connection)</a></li>
<li><a href="#function-database-open-p">function database-open-p (database-connection)</a></li>
<li><a href="#method-conection-meta">method connection-meta (database-connection)</a></li>
<li><a href="#method-connection-parameters">method connection-parameters (database-connection)</a></li>
<li><a href="#variable-unix-socket-dir">variable <code>*unix-socket-dir*</code></a></li>
<li><a href="#variable-ssl-certificate-file">variable <code>*ssl-certificate-file*</code></a></li>
<li><a href="#variable-ssl-key-files">variable <code>*ssl-key-file*</code></a></li>
<li><a href="#variable-on-evidence-of-man-in-the-middle-attack">variable <code>*on-evidence-of-man-in-the-middle-attack*</code></a></li>
<li><a href="#variable-retry-connect-times">variable <code>*retry-connect-times*</code> (5)</a></li>
<li><a href="#variable-retry-connect-delay">variable <code>*retry-connect-delay*</code> (0.5)</a></li>
<li><a href="#function-wait-for-notification">function wait-for-notification (database-connection)</a></li>
<li><a href="#function-get-postgresql-version">function get-postgresql-version (database-connection)</a></li>
<li><a href="#function-postgresql-version-at-least">function postgresql-version-at-least (desired-version connection)</a></li>
</ul>
</li>
<li><a href="#querying">Querying</a>
<ul>
<li><a href="#function-exec-query">function exec-query (database-connection query &amp;optional (row-reader 'ignore-row-reader))</a></li>
<li><a href="#function-prepare-query">function prepare-query (database-connection name query)</a></li>
<li><a href="#function-exec-prepared">function exec-prepared (database-connection name parameters &amp;optional (row-reader 'ignore-row-reader))</a></li>
<li><a href="#function-unprepare-query">function unprepare-query (database-connection name)</a></li>
<li><a href="#method-to-sql-string">method to-sql-string (value)</a></li>
<li><a href="#variable-silently-truncate-ratios">variable <code>*silently-truncate-ratios*</code></a></li>
<li><a href="#variable-query-log">variable <code>*query-log*</code></a></li>
<li><a href="#variable-query-callback">variable <code>*query-callback*</code></a></li>
<li><a href="#function-log-query">function log-query (query internal-time)</a></li>
</ul>
</li>
<li><a href="#reading-values">Reading values</a>
<ul>
<li><a href="#function-copy-sql-readtable">function copy-sql-readtable (table)</a></li>
<li><a href="#function-default-sql-readtable">function default-sql-readtable ()</a></li>
<li><a href="#function-set-sql-reader">function set-sql-reader (oid function &amp;key table binary-p)</a></li>
<li><a href="#function-set-sql-datetime-readers">function set-sql-datetime-readers (&amp;key date timestamp timestamp-with-timezone time interval table)</a></li>
</ul>
</li>
<li><a href="#row-readers">Row readers</a>
<ul>
<li><a href="#macro-row-reader">macro row-reader ((fields) &amp;body body)</a></li>
<li><a href="#macro-def-row-reader">macro def-row-reader (name (fields) &amp;body body)</a></li>
<li><a href="#method-field-name">method field-name (field)</a></li>
<li><a href="#method-field-type">method field-type (field)</a></li>
<li><a href="#function-list-row-reader">function list-row-reader (socket fields)</a></li>
<li><a href="#function-alist-row-reader">function alist-row-reader (socket fields)</a></li>
<li><a href="#function-ignore-row-reader">function ignore-row-reader (socket fields)</a></li>
</ul>
</li>
<li><a href="#bulk-copying">Bulk Copying</a>
<ul>
<li><a href="#function-open-db-writer">function open-db-writer (db table &amp;optional columns)</a></li>
<li><a href="#function-close-db-writer">function close-db-writer (writer &amp;key abort)</a></li>
<li><a href="#function-db-write-row">function db-write-row (writer row-data)</a></li>
</ul>
</li>
<li><a href="#normalization">Normalization</a>
<ul>
<li><a href="#function-saslprep-normalize">function saslprep-normalize (str &amp;optional form)</a></li>
<li><a href="#function-string-mapped-to-nothing">function string-mapped-to-nothing (str)</a></li>
<li><a href="#function-string-mapped-to-space">function string-mapped-to-space (str)</a></li>
<li><a href="#function-string-printable-ascii-p">function string-printable-ascii-p (str)</a></li>
</ul>
</li>
<li><a href="#conditions">Conditions</a>
<ul>
<li><a href="#condition-database-error">condition database-error</a></li>
<li><a href="#method-database-error-message">method database-error-message (database-error)</a></li>
<li><a href="#method-database-error-detail">method database-error-detail (database-error)</a></li>
<li><a href="#method-database-error-code">method database-error-code (database-error)</a></li>
<li><a href="#method-database-error-query">method database-error-query (database-error)</a></li>
<li><a href="#method-database-error-cause">method database-error-cause (database-error)</a></li>
<li><a href="#function-database-error-constraint-name">function database-error-constraint-name (database-error)</a></li>
<li><a href="#function-database-error-extract-name">function database-error-extract-name (database-error)</a></li>
<li><a href="#condition-database-connection-error">condition database-connection-error</a></li>
<li><a href="#condition-postgresql-notification">condition postgresql-notification</a></li>
<li><a href="#method-postgresql-notification-channel">method postgresql-notification-channel (postgresql-notification)</a></li>
<li><a href="#method-postgresql-notification-payload">method postgresql-notification-payload (postgresql-notification)</a></li>
<li><a href="#method-postgresql-notification-pid">method postgresql-notification-pid (postgresql-notification)</a></li>
</ul>
</li>
</ul>
</div>
</nav>
<p>
The CL-postgres module implements a rather low-level interface for
communicating with a PostgreSQL database server. It is part of the Postmodern
library, but can be used separately.
</p>

<div id="outline-container-connecting" class="outline-2">
<h2 id="connecting">Connecting</h2>
<div class="outline-text-2" id="text-connecting">
</div>
<div id="outline-container-class-database-connection" class="outline-3">
<h3 id="class-database-connection">class database-connection</h3>
<div class="outline-text-3" id="text-class-database-connection">
<p>
Representation of a database connection. Contains login information in order to be able to automatically re-establish a connection when it is somehow closed.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(<span style="color: #ffad29; font-weight: bold;">defclass</span> <span style="color: #34cae2;">database-connection</span> ()
  ((host <span style="color: #23d7d7;">:initarg</span> <span style="color: #23d7d7;">:host</span> <span style="color: #23d7d7;">:reader</span> connection-host)
   (port <span style="color: #23d7d7;">:initarg</span> <span style="color: #23d7d7;">:port</span> <span style="color: #23d7d7;">:reader</span> connection-port)
   (database <span style="color: #23d7d7;">:initarg</span> <span style="color: #23d7d7;">:db</span> <span style="color: #23d7d7;">:reader</span> connection-db)
   (user <span style="color: #23d7d7;">:initarg</span> <span style="color: #23d7d7;">:user</span> <span style="color: #23d7d7;">:reader</span> connection-user)
   (password <span style="color: #23d7d7;">:initarg</span> <span style="color: #23d7d7;">:password</span> <span style="color: #23d7d7;">:reader</span> connection-password)
   (use-ssl <span style="color: #23d7d7;">:initarg</span> <span style="color: #23d7d7;">:ssl</span> <span style="color: #23d7d7;">:reader</span> connection-use-ssl)
   (use-binary <span style="color: #23d7d7;">:initarg</span> <span style="color: #23d7d7;">:binary</span> <span style="color: #23d7d7;">:accessor</span> connection-use-binary <span style="color: #23d7d7;">:initform</span> nil)
   (service <span style="color: #23d7d7;">:initarg</span> <span style="color: #23d7d7;">:service</span> <span style="color: #23d7d7;">:accessor</span> connection-service)
   (application-name <span style="color: #23d7d7;">:initarg</span> <span style="color: #23d7d7;">:application-name</span> <span style="color: #23d7d7;">:accessor</span> connection-application-name)
   (socket <span style="color: #23d7d7;">:initarg</span> <span style="color: #23d7d7;">:socket</span> <span style="color: #23d7d7;">:accessor</span> connection-socket)
   (meta <span style="color: #23d7d7;">:initform</span> nil)
   (available <span style="color: #23d7d7;">:initform</span> t <span style="color: #23d7d7;">:accessor</span> connection-available)
   (parameters <span style="color: #23d7d7;">:accessor</span> connection-parameters)
   (timestamp-format <span style="color: #23d7d7;">:accessor</span> connection-timestamp-format)))
</pre>
</div>
</div>
</div>

<div id="outline-container-function-open-databasec" class="outline-3">
<h3 id="function-open-databasec">function open-database (database user password host &amp;optional (port 5432) (use-ssl :no) (service "postgres")(application-name "") use-binary)</h3>
<div class="outline-text-3" id="text-function-open-databasec">
<p>
→ database-connection
</p>

<p>
Create and open a connection for the specified server, database, and user.
use-ssl may be :no, :try, :require, :yes, or :full
</p>
<ul class="org-ul">
<li>:try means if the server supports it</li>
<li>:require means use provided ssl certificate with no verification</li>
<li>:yes means verify that the server cert is issued by a trusted CA, but does not verify the server hostname</li>
<li>:full means expect a CA-signed cert for the supplied hostname and verify the server hostname</li>
</ul>

<p>
If you set it to anything other than :no be sure to also load the CL+SSL library.
When it is anything but :no, you must have the CL+SSL package loaded to initiate the connection.
</p>

<p>
On SBCL and Clozure CL, the value :unix may be passed for host, in order to
connect using a Unix domain socket instead of a TCP socket.
</p>

<p>
Service defaults to "postgres".
</p>

<p>
Application-name defaults to a blank string. If provided, Postgresl can use it to track applications with multiple connections.
</p>

<p>
Use-binary defaults to nil. This relates to how Postmodern passes the parameters to Postgresql prepared queries. If you want to have cl-postgres pass integer, float or boolean parameters to Postgresql in binary format, then set this optional parameter to t.
</p>

<p>
If a query to Postgresql does not have a table column which would allow Postgresql to determine the correct datatype and you do not specify differently, Postgresql will treat the parameters passed with a prepared query as text. The default cl-postgres connection will have results like the following examples:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(<span style="color: #ffad29; font-weight: bold;">defparameter</span> <span style="color: #dbdb95;">*connect-spec*</span> '(<span style="color: #e67128;">"database_name"</span> <span style="color: #e67128;">"user_name"</span> <span style="color: #e67128;">"user_password"</span> <span style="color: #e67128;">"localhost"</span> 5432 <span style="color: #23d7d7;">:no</span> <span style="color: #e67128;">"postgres"</span> <span style="color: #e67128;">"some_app_name"</span> nil))
(<span style="color: #ffad29; font-weight: bold;">defmacro</span> <span style="color: #00ede1; font-weight: bold;">connect-now</span> (<span style="color: #34cae2;">&amp;body</span> body)
  `(<span style="color: #ffad29; font-weight: bold;">let</span> ((connection (apply 'open-database *connect-spec*)))
     (<span style="color: #ffad29; font-weight: bold;">unwind-protect</span> (<span style="color: #ffad29; font-weight: bold;">progn</span> ,@body)
       (close-database connection))))

(connect-now
 (prepare-query connection <span style="color: #e67128;">"test4"</span> <span style="color: #e67128;">"select $1"</span>)
 (exec-prepared connection <span style="color: #e67128;">"test4"</span> '(42) 'list-row-reader))
((<span style="color: #e67128;">"42"</span>))

(connect-now
 (prepare-query connection <span style="color: #e67128;">"test4"</span> <span style="color: #e67128;">"select $1"</span>)
 (exec-prepared connection <span style="color: #e67128;">"test4"</span> '(T) 'list-row-reader))
((<span style="color: #e67128;">"true"</span>))

(connect-now
 (prepare-query connection <span style="color: #e67128;">"test4"</span> <span style="color: #e67128;">"select $1"</span>)
 (exec-prepared connection <span style="color: #e67128;">"test4"</span> '(nil) 'list-row-reader))
((<span style="color: #e67128;">"false"</span>))
</pre>
</div>
<p>
Still in the default setting, you can specify parameter type as so:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(connect-now
  (prepare-query connection <span style="color: #e67128;">"test1"</span> <span style="color: #e67128;">"select $1::integer"</span>)
  (exec-prepared connection <span style="color: #e67128;">"test1"</span> '(42) 'list-row-reader))
((42))

(connect-now
  (prepare-query connection <span style="color: #e67128;">"test1"</span> <span style="color: #e67128;">"select $1::float"</span>)
  (exec-prepared connection <span style="color: #e67128;">"test1"</span> '(42.53) 'list-row-reader))
((42.53d0))

(connect-now
  (prepare-query connection <span style="color: #e67128;">"test1"</span> <span style="color: #e67128;">"select $1::boolean"</span>)
  (exec-prepared connection <span style="color: #e67128;">"test1"</span> '(T) 'list-row-reader))
((T))

(connect-now
  (prepare-query connection <span style="color: #e67128;">"test1"</span> <span style="color: #e67128;">"select $1::boolean"</span>)
  (exec-prepared connection <span style="color: #e67128;">"test1"</span> '(nil) 'list-row-reader))
((NIL))
</pre>
</div>
<p>
Setting the use-binary slot in the database connection object to t (the last optional parameter) will allow you to pass parameters as binary BUT Postgresql insists on knowing the type of parameters you will be passing in a prepared query. If you do not pass a list of sample parameters, Postgresql will treat all parameters passed in the exec-prepared function required to be text and will throw an error, typically insufficient data left in message. Unlike Postmodern, cl-postgres does not keep a copy of prepared statements in the database connection object. Thus any error checking would require making a second call to Postgresql to find out what it has in the way of prepared statement parameters will lose any efficiency which would otherwise be gained by passing parameters in binary form.
</p>
<div class="org-src-container">
<pre class="src src-lisp">    (setf *connect-spec* '(<span style="color: #e67128;">"database_name"</span> <span style="color: #e67128;">"user-name"</span> <span style="color: #e67128;">"user-password"</span> <span style="color: #e67128;">"localhost"</span> 5432 <span style="color: #23d7d7;">:no</span> <span style="color: #e67128;">"postgres"</span> <span style="color: #e67128;">"some-app-name"</span> t))

  (connect-now
   (prepare-query connection <span style="color: #e67128;">"test6"</span> <span style="color: #e67128;">"select $1, $2, $3"</span> '(1 nil 1.0))
   (exec-prepared connection <span style="color: #e67128;">"test6"</span> '(42 T 127.89) 'list-row-reader))
((42 T 127.89))
</pre>
</div>
<p>
The default for cl-postgres/Postmodern is to continue to pass parameters to Postgresql as text (not in binary format) in order to avoid breaking existing user code. If you want to pass parameters to Postgresql in binary format and want to set that up when you are making the database connection, the following examples may help. We continue the difference in the signatures (cl-postgres using optional parameters and postmodern using keyword parameters) because of the expected downstream breakage if we shifted cl-postgres:open-database to using keyword parameters.
</p>
</div>
</div>

<div id="outline-container-function-close-database" class="outline-3">
<h3 id="function-close-database">function close-database (database-connection)</h3>
<div class="outline-text-3" id="text-function-close-database">
<p>
Close a database connection. It is advisable to call this on connections when
you are done with them. Otherwise the open socket will stick around until it
is garbage collected, and no one will tell the database server that we are done
with it.
</p>
</div>
</div>

<div id="outline-container-function-reopen-database" class="outline-3">
<h3 id="function-reopen-database">function reopen-database (database-connection)</h3>
<div class="outline-text-3" id="text-function-reopen-database">
<p>
Re-establish a database connection for a previously closed connection object.
(Calling this on a connection that is still open is harmless.)
</p>
</div>
</div>

<div id="outline-container-function-database-open-p" class="outline-3">
<h3 id="function-database-open-p">function database-open-p (database-connection)</h3>
<div class="outline-text-3" id="text-function-database-open-p">
<p>
→ boolean
</p>

<p>
Returns a boolean indicating whether the given connection is currently connected.
</p>
</div>
</div>

<div id="outline-container-method-conection-meta" class="outline-3">
<h3 id="method-conection-meta">method connection-meta (database-connection)</h3>
<div class="outline-text-3" id="text-method-conection-meta">
<p>
→ hash-table
</p>

<p>
This method provides access to a hash table that is associated with the
current database connection, and is used to store information about the
prepared statements that have been parsed for this connection.
</p>
</div>
</div>

<div id="outline-container-method-connection-parameters" class="outline-3">
<h3 id="method-connection-parameters">method connection-parameters (database-connection)</h3>
<div class="outline-text-3" id="text-method-connection-parameters">
<p>
→ hash-table
</p>

<p>
This method returns a mapping (string to string) containing all the
configuration parameters for the connection.
</p>
</div>
</div>

<div id="outline-container-variable-unix-socket-dir" class="outline-3">
<h3 id="variable-unix-socket-dir">variable <code>*unix-socket-dir*</code></h3>
<div class="outline-text-3" id="text-variable-unix-socket-dir">
<p>
Directory where the Unix domain socket for PostgreSQL be found.
</p>

<p>
On SBCL, when using the :unix keyword as host argument when creating a
connection, this variable determines the directory in which CL-Postgres
will look for the socket file.
</p>
</div>
</div>

<div id="outline-container-variable-ssl-certificate-file" class="outline-3">
<h3 id="variable-ssl-certificate-file">variable <code>*ssl-certificate-file*</code></h3>
<div class="outline-text-3" id="text-variable-ssl-certificate-file">
</div>
</div>
<div id="outline-container-variable-ssl-key-files" class="outline-3">
<h3 id="variable-ssl-key-files">variable <code>*ssl-key-file*</code></h3>
<div class="outline-text-3" id="text-variable-ssl-key-files">
<p>
When using SSL (see open-database), these can be used to provide client key
and certificate files. They can be either NIL, for no file, or a pathname.
</p>
</div>
</div>

<div id="outline-container-variable-on-evidence-of-man-in-the-middle-attack" class="outline-3">
<h3 id="variable-on-evidence-of-man-in-the-middle-attack">variable <code>*on-evidence-of-man-in-the-middle-attack*</code></h3>
<div class="outline-text-3" id="text-variable-on-evidence-of-man-in-the-middle-attack">
  <p>
    When establishing an SSL connection, Postmodern will check to see if unexpected extra data was received prior to the connection being encrypted. Unexpected extra data may indicate an attempted man-in-the-middle attack. By default, this variable is set to :error. You can set the response to be a simple warning (by setting this to :warn) or you can set this to :ignore.
</p>
</div>
</div>

<div id="outline-container-variable-retry-connect-times" class="outline-3">
<h3 id="variable-retry-connect-times">variable <code>*retry-connect-times*</code> (5)</h3>
<div class="outline-text-3" id="text-variable-retry-connect-times">
<p>
How many times do we try to connect again. Borrowed from pgloader
</p>
</div>
</div>

<div id="outline-container-variable-retry-connect-delay" class="outline-3">
<h3 id="variable-retry-connect-delay">variable <code>*retry-connect-delay*</code> (0.5)</h3>
<div class="outline-text-3" id="text-variable-retry-connect-delay">
<p>
How many seconds to wait before trying to connect again. Borrowed from pgloader
</p>
</div>
</div>

<div id="outline-container-function-wait-for-notification" class="outline-3">
<h3 id="function-wait-for-notification">function wait-for-notification (database-connection)</h3>
<div class="outline-text-3" id="text-function-wait-for-notification">
<p>
This function blocks until asynchronous notification is received on the connection. Retrun the channel string, the payload and notifying pid as multiple values. The PostgreSQL LISTEN command must be used to enable listening for notifications.
</p>
</div>
</div>

<div id="outline-container-function-get-postgresql-version" class="outline-3">
<h3 id="function-get-postgresql-version">function get-postgresql-version (database-connection)</h3>
<div class="outline-text-3" id="text-function-get-postgresql-version">
<p>
This function returns the version of the connected postgresql instance as a string.
</p>
</div>
</div>

<div id="outline-container-function-postgresql-version-at-least" class="outline-3">
<h3 id="function-postgresql-version-at-least">function postgresql-version-at-least (desired-version connection)</h3>
<div class="outline-text-3" id="text-function-postgresql-version-at-least">
<p>
Takes a postgresql version number which should be a string with the major and minor versions separated by a period e.g. '12.2' or '9.6.17'. Checks against the connection understanding of the running postgresql version and returns t if the running version is the requested version or newer.
</p>
</div>
</div>
</div>
<div id="outline-container-querying" class="outline-2">
<h2 id="querying">Querying</h2>
<div class="outline-text-2" id="text-querying">
</div>
<div id="outline-container-function-exec-query" class="outline-3">
<h3 id="function-exec-query">function exec-query (database-connection query &amp;optional (row-reader 'ignore-row-reader))</h3>
<div class="outline-text-3" id="text-function-exec-query">
<p>
→ result
</p>

<p>
Sends the given query to the given connection, and interprets the results (if
there are any) with the given row-reader. If the database returns information
about the amount of rows affected, this is returned as a second value.
</p>

<p>
Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(exec-query connection <span style="color: #e67128;">"select 1"</span> 'list-row-reader)
'((1))

(exec-query connection <span style="color: #e67128;">"select name from employees where id=3"</span> 'list-row-reader)
</pre>
</div>
</div>
</div>

<div id="outline-container-function-prepare-query" class="outline-3">
<h3 id="function-prepare-query">function prepare-query (database-connection name query)</h3>
<div class="outline-text-3" id="text-function-prepare-query">
<p>
Parse and plan the given query, and store it under the given name. Note that
prepared statements are per-connection, so they can only be executed through
the same connection that prepared them. Also note that while the Postmodern package
will also stored the prepared query in the connection-meta slot of the connection, but
cl-postgres prepare-query does not. If the name is an empty string, Postgresql will not
store it as a reusable query.
</p>

<p>
If parameters are not passed, Postgresql will assume the parameters will be text. In order to pass integer, float or boolean parameters as binary even when the database-connection is set to use binary parameters, you need to pass a list of parameters with the same
type as you will be using when you call (exec-prepared).
</p>

<p>
The following example shows preparing and executing a query that will accept a boolean parameter:
</p>
<div class="org-src-container">
<pre class="src src-lisp">  (prepare-query connection <span style="color: #e67128;">"test-bool"</span> <span style="color: #e67128;">"select $1"</span> '(t))
  (exec-prepared connection <span style="color: #e67128;">"test-bool"</span> '(nil) 'list-row-reader)

'((nil))
</pre>
</div>
<p>
See also the discussion under open database with respect to the use-binary parameter.
</p>
</div>
</div>

<div id="outline-container-function-exec-prepared" class="outline-3">
<h3 id="function-exec-prepared">function exec-prepared (database-connection name parameters &amp;optional (row-reader 'ignore-row-reader))</h3>
<div class="outline-text-3" id="text-function-exec-prepared">
<p>
→ result
</p>

<p>
Execute the prepared statement by the given name. Parameters should be given
as a list. Each value in this list should be of a type that to-sql-string has
been specialised on. (Byte arrays will be passed in their binary form,
without being put through to-sql-string.) The result of the executing the
statement, if any, is interpreted by the given row reader, and returned.
Again, the number or affected rows is optionally returned as a second value.
</p>

<p>
The following example shows preparing and executing a query that will accept an integer and a float in that order:
</p>
<div class="org-src-container">
<pre class="src src-lisp">  (prepare-query connection <span style="color: #e67128;">"test-prepl"</span> <span style="color: #e67128;">"select $1"</span> '(10 7.4))
  (exec-prepared connection <span style="color: #e67128;">"test-prep1"</span> '(12 4.2) 'list-row-reader)

'((nil))
</pre>
</div>
</div>
</div>

<div id="outline-container-function-unprepare-query" class="outline-3">
<h3 id="function-unprepare-query">function unprepare-query (database-connection name)</h3>
<div class="outline-text-3" id="text-function-unprepare-query">
<p>
Close the prepared query given by name by closing the session connection.
Note: This is not the same as keeping the connection open and sending Postgresql query to deallocate the named prepared query. That can be done with the postmodern package's function:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(drop-prepared-statement (name <span style="color: #34cae2;">&amp;key</span> (location <span style="color: #23d7d7;">:both</span>) (database *database*)
                                       (remove-function t))
</pre>
</div>
</div>
</div>

<div id="outline-container-method-to-sql-string" class="outline-3">
<h3 id="method-to-sql-string">method to-sql-string (value)</h3>
<div class="outline-text-3" id="text-method-to-sql-string">
<p>
→ (values string needs-escaping)
</p>

<p>
Convert a Lisp value to its textual unescaped SQL representation. Returns a
second value indicating whether this value should be escaped if it is to be
put directly into a query. Generally any string is going to be designated to be escaped.
</p>

<p>
You can define to-sql-string methods for your own datatypes if you want to be
able to pass them to exec-prepared. When a non-NIL second value is returned,
this may be T to indicate that the first value should simply be escaped as a
string, or a second string providing a type prefix for the value. (This is
used by S-SQL.)
</p>
</div>
</div>

<div id="outline-container-variable-silently-truncate-ratios" class="outline-3">
<h3 id="variable-silently-truncate-ratios">variable <code>*silently-truncate-ratios*</code></h3>
<div class="outline-text-3" id="text-variable-silently-truncate-ratios">
<p>
Given a ratio, a stream and a digital-length-limit, if <code>*silently-truncate-ratios*</code> is true,
will return a potentially truncated ratio. If false and the digital-length-limit is reached,
it will throw an error noting the loss of precision and offering to continue or reset
<code>*silently-truncate-ratios*</code> to true. Code contributed by Attila Lendvai.
</p>
</div>
</div>

<div id="outline-container-variable-query-log" class="outline-3">
<h3 id="variable-query-log">variable <code>*query-log*</code></h3>
<div class="outline-text-3" id="text-variable-query-log">
<p>
When debugging, it can be helpful to inspect the queries that are being sent
to the database. Set this variable to an output stream value (<code>*standard-output*</code>,
for example) to have CL-postgres log every query it makes.
</p>
</div>
</div>

<div id="outline-container-variable-query-callback" class="outline-3">
<h3 id="variable-query-callback">variable <code>*query-callback*</code></h3>
<div class="outline-text-3" id="text-variable-query-callback">
<p>
When profiling or debugging, the <code>*query-log*</code> may not give enough information,
or reparsing its output may not be feasible. This variable may be set to a
designator of function taking two arguments. This function will be then called
after every query, and receive query string and internal time units (as in
(CL:GET-INTERNAL-REAL-TIME)) spent in query as its arguments.
</p>

<p>
Default value of this variable is 'LOG-QUERY, which takes care of <code>*QUERY-LOG*</code>
processing. If you provide custom query callback and wish to keep <code>*QUERY-LOG*</code>
functionality, you will have to call LOG-QUERY from your callback function
</p>
</div>
</div>

<div id="outline-container-function-log-query" class="outline-3">
<h3 id="function-log-query">function log-query (query internal-time)</h3>
<div class="outline-text-3" id="text-function-log-query">
<p>
This function is default value of <code>*QUERY-CALLBACK*</code> and logs queries
to <code>*QUERY-LOG*</code> if it is not NIL.
</p>
</div>
</div>
</div>

<div id="outline-container-reading-values" class="outline-2">
<h2 id="reading-values">Reading values</h2>
<div class="outline-text-2" id="text-reading-values">
<p>
CL-postgres knows how to convert commonly used PostgreSQL data types to Lisp
values. This table shows the mapping:
</p>

<table>


<colgroup>
<col  class="org-left">

<col  class="org-left">
</colgroup>
<tbody>
<tr>
<td class="org-left">PostgreSQL</td>
<td class="org-left">Lisp</td>
</tr>

<tr>
<td class="org-left">smallint</td>
<td class="org-left">integer</td>
</tr>

<tr>
<td class="org-left">integer</td>
<td class="org-left">integer</td>
</tr>

<tr>
<td class="org-left">bigint</td>
<td class="org-left">integer</td>
</tr>

<tr>
<td class="org-left">numeric</td>
<td class="org-left">ratio</td>
</tr>

<tr>
<td class="org-left">real</td>
<td class="org-left">float</td>
</tr>

<tr>
<td class="org-left">double precision</td>
<td class="org-left">double-float</td>
</tr>

<tr>
<td class="org-left">boolean</td>
<td class="org-left">boolean</td>
</tr>

<tr>
<td class="org-left">varchar</td>
<td class="org-left">string</td>
</tr>

<tr>
<td class="org-left">text</td>
<td class="org-left">string</td>
</tr>

<tr>
<td class="org-left">bytea</td>
<td class="org-left">(vector (unsigned-byte 8))</td>
</tr>

<tr>
<td class="org-left">array</td>
<td class="org-left">array</td>
</tr>
</tbody>
</table>
<p>
The mapping from PostgreSQL types (identified by OID numbers) to the functions
that interpret them is kept in so-called SQL readtables. All types for which
no reader is defined will be returned as string values containing their
PostgreSQL representation.
</p>

<p>
variable <code>*sql-readtable*</code>
</p>

<p>
The exported special var holding the current read table, a hash
mapping OIDs to instances of the type-interpreter class that contain
functions for retreiving values from the database in text, and
possible binary, form.
</p>

<p>
For simple use, you will not have to touch this, but it is possible that code within a Lisp image
requires different readers in different situations, in which case you can create separate read tables.
</p>
</div>

<div id="outline-container-function-copy-sql-readtable" class="outline-3">
<h3 id="function-copy-sql-readtable">function copy-sql-readtable (table)</h3>
<div class="outline-text-3" id="text-function-copy-sql-readtable">
<p>
→ readtable
</p>

<p>
Copies a given readtable.
</p>
</div>
</div>

<div id="outline-container-function-default-sql-readtable" class="outline-3">
<h3 id="function-default-sql-readtable">function default-sql-readtable ()</h3>
<div class="outline-text-3" id="text-function-default-sql-readtable">
<p>
→ readtable
</p>

<p>
Returns the default readtable, containing only the readers defined by
CL-postgres itself.
</p>
</div>
</div>

<div id="outline-container-function-set-sql-reader" class="outline-3">
<h3 id="function-set-sql-reader">function set-sql-reader (oid function &amp;key table binary-p)</h3>
<div class="outline-text-3" id="text-function-set-sql-reader">
<p>
Define a new reader for a given type. table defaults to <code>*sql-readtable*</code>.
The reader function should take a single argument, a string, and transform
that into some kind of equivalent Lisp value. When binary-p is true, the reader
function is supposed to directly read the binary representation of the value.
In most cases this is not recommended, but if you want to use it: provide a
function that takes a binary input stream and an integer (the size of the
value, in bytes), and reads the value from that stream. Note that reading
less or more bytes than the given size will horribly break your connection.
</p>
</div>
</div>

<div id="outline-container-function-set-sql-datetime-readers" class="outline-3">
<h3 id="function-set-sql-datetime-readers">function set-sql-datetime-readers (&amp;key date timestamp timestamp-with-timezone time interval table)</h3>
<div class="outline-text-3" id="text-function-set-sql-datetime-readers">
<p>
Since there is no widely recognised standard way of representing dates and
times in Common Lisp, and reading these from string representation is clunky
and slow, this function provides a way to easily plug in binary readers for
the date, time, timestamp, and interval types. It should be given functions
with the following signatures:
</p>

<ul class="org-ul">
<li>:date (days)</li>
</ul>

<p>
Where days is the amount of days since January 1st, 2000.
</p>

<ul class="org-ul">
<li>:timestamp (useconds)</li>
</ul>

<p>
Timestamps have a microsecond resolution. Again, the zero point is the start
of the year 2000, UTC.
</p>

<ul class="org-ul">
<li>:timestamp-with-timezone</li>
</ul>

<p>
Like :timestamp, but for values of the 'timestamp with time zone' type (which
PostgreSQL internally stores exactly the same as regular timestamps).
</p>

<ul class="org-ul">
<li>:time (useconds)</li>
</ul>

<p>
Refers to a time of day, counting from midnight.
</p>

<ul class="org-ul">
<li>:interval (months days useconds)</li>
</ul>

<p>
An interval is represented as several separate components. The reason that days
and microseconds are separated is that you might want to take leap seconds into
account.
</p>
</div>
</div>
</div>

<div id="outline-container-row-readers" class="outline-2">
<h2 id="row-readers">Row readers</h2>
<div class="outline-text-2" id="text-row-readers">
<p>
Row readers are a way to read and group the results of queries. Roughly, they
are functions that perform the iteration over the rows and cells in the
result, and do something with the returned values.
</p>
</div>

<div id="outline-container-macro-row-reader" class="outline-3">
<h3 id="macro-row-reader">macro row-reader ((fields) &amp;body body)</h3>
<div class="outline-text-3" id="text-macro-row-reader">
<p>
→ function
</p>

<p>
Creates a row-reader, using the given name for the variable. Inside the body
this variable refers to a vector of field descriptions. On top of that, two
local functions are bound, next-row and next-field. The first will start
reading the next row in the result, and returns a boolean indicating whether
there is another row. The second will read and return one field, and should
be passed the corresponding field description from the fields argument as a
parameter.
</p>

<p>
A row reader should take care to iterate over all the rows in a result, and
within each row iterate over all the fields. This means it should contain
an outer loop that calls next-row, and every time next-row returns T it
should iterate over the fields vector and call next-field for every field.
</p>

<p>
The definition of list-row-reader should give you an idea what a row reader
looks like:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(row-reader (fields)
  (<span style="color: #ffad29; font-weight: bold;">loop</span> <span style="color: #23d7d7;">:while</span> (next-row)
        <span style="color: #23d7d7;">:collect</span> (<span style="color: #ffad29; font-weight: bold;">loop</span> <span style="color: #23d7d7;">:for</span> field <span style="color: #23d7d7;">:across</span> fields
                       <span style="color: #23d7d7;">:collect</span> (next-field field))))
</pre>
</div>

<p>
Obviously, row readers should not do things with the database connection
like, say, close it or start a new query, since it still reading out the
results from the current query.
</p>
</div>
</div>

<div id="outline-container-macro-def-row-reader" class="outline-3">
<h3 id="macro-def-row-reader">macro def-row-reader (name (fields) &amp;body body)</h3>
<div class="outline-text-3" id="text-macro-def-row-reader">
<p>
The defun-like variant of row-reader: creates a row reader and gives it a
top-level function name.
</p>
</div>
</div>

<div id="outline-container-method-field-name" class="outline-3">
<h3 id="method-field-name">method field-name (field)</h3>
<div class="outline-text-3" id="text-method-field-name">
<p>
→ string
</p>

<p>
This can be used to get information about the fields read by a row reader.
Given a field description, it returns the name the database associated with
this column.
</p>
</div>
</div>

<div id="outline-container-method-field-type" class="outline-3">
<h3 id="method-field-type">method field-type (field)</h3>
<div class="outline-text-3" id="text-method-field-type">
<p>
→ oid
</p>

<p>
This extracts the PostgreSQL OID associated with this column. You can, if
you really want to, query the pg_types table to find out more about the
types denoted by OIDs.
</p>
</div>
</div>

<div id="outline-container-function-list-row-reader" class="outline-3">
<h3 id="function-list-row-reader">function list-row-reader (socket fields)</h3>
<div class="outline-text-3" id="text-function-list-row-reader">
<p>
→ list
</p>

<p>
A row reader that builds a list of lists from the query results.
</p>
</div>
</div>

<div id="outline-container-function-alist-row-reader" class="outline-3">
<h3 id="function-alist-row-reader">function alist-row-reader (socket fields)</h3>
<div class="outline-text-3" id="text-function-alist-row-reader">
<p>
→ alist
</p>

<p>
A row reader that returns a list of alists, which associate column names with
values.
</p>
</div>
</div>

<div id="outline-container-function-ignore-row-reader" class="outline-3">
<h3 id="function-ignore-row-reader">function ignore-row-reader (socket fields)</h3>
<div class="outline-text-3" id="text-function-ignore-row-reader">
<p>
A row reader that completely ignores the result of a query.
</p>
</div>
</div>
</div>

<div id="outline-container-bulk-copying" class="outline-2">
<h2 id="bulk-copying">Bulk Copying</h2>
<div class="outline-text-2" id="text-bulk-copying">
<p>
When loading large amounts of data into PostgreSQL, it can be done
significantly faster using the bulk copying feature. The drawback to this
approach is that you don't find out about data integrity errors until the
entire batch is completed but sometimes the speed is worth it
</p>
</div>

<div id="outline-container-function-open-db-writer" class="outline-3">
<h3 id="function-open-db-writer">function open-db-writer (db table &amp;optional columns)</h3>
<div class="outline-text-3" id="text-function-open-db-writer">
<p>
Opens a table stream into which rows can be written one at a time using
db-write-row. db is either a connection object or a list of arguments that
could be passed to open-database. table is the name of an existing table
into which this writer will write rows. If you don't have data for all
columns, use columns to indicate those that you do.
</p>
</div>
</div>

<div id="outline-container-function-close-db-writer" class="outline-3">
<h3 id="function-close-db-writer">function close-db-writer (writer &amp;key abort)</h3>
<div class="outline-text-3" id="text-function-close-db-writer">
<p>
Closes a bulk writer opened by open-db-writer. Will close the associated
database connection when it was created for this copier, or abort is true.
</p>
</div>
</div>

<div id="outline-container-function-db-write-row" class="outline-3">
<h3 id="function-db-write-row">function db-write-row (writer row-data)</h3>
<div class="outline-text-3" id="text-function-db-write-row">
<p>
Writes row-data into the table and columns referenced by the writer.
row-data is a list of Lisp objects, one for each column included when
opening the writer. Arrays (the elements of which must all be the same type)
will be serialized into their PostgreSQL representation before being written
into the DB.
</p>
</div>
</div>
</div>
<div id="outline-container-normalization" class="outline-2">
<h2 id="normalization">Normalization</h2>
<div class="outline-text-2" id="text-normalization">
</div>
<div id="outline-container-function-saslprep-normalize" class="outline-3">
<h3 id="function-saslprep-normalize">function saslprep-normalize (str &amp;optional form)</h3>
<div class="outline-text-3" id="text-function-saslprep-normalize">
<p>
→ string
</p>

<p>
Scans string. If any character should be mapped to nothing, it eliminates that character. If any character is not printable ascii, it returns nil. If every character remaining after eliminations is printable ascii, it returns the printable-ascii string. It then calls (uax-15:normalize str form) to normalize the string based on the provided unicode form, defaulting to :nfkc.
</p>
</div>
</div>

<div id="outline-container-function-string-mapped-to-nothing" class="outline-3">
<h3 id="function-string-mapped-to-nothing">function string-mapped-to-nothing (str)</h3>
<div class="outline-text-3" id="text-function-string-mapped-to-nothing">
<p>
→ string
</p>

<p>
Reads a string and removes any character that should be mapped to nothing per RFC 3454 and RFC 4013.
</p>
</div>
</div>

<div id="outline-container-function-string-mapped-to-space" class="outline-3">
<h3 id="function-string-mapped-to-space">function string-mapped-to-space (str)</h3>
<div class="outline-text-3" id="text-function-string-mapped-to-space">
<p>
→ string
</p>

<p>
Reads a string and converts any character which should be mapped to a space per RFC 3454 and RFC 4013 to a space.
</p>
</div>
</div>

<div id="outline-container-function-string-printable-ascii-p" class="outline-3">
<h3 id="function-string-printable-ascii-p">function string-printable-ascii-p (str)</h3>
<div class="outline-text-3" id="text-function-string-printable-ascii-p">
<p>
→ boolean
</p>

<p>
Returns t if every character in the string is printable ascii.
</p>
</div>
</div>
</div>
<div id="outline-container-conditions" class="outline-2">
<h2 id="conditions">Conditions</h2>
<div class="outline-text-2" id="text-conditions">
<p>
Opening or querying a database may raise errors. CL-postgres will wrap the
errors that the server returns in a lisp condition, and raise conditions of
the same type when it detects some problem itself. Socket errors are let
through as they are.
</p>
</div>

<div id="outline-container-condition-database-error" class="outline-3">
<h3 id="condition-database-error">condition database-error</h3>
<div class="outline-text-3" id="text-condition-database-error">
<p>
This is the condition type that will be used to signal virtually all database-related errors \(though in some cases
socket errors may be raised when a connection fails on the IP level). For errors that you may want to catch by type, the cl-postgres-error package defines a bucket of subtypes used for specific errors. See the cl-postgres/package.lisp file for a list.
</p>
</div>
</div>

<div id="outline-container-method-database-error-message" class="outline-3">
<h3 id="method-database-error-message">method database-error-message (database-error)</h3>
<div class="outline-text-3" id="text-method-database-error-message">
<p>
→ string
</p>

<p>
The primary human-readable error message. This should be accurate but terse (typically one line). Always present.
</p>
</div>
</div>

<div id="outline-container-method-database-error-detail" class="outline-3">
<h3 id="method-database-error-detail">method database-error-detail (database-error)</h3>
<div class="outline-text-3" id="text-method-database-error-detail">
<p>
→ string
</p>

<p>
Detail: an optional secondary error message carrying more detail about the problem. Might run to multiple lines or NIL if none is available.
</p>
</div>
</div>

<div id="outline-container-method-database-error-code" class="outline-3">
<h3 id="method-database-error-code">method database-error-code (database-error)</h3>
<div class="outline-text-3" id="text-method-database-error-code">
<p>
→ string
</p>

<p>
Code: the Postgresql SQLSTATE code for the error (see the Postgresql Manual Appendix A for their meaning). Not localizable. Always present.
</p>
</div>
</div>

<div id="outline-container-method-database-error-query" class="outline-3">
<h3 id="method-database-error-query">method database-error-query (database-error)</h3>
<div class="outline-text-3" id="text-method-database-error-query">
<p>
→ string
</p>

<p>
The query that led to this error, or NIL if no query was involved.
</p>
</div>
</div>

<div id="outline-container-method-database-error-cause" class="outline-3">
<h3 id="method-database-error-cause">method database-error-cause (database-error)</h3>
<div class="outline-text-3" id="text-method-database-error-cause">
<p>
→ condition
</p>

<p>
The condition that caused this error, or NIL when it was not caused by another condition.
</p>
</div>
</div>

<div id="outline-container-function-database-error-constraint-name" class="outline-3">
<h3 id="function-database-error-constraint-name">function database-error-constraint-name (database-error)</h3>
<div class="outline-text-3" id="text-function-database-error-constraint-name">
<p>
→ string
</p>

<p>
For integrity-violation error, given a database-error for an integrity violation, will attempt to
extract and return the constraint name (or nil if no constraint was found).
</p>
</div>
</div>

<div id="outline-container-function-database-error-extract-name" class="outline-3">
<h3 id="function-database-error-extract-name">function database-error-extract-name (database-error)</h3>
<div class="outline-text-3" id="text-function-database-error-extract-name">
<p>
→ string
</p>

<p>
For various errors, returns the name provided by the error message
 (or nil if no such name was found.)
</p>
</div>
</div>

<div id="outline-container-condition-database-connection-error" class="outline-3">
<h3 id="condition-database-connection-error">condition database-connection-error</h3>
<div class="outline-text-3" id="text-condition-database-connection-error">
<p>
Subtype of database-error. An error of this type (or one of its subclasses)
is signaled when a query is attempted with a connection object that is no
longer connected, or a database connection becomes invalid during a query.
Always provides a :reconnect restart, which will cause the library to make an
attempt to restore the connection and re-try the query.
</p>

<p>
The following shows an example use of this feature, a way to ensure that the
first connection error causes a reconnect attempt, while others pass through
as normal. A variation on this theme could continue trying to reconnect, with
successively longer pauses.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(<span style="color: #ffad29; font-weight: bold;">defun</span> <span style="color: #00ede1; font-weight: bold;">call-with-single-reconnect</span> (fun)
  (<span style="color: #ffad29; font-weight: bold;">let</span> ((reconnected nil))
    (<span style="color: #ffad29; font-weight: bold;">handler-bind</span>
        ((database-connection-error
          (<span style="color: #ffad29; font-weight: bold;">lambda</span> (err)
            (<span style="color: #ffad29; font-weight: bold;">when</span> (not reconnected)
              (setf reconnected t)
              (invoke-restart <span style="color: #23d7d7;">:reconnect</span>)))))
      (funcall fun))))
</pre>
</div>
</div>
</div>

<div id="outline-container-condition-postgresql-notification" class="outline-3">
<h3 id="condition-postgresql-notification">condition postgresql-notification</h3>
<div class="outline-text-3" id="text-condition-postgresql-notification">
<p>
The condition that is signalled when a notification message is received from
the PostgreSQL server. This is a WARNING condition which is caught by the
WAIT-FOR-NOTIFICATION function that implements synchronous waiting for
notifications.
</p>
</div>
</div>

<div id="outline-container-method-postgresql-notification-channel" class="outline-3">
<h3 id="method-postgresql-notification-channel">method postgresql-notification-channel (postgresql-notification)</h3>
<div class="outline-text-3" id="text-method-postgresql-notification-channel">
<p>
→ string
</p>

<p>
The channel string of this notification.
</p>
</div>
</div>

<div id="outline-container-method-postgresql-notification-payload" class="outline-3">
<h3 id="method-postgresql-notification-payload">method postgresql-notification-payload (postgresql-notification)</h3>
<div class="outline-text-3" id="text-method-postgresql-notification-payload">
<p>
→ string
</p>

<p>
The payload of this notification.
</p>
</div>
</div>

<div id="outline-container-method-postgresql-notification-pid" class="outline-3">
<h3 id="method-postgresql-notification-pid">method postgresql-notification-pid (postgresql-notification)</h3>
<div class="outline-text-3" id="text-method-postgresql-notification-pid">
<p>
→ integer
</p>

<p>
The process ID of the process that sent the notification.
</p>
</div>
</div>
</div>
</div>
</body>
</html>