The postmodern Reference Manual

This is the postmodern Reference Manual, version 1.33.8, generated automatically by Declt version 4.0 beta 2 "William Riker" on Mon Feb 26 17:36:00 2024 GMT+0.

Table of Contents


1 Introduction


2 Systems

The main system appears first, followed by any subsystem dependency.


2.1 postmodern

PostgreSQL programming API

Maintainer

Sabra Crolleton <>

Author

Marijn Haverbeke <>

Home Page

https://github.com/marijnh/Postmodern

License

zlib

Version

1.33.8

Dependencies
  • alexandria (system).
  • cl-postgres (system).
  • s-sql (system).
  • global-vars (system).
  • split-sequence (system).
  • uiop (system).
  • closer-mop (system)., for feature :postmodern-use-mop
  • bordeaux-threads (system)., for feature :postmodern-thread-safe
Source

postmodern.asd.

Child Component

postmodern (module).


2.2 cl-postgres

Low-level client library for PostgreSQL

Maintainer

Sabra Crolleton <>

Author

Marijn Haverbeke <>

License

zlib

Version

1.33.8

Dependencies
  • md5 (system).
  • split-sequence (system).
  • ironclad (system).
  • cl-base64 (system).
  • uax-15 (system).
  • usocket (system)., for feature (:or :allegro :ccl :clisp :genera :armedbear :cmucl :lispworks :ecl)
  • sb-bsd-sockets (system)., required, for feature :sbcl
Source

cl-postgres.asd.

Child Component

cl-postgres (module).


2.3 s-sql

Lispy DSL for SQL

Maintainer

Sabra Crolleton <>

Author

Marijn Haverbeke <>

License

zlib

Version

1.33.8

Dependencies
Source

s-sql.asd.

Child Component

s-sql (module).


3 Modules

Modules are listed depth-first from the system components tree.


3.1 postmodern/postmodern

Source

postmodern.asd.

Parent Component

postmodern (system).

Child Components

3.2 cl-postgres/cl-postgres

Source

cl-postgres.asd.

Parent Component

cl-postgres (system).

Child Components

3.3 s-sql/s-sql

Source

s-sql.asd.

Parent Component

s-sql (system).

Child Components

4 Files

Files are sorted by type and then listed depth-first from the systems components trees.


4.1 Lisp


4.1.1 postmodern/postmodern.asd

Source

postmodern.asd.

Parent Component

postmodern (system).

ASDF Systems

postmodern.

Packages

postmodern-system.


4.1.2 cl-postgres/cl-postgres.asd

Source

cl-postgres.asd.

Parent Component

cl-postgres (system).

ASDF Systems

cl-postgres.

Packages

cl-postgres-system.

Internals

4.1.3 s-sql/s-sql.asd

Source

s-sql.asd.

Parent Component

s-sql (system).

ASDF Systems

s-sql.

Packages

s-sql-system.


4.1.4 postmodern/postmodern/package.lisp

Source

postmodern.asd.

Parent Component

postmodern (module).

Packages

postmodern.


4.1.5 postmodern/postmodern/config.lisp

Source

postmodern.asd.

Parent Component

postmodern (module).

Public Interface
Internals

4.1.6 postmodern/postmodern/connect.lisp

Dependencies
Source

postmodern.asd.

Parent Component

postmodern (module).

Public Interface
Internals

4.1.7 postmodern/postmodern/json-encoder.lisp

Dependencies
Source

postmodern.asd.

Parent Component

postmodern (module).

Public Interface

encode-json-to-string (function).

Internals

4.1.8 postmodern/postmodern/query.lisp

Dependencies
Source

postmodern.asd.

Parent Component

postmodern (module).

Public Interface
Internals

4.1.9 postmodern/postmodern/prepare.lisp

Dependencies
Source

postmodern.asd.

Parent Component

postmodern (module).

Public Interface
Internals

4.1.10 postmodern/postmodern/roles.lisp

Dependencies
Source

postmodern.asd.

Parent Component

postmodern (module).

Public Interface
Internals

4.1.11 postmodern/postmodern/util.lisp

Dependencies
Source

postmodern.asd.

Parent Component

postmodern (module).

Public Interface
Internals

4.1.12 postmodern/postmodern/transaction.lisp

Dependencies
Source

postmodern.asd.

Parent Component

postmodern (module).

Public Interface
Internals

4.1.13 postmodern/postmodern/namespace.lisp

Dependencies
Source

postmodern.asd.

Parent Component

postmodern (module).

Public Interface
Internals

do-with-schema (function).


4.1.14 postmodern/postmodern/execute-file.lisp

Dependencies
Source

postmodern.asd.

Parent Component

postmodern (module).

Public Interface
Internals

4.1.15 postmodern/postmodern/table.lisp

If Feature

:postmodern-use-mop

Dependencies
Source

postmodern.asd.

Parent Component

postmodern (module).

Public Interface
Internals

4.1.16 postmodern/postmodern/deftable.lisp

Dependencies
Source

postmodern.asd.

Parent Component

postmodern (module).

Public Interface
Internals

4.1.17 cl-postgres/cl-postgres/package.lisp

Source

cl-postgres.asd.

Parent Component

cl-postgres (module).

Packages
Internals

*optimize* (special variable).


4.1.18 cl-postgres/cl-postgres/features.lisp

Source

cl-postgres.asd.

Parent Component

cl-postgres (module).


4.1.19 cl-postgres/cl-postgres/config.lisp

Source

cl-postgres.asd.

Parent Component

cl-postgres (module).

Public Interface

4.1.20 cl-postgres/cl-postgres/oid.lisp

Dependencies
Source

cl-postgres.asd.

Parent Component

cl-postgres (module).

Public Interface

4.1.21 cl-postgres/cl-postgres/errors.lisp

Dependency

package.lisp (file).

Source

cl-postgres.asd.

Parent Component

cl-postgres (module).

Public Interface
Internals

4.1.22 cl-postgres/cl-postgres/data-types.lisp

Dependencies
Source

cl-postgres.asd.

Parent Component

cl-postgres (module).

Public Interface
Internals

4.1.23 cl-postgres/cl-postgres/sql-string.lisp

Dependencies
Source

cl-postgres.asd.

Parent Component

cl-postgres (module).

Public Interface
Internals

4.1.24 cl-postgres/cl-postgres/trivial-utf-8.lisp

Dependencies
Source

cl-postgres.asd.

Parent Component

cl-postgres (module).

Public Interface
Internals

4.1.25 cl-postgres/cl-postgres/strings-utf-8.lisp

Dependencies
Source

cl-postgres.asd.

Parent Component

cl-postgres (module).

Internals

4.1.26 cl-postgres/cl-postgres/communicate.lisp

Dependencies
Source

cl-postgres.asd.

Parent Component

cl-postgres (module).

Internals

4.1.27 cl-postgres/cl-postgres/messages.lisp

Dependencies
Source

cl-postgres.asd.

Parent Component

cl-postgres (module).

Internals

4.1.28 cl-postgres/cl-postgres/ieee-floats.lisp

Dependency

config.lisp (file).

Source

cl-postgres.asd.

Parent Component

cl-postgres (module).

Public Interface

4.1.29 cl-postgres/cl-postgres/interpret.lisp

Dependencies
Source

cl-postgres.asd.

Parent Component

cl-postgres (module).

Public Interface
Internals

4.1.30 cl-postgres/cl-postgres/saslprep.lisp

Dependencies
Source

cl-postgres.asd.

Parent Component

cl-postgres (module).

Public Interface
Internals

4.1.31 cl-postgres/cl-postgres/scram.lisp

Dependencies
Source

cl-postgres.asd.

Parent Component

cl-postgres (module).

Internals

4.1.32 cl-postgres/cl-postgres/protocol.lisp

Dependencies
Source

cl-postgres.asd.

Parent Component

cl-postgres (module).

Public Interface
Internals

4.1.33 cl-postgres/cl-postgres/public.lisp

Dependencies
Source

cl-postgres.asd.

Parent Component

cl-postgres (module).

Public Interface
Internals

4.1.34 cl-postgres/cl-postgres/bulk-copy.lisp

Dependencies
Source

cl-postgres.asd.

Parent Component

cl-postgres (module).

Public Interface
Internals

4.1.35 s-sql/s-sql/package.lisp

Source

s-sql.asd.

Parent Component

s-sql (module).

Packages

s-sql.


4.1.36 s-sql/s-sql/config.lisp

Dependency

package.lisp (file).

Source

s-sql.asd.

Parent Component

s-sql (module).

Public Interface
Internals

*postgres-reserved-words* (special variable).


4.1.37 s-sql/s-sql/s-sql.lisp

Dependencies
Source

s-sql.asd.

Parent Component

s-sql (module).

Public Interface
Internals

5 Packages

Packages are listed by definition order.


5.1 cl-postgres-error

Source

package.lisp.

Use List
Public Interface
Internals

5.2 cl-postgres-ieee-floats

Source

package.lisp.

Nickname

clp-ieee-floats

Use List

common-lisp.

Public Interface

5.3 cl-postgres.features

Source

package.lisp.

Use List

common-lisp.


5.4 cl-postgres-oid

Source

package.lisp.

Nickname

oid

Use List

common-lisp.

Public Interface

5.5 cl-postgres

Source

package.lisp.

Use List

common-lisp.

Used By List
Public Interface
Internals

5.6 cl-postgres-system

Source

cl-postgres.asd.

Use List
  • asdf/interface.
  • common-lisp.
Internals

5.7 postmodern-system

Source

postmodern.asd.

Use List
  • asdf/interface.
  • common-lisp.

5.8 cl-postgres-trivial-utf-8

Source

package.lisp.

Nickname

clp-utf8

Use List

common-lisp.

Public Interface
Internals

5.9 postmodern

Source

package.lisp.

Nickname

pomo

Use List
Public Interface
Internals

5.10 s-sql

Source

package.lisp.

Use List

common-lisp.

Used By List

postmodern.

Public Interface
Internals

5.11 s-sql-system

Source

s-sql.asd.

Use List
  • asdf/interface.
  • common-lisp.

6 Definitions

Definitions are sorted by export status, category, package, and then by lexicographic order.


6.1 Public Interface


6.1.1 Constants

Constant: +abstime+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +aclitem+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +any+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +any-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +anyelement+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +anyenum+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +anynon-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +anyrange+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +bit+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +bit-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +bool+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +bool-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +box+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +box-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +bpchar+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +bpchar-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +bytea+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +bytea-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +cash+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +char+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +char-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +cid+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +cidr+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +circle+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +cstring+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +cstring-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +date+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +date-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +evttrigger+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +fdw-handler+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +float4+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +float4-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +float8+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +float8-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +gtsvector+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +index-am-handler+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +inet+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +int2+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +int2-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +int2vector+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +int4+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +int4-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +int4range+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +int8+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +int8-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +internal+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +interval+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +interval-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +json+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +jsonb+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +language-handler+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +line+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +lseg+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +lseg-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +lsn+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +macaddr+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +name+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +name-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +numeric+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +numeric-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +oid+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +oid-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +oid-vector+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +opaque+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +path+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +pgddlcommand+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +pgnodetree+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +point+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +point-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +polygon+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +record+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +record-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +refcursor+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +regclass+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +regconfig+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +regdictionary+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +regnamespace+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +regoper+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +regoperator+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +regproc+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +regprocedure+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +regrole+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +regtype+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +regtype-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +reltime+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +text+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +text-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +tid+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +time+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +time-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +timestamp+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +timestamp-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +timestamptz+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +timestamptz-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +timetz+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +tinterval+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +trigger+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +tsm-handler+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +tsquery+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +tsvector+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +unknown+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +uuid+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +v-oid+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +varbit+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +varbit-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +varchar+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +varchar-array+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +xid+
Package

cl-postgres-oid.

Source

oid.lisp.

Constant: +xml+
Package

cl-postgres-oid.

Source

oid.lisp.


6.1.2 Special variables

Special Variable: *allow-overwriting-prepared-statements*

When set to t, ensured-prepared will overwrite prepared statements having the same name if the query statement itself in the postmodern meta connection is different than the query statement provided to ensure-prepared.

Package

postmodern.

Source

config.lisp.

Special Variable: *current-logical-transaction*

This is bound to the current transaction-handle or savepoint-handle instance representing the innermost open logical transaction.

Package

postmodern.

Source

transaction.lisp.

Special Variable: *database*

Special holding the current database. Most functions and macros operating on a database assume this contains a connected database.

Package

postmodern.

Source

connect.lisp.

Special Variable: *default-use-ssl*

The default for connect’s use-ssl argument.
This starts at :no. If you set it to anything else, be sure to also load the CL+SSL library.

Package

postmodern.

Source

config.lisp.

Special Variable: *downcase-symbols*

When converting symbols to strings, whether to downcase the symbols is set here. The default is to downcase symbols.

Package

s-sql.

Source

config.lisp.

Special Variable: *enforce-parameter-types*

When set to t, the parameters of the first invocation of a prepared statement
will set the mandatory types that subsequent invocations of that prepared
statement must meet. If the parameters are used in paramparameters must meet. parameters must match type ensured-prepared will overwrite prepared statements having the same name if the query statement itself in the postmodern meta connection is
different than the query statement provided to ensure-prepared.

Package

postmodern.

Source

config.lisp.

Special Variable: *escape-sql-names-p*

Determines whether double quotes are added around column, table, and ** function names in queries. Valid values:

- T, in which case every name is escaped,
- NIL, in which case no name is escaped,
- :auto, which causes only reserved words to be escaped, or.
- :literal which is the same as :auto except it has added consequence in to-sql-name (see below).

The default value is :auto.

Be careful when binding this with let and such ― since a lot of SQL compilation tends to happen at compile-time, the result might not be what you expect. Mixed case sensitivity is not currently well supported. Postgresql itself will downcase unquoted identifiers. This will be revisited in the future if requested.

Package

s-sql.

Source

config.lisp.

Special Variable: *ignore-unknown-columns*

Normally, when get-dao, select-dao,
save-dao or query-dao finds a column in the database that’s not in the DAO class, it should raise an error. Setting this variable to a non-NIL will cause it to simply ignore the unknown column and allows you to define daos which are subsets of a table.

Package

postmodern.

Source

config.lisp.

Special Variable: *isolation-level*

The transaction isolation
level currently in use. You can specify the following isolation levels in postmodern transactions:

- :read-committed-rw (read committed with read and write)
- :read-committed-ro (read committed with read only)
- :repeatable-read-rw (repeatable read with read and write)
- :repeatable-read-ro (repeatable read with read only)
- :serializable (serializable with reand and write)

Package

postmodern.

Source

config.lisp.

Special Variable: *max-pool-size*

Set the maximum amount of connections kept in a single connection pool, where a pool consists of all the stored connections with the exact same connect arguments. Defaults to NIL, which means there is no maximum.

Package

postmodern.

Source

config.lisp.

Special Variable: *on-evidence-of-man-in-the-middle-attack*

If Postmodern sees evidence of an attempted man-in-the-middle attack, what should Postmodern do? Acceptable values are :error, :warn or :ignore

Package

cl-postgres.

Source

config.lisp.

Special Variable: *query-callback*

When profiling or debugging, the
*query-log* 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.

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

Package

cl-postgres.

Source

config.lisp.

Special Variable: *query-log*

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 (*standard-output*, for example) to have CL-postgres log every query it makes.

Package

cl-postgres.

Source

config.lisp.

Special Variable: *read-row-values-as-binary*

Controls whether row values (as in select row(1, ’foo’) ) should be received from the database in text or binary form. The default value is nil, specifying that the results be sent back as text. Set this to t to cause the results to be read as binary.

Package

cl-postgres.

Source

interpret.lisp.

Special Variable: *retry-connect-delay*

How many seconds to wait before trying to connect again. Borrowed from pgloader

Package

cl-postgres.

Source

config.lisp.

Special Variable: *retry-connect-times*

How many times do we try to connect again. Borrowed from pgloader

Package

cl-postgres.

Source

config.lisp.

Special Variable: *silently-truncate-ratios*

Given a ratio, a stream and a
digital-length-limit, if *silently-truncate-ratios* 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 *silently-truncate-ratios* to true. Code contributed by Attila Lendvai.

Package

cl-postgres.

Source

config.lisp.

Special Variable: *sql-readtable*

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.

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.

Package

cl-postgres.

Source

interpret.lisp.

Special Variable: *ssl-certificate-file*

When set to a filename, this file will be used as client certificate for SSL connections.

Package

cl-postgres.

Source

protocol.lisp.

Special Variable: *ssl-key-file*

When set to a filename, this file will be used as client key for SSL connections.

Package

cl-postgres.

Source

protocol.lisp.

Special Variable: *ssl-root-ca-file*

Should be a path to a root certificate file, typically a pem file. It is used for SSL connections when you want to verify the host using a root ca.

Package

cl-postgres.

Source

protocol.lisp.

Special Variable: *standard-sql-strings*

Indicate whether S-SQL will use standard SQL strings (just use ” for #’), or backslash-style escaping. Setting this to NIL is always safe, but when the server is configured to allow standard strings (parameter ’standard_conforming_strings’ is ’on’), the noise in queries can be reduced by setting this to T.

Package

s-sql.

Source

config.lisp.

Special Variable: *table-name*

Used inside deftable to find the name of the table being defined.

Package

postmodern.

Source

deftable.lisp.

Special Variable: *table-symbol*

Used inside deftable to find the symbol naming the table being defined.

Package

postmodern.

Source

deftable.lisp.

Special Variable: *unix-socket-dir*

Directory where the Unix domain socket for PostgreSQL be found.

Package

cl-postgres.

Source

public.lisp.


6.1.3 Macros

Macro: def-row-reader (name (fields) &body body)

The defun-like variant of row-reader: creates a row reader and gives it a top-level function name.

Package

cl-postgres.

Source

protocol.lisp.

Macro: define-dao-finalization (((dao-name class) &rest keyword-args) &body body)

Create an :around-method for make-dao. The body is executed in a lexical environment where dao-name is bound to a freshly created and inserted DAO. The representation of the DAO in the database is then updated to reflect changes that body might have introduced. Useful for processing values of slots with the type serial, which are unknown before insert-dao.

Package

postmodern.

Source

table.lisp.

Macro: defprepared (name query &optional format)

This is another macro variant of prepare. It is like prepare, but gives the function a name which now becomes a top-level function for the prepared statement. The name should not be a string but may be quoted.

Package

postmodern.

Source

prepare.lisp.

Macro: defprepared-with-names (name (&rest args) (query &rest query-args) &optional format)

Like defprepared, but allows to specify names of the function arguments in a lambda list as well as arguments supplied to the query.

Package

postmodern.

Source

prepare.lisp.

Macro: deftable (name &body definitions)

Define a table. name can be either a symbol or a (symbol string) list. In the first case, the table name is derived from the symbol by S-SQL’s rules, in the second case, the name is given explicitly. The body of definitions can contain anything that evaluates to a string, as well as S-SQL expressions. In this body, the variables *table-name* and *table-symbol* are bound to the relevant values. Note that the evaluation of the definition is ordered, so you will generally want to create your table first and then define indices on it.

Package

postmodern.

Source

deftable.lisp.

Macro: do-query-dao (((type type-var) query) &body body)

Like query-dao, but iterates over the results rather than returning them. For each matching DAO, body is evaluated with type-var bound to the instance.

Example:

(do-query-dao ((’user user)
(:order-by
(:select ’* :from ’user :where (:> ’score 10000)) ’name))
(pushnew user high-scorers))

Package

postmodern.

Source

table.lisp.

Macro: do-select-dao (((type type-var) &optional test &rest ordering) &body body)

Like select-dao, but iterates over the results rather than returning them. For each matching DAO, body is evaluated with type-var bound to the DAO instance.

Example:

(do-select-dao ((’user user) (:> ’score 10000) ’name)
(pushnew user high-scorers))

Package

postmodern.

Source

table.lisp.

Macro: doquery (query (&rest names) &body body)

Execute the given query (a string or a list starting with a keyword), iterating over the rows in the result. The body will be executed with the values in the row bound to the symbols given in names. To iterate over a parameterised query, one can specify a list whose car is the query, and whose cdr contains the arguments. For example:

(doquery (:select ’name ’score :from ’scores) (n s)
(incf (gethash n *scores*) s))

(doquery ((:select ’name :from ’scores :where (:> ’score ’$1)) 100) (name) (print name))

Package

postmodern.

Source

query.lisp.

Macro: ensure-transaction (&body body)

Ensures that body is executed within a transaction, but does not begin a new transaction if one is already in progress.

Package

postmodern.

Source

transaction.lisp.

Macro: ensure-transaction-with-isolation-level (isolation-level &body body)

Executes body within a with-transaction form if and only if no transaction is already in progress. This adds the ability to specify an isolation level other than the current default

Package

postmodern.

Source

transaction.lisp.

Macro: execute (query &rest args)

Execute a query, ignore the results. So, in effect, like a query called with format :none. Returns the amount of affected rows as its first returned value. (Also returns this amount as the second returned value, but use of this is deprecated.)

Package

postmodern.

Source

query.lisp.

Macro: make-float-converters (encoder-name decoder-name exponent-bits significand-bits support-nan-and-infinity-p)

Writes an encoder and decoder function for floating point numbers with the given amount of exponent and significand bits (plus an extra sign bit). If support-nan-and-infinity-p is true, the decoders will also understand these special cases. NaN is represented as :not-a-number, and the infinities as :positive-infinity and :negative-infinity. Note that this means that the in- or output of these functions is not just floating point numbers anymore, but also keywords.

Package

cl-postgres-ieee-floats.

Source

ieee-floats.lisp.

Macro: prepare (query &optional format)

Wraps a query into a function that can be used as the interface to a prepared statement. The given query (either a string or an S-SQL form) may contain placeholders, which look like $1, $2, etc. The resulting function takes one argument for every placeholder in the query, executes the prepared query, and returns the result in the format specified. (Allowed formats are the same as for query.)

For queries that have to be run very often, especially when they are complex, it may help performance since the server only has to plan them once. See the http://www.postgresql.org/docs/current/static/sql-prepare.html
in the PostgreSQL manual for details.

In some cases, the server will complain about not being able to deduce the type of the arguments in a statement. In that case you should add type
declarations (either with the PostgreSQL’s CAST SQL-conforming syntax or historical :: syntax, or with S-SQL’s :type construct) to help it out.

Note that it will attempt to automatically reconnect if database-connection-error, or admin-shutdown. It will reset prepared statements triggering an invalid-sql-statement-name error. It will overwrite old prepared statements triggering a duplicate-prepared-statement error.

Package

postmodern.

Source

prepare.lisp.

Macro: query (query &rest args/format)

Execute the given query, which can be either a string or an S-SQL form (list starting with a keyword). If the query contains placeholders ($1, $2, etc) their values can be given as extra arguments. If one of these arguments is a keyword occurring in the table below, it will not be used as a query
argument, but will determine the format in which the results are returned instead. Any of the following formats can be used, with the default being :rows:

| :none | Ignore the result values. | | :lists, :rows | Return a list of lists, each list containing the values | | | for a row. | | :list, :row | Return a single row as a list. | | :alists | Return a list of alists which map column names to values, | | | with the names represented as keywords. | | :alist | Return a single row as an alist. | | :array-hash | Return an array of hashtables which map column names to | | | hash table keys. NOTE: It will return an empty array | | | if there is no result. | | :vectors | Returns a vector of vectors where each internal vector is | | | a returned row from the query. The field names are not | | | included. NOTE: It will return an empty vector instead of | | | NIL if there is no result. | | :str-alists | Like :alists, but use the original column names. | | :str-alist | Return a single row as an alist, with strings for names. | | :plists | Return a list of plists which map column names to values, | | | with the names represented as keywords. | | :plist | Return a single row as a plist. | | :column | Return a single column as a list. | | :single | Return a single value. | | :single! | Like :single, but raise an error when the number of | | | selected rows is not equal to 1. | | (:dao type) | Return a list of DAOs of the given type. The names of the | | | fields returned by the query must match slots in the DAO | | | class the same way as with query-dao. | | (:dao type :single)| Return a single DAO of the given type. | | :json-strs | Return a list of strings where each row is a json object | | | expressed as a string | | :json-str | Return a single string where the row returned is a json | | | object expressed as a string. | | :json-array-str | Return a string containing a json array, each element in | | | the array is a selected row expressed as a json object. | | | NOTE: If there is no result, it will return a string with | | | an empty json array. |

If the database returns information about the amount rows that were affected, such as with updating or deleting queries, this is returned as a second value.

Package

postmodern.

Source

query.lisp.

Macro: query-dao (type query &rest args)

Execute the given query (which can be either a string or an S-SQL expression) and return the result as DAOs of the given type. If the query contains placeholders ($1, $2, etc) their values can be given as extra arguments. The names of the fields returned by the query must either match slots in the DAO class, or be bound through with-column-writers.

Package

postmodern.

Source

table.lisp.

Macro: register-sql-operators (arity &rest names)

Define simple operators. Arity is one of :unary (like
’not’), :unary-postfix (the operator comes after the operand), :n-ary (like ’+’: the operator falls away when there is only one operand), :2+-ary (like ’=’, which is meaningless for one operand), or :n-or-unary (like ’-’, where the operator is kept in the unary case). After the arity follow any number of operators, either just a keyword, in which case the downcased symbol name is used as the operator, or a two-element list containing a keyword and a name string.

Package

s-sql.

Source

s-sql.lisp.

Macro: row-reader ((fields) &body body)

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.

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.

The definition of list-row-reader should give you an idea what a row reader looks like:

(row-reader (fields)
(loop :while (next-row)
:collect (loop :for field :across fields
:collect (next-field field))))

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. A row reader
is a function that is used to do something with the results of a query. It has two local functions: next-row and next-field, the first should be called once per row and will return a boolean indicating whether there are
any more rows, the second should be called once for every element in
the fields vector, with that field as argument, to read a single value
in a row. See list-row-reader in public.lisp for an example.

Package

cl-postgres.

Source

protocol.lisp.

Macro: select-dao (type &optional test &rest ordering)

Select DAO objects for the rows in the associated table for which the given test (either an S-SQL expression or a string) holds. When sorting arguments are given, which can also be S-SQL forms or strings, these are used to sort the result.

(Note that, if you want to sort, you have to pass the test argument.)

(select-dao ’user (:> ’score 10000) ’name)

Package

postmodern.

Source

table.lisp.

Macro: sql (form)

Convert the given form (a list starting with a keyword) to an SQL query string at compile time, according to the rules described here. For example:

(sql (:select ’* :from ’country :where (:= ’a 1)))
"(SELECT * FROM country WHERE (a = 1))"

but

(sql ’(:select ’* :from ’country :where (:= ’a 1)))

would throw an error. For the later case you need to use sql-compile.

Package

s-sql.

Source

s-sql.lisp.

Macro: with-binary-row-values (&body body)

Helper macro to locally set *read-row-values-as-binary* to t while executing body so that row values will be returned as binary.

Package

cl-postgres.

Source

interpret.lisp.

Macro: with-column-writers ((&rest defs) &body body)

Provides control over the way get-dao, select-dao, and query-dao read values from the database. This is not commonly needed, but can be used to reduce the amount of queries a system makes. writers should be a list of alternating column names (strings or symbols) and writers, where writers are either symbols referring to a slot in the objects, or functions taking two arguments ― an instance and a value ― which can be used to somehow store the value in the new instance. When any DAO-fetching function is called in the body, and columns matching the given names are encountered in the result, the writers are used instead of the default behaviour (try and store the value in the slot that matches the column name).

An example of using this is to add some non-column slots to a DAO class, and use query-dao within a with-column-writers form to pull in extra information about the objects, and immediately store it in the new instances.

Package

postmodern.

Source

table.lisp.

Macro: with-connection (spec &body body)

Evaluates the body with *database* bound to a connection as specified by spec, which should be list that connect can be applied to.

Package

postmodern.

Source

connect.lisp.

Macro: with-logical-transaction ((&optional name isolation-level) &body body)

Executes body within a with-transaction form if no transaction is currently in progress, otherwise simulates a nested transaction by executing it within a with-savepoint form. The transaction or savepoint is bound to name if one is supplied. The isolation-level will set the isolation-level used by the transaction.

You can specify the following isolation levels in postmodern transactions:

- :read-committed-rw (read committed with read and write)
- :read-committed-ro (read committed with read only)
- :repeatable-read-rw (repeatable read with read and write)
- :repeatable-read-ro (repeatable read with read only)
- :serializable (serializable with reand and write)

Sample usage where george is just the name given to the transaction (not quoted or a string) and ... simply indicates other statements would be expected here:

(with-logical-transaction ()
(execute (:insert-into ’test-data :set ’value 77))
...)

(with-logical-transaction (george)
(execute (:insert-into ’test-data :set ’value 22))
...)

(with-logical-transaction (george :read-committed-rw)
(execute (:insert-into ’test-data :set ’value 33))
...)

(with-logical-transaction (:serializable)
(execute (:insert-into ’test-data :set ’value 44))
...)

Package

postmodern.

Source

transaction.lisp.

Macro: with-savepoint (name &body body)

Can only be used within a transaction. Establishes a savepoint with the given name at the start of body, and binds the same name to a handle for that savepoint. The body is executed and, at the end of body, the savepoint is released, unless a condition is thrown, in which case it is rolled back. Execute the body within a savepoint, releasing savepoint when the body exits normally, and rolling back otherwise. NAME is both the variable that can be used to release or rolled back before the body unwinds, and the SQL name of the savepoint.

An example might look like this:

(defun test12 (x &optional (y nil))
(with-logical-transaction (lt1 :read-committed-rw)
(execute (:insert-into ’test-data :set ’value 0))
(with-savepoint sp1
(execute (:insert-into ’test-data :set ’value 1))
(if (< x 0)
(rollback-savepoint sp1)
(release-savepoint sp1)))
(with-savepoint sp2
(execute (:insert-into ’test-data :set ’value 2))
(with-savepoint sp3
(execute (:insert-into ’test-data :set ’value 3))
(if (> x 0)
(rollback-savepoint sp3)
(release-savepoint sp3))
(when y (rollback-savepoint sp2)))
(if (= x 0)
(rollback-savepoint sp2)
(release-savepoint sp2)))
(when (string= y "abrt")
(abort-transaction lt1))))

Package

postmodern.

Source

transaction.lisp.

Macro: with-schema ((schema &key strict if-not-exist drop-after) &body form)

A macro to set the schema search path (namespace) of the postgresql database to include as first entry a specified schema and then executes the body. Before executing body the PostgreSQL’s session variable search_path is set to the given namespace. After executing body the search_path variable is restored to the original value.

Calling with :strict ’t only the specified schema is set as current search path. All other schema are then not searched any more. If strict is nil, the namespace is just first schema on the search path upon the the body execution.

Calling with :if-not-exist set to :create the schema is created if this schema did not exist.
Calling with :if-not-exist set to nil, an error is signaled.

calling with drop-after set to ’t the schema is removed after the execution of the body form.

example :

(with-schema (:schema-name :strict nil :drop-after nil :if-not-exist :error) (foo 1)
(foo 2))

example :

(with-schema (’uniq :if-not-exist :create) ;; changing the search path (schema-exists-p ’uniq))

Package

postmodern.

Source

namespace.lisp.

Macro: with-text-row-values (&body body)

Helper macro to locally set *read-row-values-as-binary* to nil while executing body so that row values will be returned as t.

Package

cl-postgres.

Source

interpret.lisp.

Macro: with-transaction ((&optional name isolation-level) &body body)

Execute the given body within a database transaction, committing it when the body exits normally, and aborting otherwise. An optional name and/or isolation-level can be given to the transaction. The name can be used to force a commit or abort before the body unwinds. The isolation-level will set the isolation-level used by the transaction.

You can specify the following isolation levels in postmodern transactions:

- :read-committed-rw (read committed with read and write)
- :read-committed-ro (read committed with read only)
- :repeatable-read-rw (repeatable read with read and write)
- :repeatable-read-ro (repeatable read with read only)
- :serializable (serializable with reand and write)

Sample usage where george is just the name given to the transaction (not quoted or a string) and ... simply indicates other statements would be expected here:

(with-transaction ()
(execute (:insert-into ’test-data :set ’value 77))
...)

(with-transaction (george)
(execute (:insert-into ’test-data :set ’value 22))
...)

(with-transaction (george :read-committed-rw)
(execute (:insert-into ’test-data :set ’value 33))
(query (:select ’* :from ’test-data))
...)

(with-transaction (:serializable)
(execute (:insert-into ’test-data :set ’value 44))
...)

Further discussion of transactions and isolation levels can found in the isolation notes file in the doc folder.

Package

postmodern.

Source

transaction.lisp.


6.1.4 Ordinary functions

Function: !dao-def ()

Should only be used inside a deftable form. Define this table using the corresponding DAO class’ slots. Adds the result of calling dao-table-definition on *table-symbol* to the definition.

Package

postmodern.

Source

deftable.lisp.

Function: !foreign (target fields &rest target-fields/on-delete/on-update/deferrable/initially-deferred)

Used inside a deftable form. Define a foreign key on this table. Pass a table the index refers to, a list of fields or single field in *this* table, and, if the fields have different names in the table referred to, another field or list of fields for the target table, or :primary-key to indicate that the other table’s primary key should be referenced.

Package

postmodern.

Source

deftable.lisp.

Function: !index (&rest fields)

Used inside a deftable form. Define an index on the table being defined. The columns can be given as symbols or strings.

Package

postmodern.

Source

deftable.lisp.

Function: !unique (target-fields &key deferrable initially-deferred)

Constrains one or more columns to only contain unique (combinations of) values, with deferrable and initially-deferred defined as in !foreign

Package

postmodern.

Source

deftable.lisp.

Function: !unique-index (&rest fields)

Used inside a deftable form. Define a unique index on the defined table.

Package

postmodern.

Source

deftable.lisp.

Function: abort-transaction (transaction)

Roll back the given transaction to the beginning, but the transaction
block is still active. Thus calling abort-transaction in the middle of a transaction does not end the transaction. Any subsequent statements will still be executed. Per the Postgresql documentation: ABORT rolls back the current transaction and causes all the updates made by the transaction to be discarded. This command is identical in behavior to the standard SQL command ROLLBACK, and is present only for historical reasons.

Package

postmodern.

Source

transaction.lisp.

Function: add-comment (type name comment &optional second-name)

Attempts to add a comment to a particular database object. The first parameter is a keyword for the type of database object. The second parameter is the name of the object. The third parameter is the comment itself. Some objects require an additional identifier. The names can be strings or symbols.

Example usage would be:
(add-comment :column ’country-locations.name "Is what it looks like - the name of a country".)

(add-comment :column "country_locations.name" "Is what it looks like - the name of a country".)

Example usage where two identifiers are required would be constraints:

(add-comment :constraint ’constraint1 "Some kind of constraint descriptions here". ’country-locations)

Package

postmodern.

Source

util.lisp.

Function: alist-row-reader (g0 fields)
Package

cl-postgres.

Source

public.lisp.

Function: alter-role-search-path (role search-path)

Changes the priority of where a role looks for tables (which schema first, second, etc. Role should be a string or symbol. Search-path could be a list of schema names either as strings or symbols.

Package

postmodern.

Source

roles.lisp.

Function: call-with-connection (spec thunk)

The functional backend to with-connection. Binds *database* to a new connection as specified by spec, which should be a list that connect can be applied to, and runs the zero-argument function given as second argument in the new environment. When the function returns or throws, the new connection is disconnected.

Package

postmodern.

Source

connect.lisp.

Function: cancel-backend (pid &optional database)

Polite way of terminating a query at the database (as opposed to calling close-database). Slower than (terminate-backend pid) and does not always work.

Package

postmodern.

Source

prepare.lisp.

Function: change-password (role password &optional expiration-date)

Alters a role’s password. If the optional expiration-date parameter is provided, the password will expire at the stated date. A sample expiration date would be ’December 31, 2020’. If the expiration date is ’infinity’, it will never expire. The password will be encrypted in the system catalogs. This is
automatic with postgresql versions 10 and above.

Package

postmodern.

Source

roles.lisp.

Function: change-toplevel-database (new-database user password host)

Just changes the database assuming you are using a toplevel connection. Recommended only for development work. Returns the name of the newly connected database as a string.

Package

postmodern.

Source

util.lisp.

Function: clear-connection-pool ()

Disconnect and remove all connections in the connection pools.

Package

postmodern.

Source

connect.lisp.

Function: close-database (connection)

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.

Package

cl-postgres.

Source

public.lisp.

Function: close-db-writer (self &key abort)

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.

Package

cl-postgres.

Source

bulk-copy.lisp.

Function: coalesce (&rest args)

Returns the first non-NIL, non-NULL (as in :null) argument, or NIL if none are present. Useful for providing a fall-back value for the result of a query, or, when given only one argument, for transforming :nulls to NIL.

Package

postmodern.

Source

util.lisp.

Function: col-type-text-p (column-slot)

Returns t if a column-slot has text as a type. Could be text or (or text db-null)

Package

postmodern.

Source

table.lisp.

Function: column-exists-p (table-name column-name &optional schema-name)

Determine if a particular column exists. Table name and column-name can be either strings or symbols. If the optional schema name is not given or the table-name is not fully qualified with a schema name, the schema will be assumed to be the public schema. Returns t or nil.

Package

postmodern.

Source

util.lisp.

Function: commit-transaction (transaction)

Immediately commit an open transaction.

Package

postmodern.

Source

transaction.lisp.

Function: connect (database-name user-name password host &key port pooled-p use-ssl use-binary service application-name)

Create a new database connection for the given user and the database. Port will default to 5432, which is where most PostgreSQL servers are running. If pooled-p is T, a connection will be taken from a pool of connections of this type, if one is available there, and when the connection is disconnected it will be put back into this pool instead. use-ssl can be :no, :yes, or :try, as in open-database, and defaults to the value of *default-use-ssl*.

Package

postmodern.

Source

connect.lisp.

Function: connect-toplevel (database-name user-name password host &key port use-ssl application-name use-binary)

Bind the *database* to a new connection. Use this if you only need one connection, or if you want a connection for debugging from the REPL.

Package

postmodern.

Source

connect.lisp.

Function: connected-p (database)

Returns a boolean indicating whether the given connection is still connected to the server.

Package

postmodern.

Source

connect.lisp.

Function: connection-meta (connection)

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.

Package

cl-postgres.

Source

public.lisp.

Function: copy-sql-readtable (&optional table)

Copies a given readtable.

Package

cl-postgres.

Source

interpret.lisp.

Function: create-all-tables ()

Create all defined tables.

Package

postmodern.

Source

deftable.lisp.

Function: create-database (database-name &key encoding connection-limit owner limit-public-access comment collation template)

Creates a basic database. Besides the obvious database-name parameter, you can also use key parameters to set encoding (defaults to UTF8), owner, connection-limit (defaults to no limit)). If limit-public-access is set to t, then only superuser roles or roles with explicit access to this database will be able to access it. If collation is set, the assumption is that template0 needs to be used rather than template1 which may contain encoding specific or locale specific data.

Package

postmodern.

Source

util.lisp.

Function: create-index (name &key unique if-not-exists concurrently on using fields)

Create an index. Slightly less sophisticated than the query version because it does not have a where clause capability.

Package

postmodern.

Source

util.lisp.

Function: create-package-tables (package)

Create all tables whose identifying symbol is interned in the given package.

Package

postmodern.

Source

deftable.lisp.

Function: create-role (name password &key base-role schema tables databases allow-whitespace allow-utf8 allow-disallowed-names comment)

Keyword parameters: Base-role. Base-role should be one of :readonly, :editor,
:admin, :standard or :superuser. A readonly user can only select existing data in the
specified tables or databases. An editor has the ability to insert, update,
delete or select data. An admin has all privileges on a database, but cannot
create new databases, roles, or replicate the system. A standard user has no
particular privileges other than connecting to databases.

:schema defaults to :public but can be a list of schemas. User will not have
access to any schemas not in the list.

:tables defaults to :all but can be a list of tables. User will not have access
to any tables not in the list.

:databases defaults to :current but can be a list of databases. User will not
have access to any databases not in the list.

:allow-whitespace - Whitespace in either the name or password is not allowed by
default.

:allow-utf8 defaults to nil. If t, the name and password will be normalized. If
nil, the name and password are limited to printable ascii characters. For fun
reading on utf8 user names see
https://labs.spotify.com/2013/06/18/creative-usernames. Also interesting reading
is https://github.com/flurdy/bad_usernames and https://github.com/dsignr/disallowed-usernames/blob/master/disallowed%20usernames.csv, and https://www.b-list.org/weblog/2018/feb/11/usernames/

:allow-disallowed-names defaults to nil. If nil, the user name will be checked
against *disallowed-role-names*.

As an aside, if allowing utf8 in names, you might want to think about whether
you should second copy of the username in the original casing and normalized as
NFC for display purposes as opposed to normalizing to NFKC. It might be viewed
as culturally insensitive to change the display of the name.

Package

postmodern.

Source

roles.lisp.

Function: create-schema (schema &optional authorization)

Create a new schema. Raises an error if the schema already exists. If the optional authorization parameter is provided, the schema will be owned by that role.

Package

postmodern.

Source

namespace.lisp.

Function: create-sequence (name &key temp if-not-exists increment min-value max-value start cache)

Create a sequence. Available additional key parameters are :temp :if-not-exists :increment :min-value :max-value :start and :cache. See https://www.postgresql.org/docs/current/static/sql-createsequence.html for details on usage.

Package

postmodern.

Source

util.lisp.

Function: create-table (name)

Takes the name of a dao-class and creates the table identified by symbol by executing all forms in its definition as found in the *tables* list.

Package

postmodern.

Source

deftable.lisp.

Function: current-database ()

Returns the string name of the current database.

Package

postmodern.

Source

util.lisp.

Function: dao-table-definition (table)

Given a DAO class, or the name of one, this will produce an SQL query string with a definition of the table. This is just the bare simple definition, so if you need any extra indices or or constraints, you’ll have to write your own queries to add them, in which case look to s-sql’s create-table function.

Package

postmodern.

Source

table.lisp.

Function: dao-table-name (class)

Get the name of the table associated with the given DAO class (or symbol naming such a class).

Package

postmodern.

Source

table.lisp.

Function: database-error-constraint-name (err)

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).

Package

cl-postgres.

Source

errors.lisp.

Function: database-error-extract-name (err)

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

Package

cl-postgres.

Source

errors.lisp.

Function: database-exists-p (database)

Returns database name string if the database parameter is actually an available database

Package

postmodern.

Source

util.lisp.

Function: database-open-p (connection)

Returns a boolean indicating whether the given connection is currently connected.

Package

cl-postgres.

Source

public.lisp.

Function: database-size (&optional name)

Given the name of a database, will return the name, a pretty-print string of the size of the database and the size in bytes. If a database name is not provided, it will return the result for the currently connected database.

Package

postmodern.

Source

util.lisp.

Function: database-version ()

DEPRECATED BECAUSE IT IS CONFUSING. IT IS REALLY THE POSTGRESQL SERVER VERSION NOT A DATABASE VERSION. USE POSTGRESQL-VERSION INSTEAD.

Returns the version string provided by postgresql of the
current postgresql server E.g. ’PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (Arch Linux 9.3.0-1) 9.3.0, 64-bit’. If you want just the postgresql version number, use cl-postgres:get-postgresql-version.

Package

postmodern.

Source

util.lisp.

Function: db-write-row (self row &optional data)

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.

Package

cl-postgres.

Source

bulk-copy.lisp.

Function: decode-float32 (bits)
Package

cl-postgres-ieee-floats.

Source

ieee-floats.lisp.

Function: decode-float64 (bits)
Package

cl-postgres-ieee-floats.

Source

ieee-floats.lisp.

Function: default-sql-readtable ()

Returns the default readtable, containing only the readers defined by CL-postgres itself.

Package

cl-postgres.

Source

interpret.lisp.

Function: describe-constraint (table-name constraint-name)

Return a list of alists of the descriptions a particular constraint given the table-name and the constraint name using the information_schema table.

Package

postmodern.

Source

util.lisp.

Function: describe-foreign-key-constraints ()

Generates a list of lists of information on the foreign key constraints
where each row returned is in the form of
(constraint-name 631066 table-name table-column 631061 foreign-table-name foreign-table-column)

Package

postmodern.

Source

util.lisp.

Function: describe-triggers ()

List detailed information on the triggers from the information_schema table.

Package

postmodern.

Source

util.lisp.

Function: describe-views (&optional schema)

Describe the current views in the specified schema. Includes the select statements used to create the view. Takes an optional schema
name but defaults to public schema.

Package

postmodern.

Source

util.lisp.

Function: disconnect-toplevel ()

Disconnect *database*.

Package

postmodern.

Source

connect.lisp.

Function: drop-database (database)

Drop the specified database. The database parameter can be a string or a symbol. Note: Only the owner of a database can drop a database and there cannot be any current connections to the database.

Package

postmodern.

Source

util.lisp.

Function: drop-index (name &key concurrently if-exists cascade)

Drop an index. Available keys are :concurrently, :if-exists, and :cascade.

Package

postmodern.

Source

util.lisp.

Function: drop-prepared-statement (name &key location database remove-function)

The statement name can be a string or quoted symbol.

Prepared statements are stored both in the meta slot in the postmodern connection and in postgresql session information. In the case of prepared statements generated with defprepared, there is also a lisp function with the same name.

If you know the prepared statement name, you can delete the prepared statement from both locations (the default behavior), just from postmodern by passing :postmodern to the location key parameter or just from postgresql by passing :postgresql to the location key parameter.

If you pass the name ’All’ as the statement name, it will
delete all prepared statements.

The default behavior is to also remove any lisp function of the same name. This behavior is controlled by the remove-function key parameter.

Package

postmodern.

Source

prepare.lisp.

Function: drop-role (role-name &optional new-owner database)

The role-name and optional new-owner name should be strings. If they are symbols, they will be converted to string and hyphens will be converted to underscores.

Before dropping the role, you must drop all the objects it owns (or reassign their ownership) and revoke any privileges the role has been granted on other objects. If database is :all, drop-role will loop through all databases in the cluster ensuring that the role has no privileges or owned objects in every database. Otherwise drop-role will drop objects owned by a role in the current database.

We will reassign ownership of the objects to the postgres role
unless otherwise specified in the optional second parameter. If neither the postgresql role nor a provided second parameter actually exist as a role on the server, object ownership will be assigned to the role calling (drop-role).

Returns t if successful. Will not drop the postgres role.

As a minor matter of note, Postgresql allows a role to own objects in databases even if it does not have connection rights. This can be useful in setting group roles.

Package

postmodern.

Source

roles.lisp.

Function: drop-schema (schema &key if-exists cascade)

Drops an existing database schema ’schema’ Accepts :if-exists and/or :cascade arguments like :drop-table. A notice instead of an error is raised with the is-exists parameter.

Package

postmodern.

Source

namespace.lisp.

Function: drop-sequence (name &key if-exists cascade)

Drop a sequence. Name should be quoted. Available key parameters are :if-exists and :cascade

Package

postmodern.

Source

util.lisp.

Function: drop-table (table-name &key if-exists cascade)

Drop a table. Available additional key parameters are :if-exists and :cascade.

Package

postmodern.

Source

util.lisp.

Function: enable-s-sql-syntax (&optional char)

Enable a syntactic shortcut #Q(...) for (sql (...)). Optionally takes a character to use instead of #\Q.

Package

s-sql.

Source

s-sql.lisp.

Function: encode-float32 (float)
Package

cl-postgres-ieee-floats.

Source

ieee-floats.lisp.

Function: encode-float64 (float)
Package

cl-postgres-ieee-floats.

Source

ieee-floats.lisp.

Function: encode-json-to-string (object)

Return the JSON representation of OBJECT as a string.

Package

postmodern.

Source

json-encoder.lisp.

Function: exec-prepared (connection name parameters &optional row-reader)

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.
row-reader to the result.

Package

cl-postgres.

Source

public.lisp.

Function: exec-query (connection query &optional row-reader)

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.

Package

cl-postgres.

Source

public.lisp.

Function: execute-file (pathname &optional print remove-comments)

This function will execute sql queries stored in a file. Each sql statement in the file will be run independently, but if one statement fails, subsequent query statements will not be run, but any statement prior to the failing statement will have been commited.

Execute-file allows the sql file to include other sql files, with the meta-commands i or include which look for a file location relative to your default pathname (current working directory) or ir or include_relative which look for a file location relative to the initial sql file. If the file is not found in the expected location, execute-file will look to see if the requested file is in the other possible location. If that does not work, it will trigger an error with a restart which allows you to provide a new name for the file.

If you want the standard transction treatment such that all statements succeed or no statement succeeds, then ensure that the file starts with a begin transaction statement and finishes with an end transaction statement. See the test file test-execute-file-broken-transaction.sql as an example.

For debugging purposes, if the optional print parameter is set to t, format will print the count of the query and the query to the REPL.

The default setting is to remove sql comments from the file before executing the sql code. If that causes problems, the remove-comments parameter can be set to nil.

IMPORTANT NOTE: This utility function assumes that the file containing the sql queries can be trusted and bypasses the normal postmodern parameterization of queries.

Package

postmodern.

Source

execute-file.lisp.

Function: find-col-type (class column-name)

Returns the col-type for a class and a column-name. The column name must be a symbol

Package

postmodern.

Source

table.lisp.

Function: find-comments (type identifier)

Returns the comments attached to a particular database object. The allowed types are :database :schema :table :columns (all the columns in a table) :column (for a single column).

An example would be (find-comments :table ’s2.employees) where the table employees is in the s2 schema.

Package

postmodern.

Source

util.lisp.

Function: find-dao-column-slot (class column-name)

Given a class and a symbol returns the dao-column-slot class for the column named by that symbol (not the sql_column_name). Column name can be a symbol or a string.

Package

postmodern.

Source

table.lisp.

Function: find-postgresql-prepared-statement (name)

Returns the specified named prepared statement (if any) that postgresql has for this session.

Package

postmodern.

Source

prepare.lisp.

Function: find-postmodern-prepared-statement (name)

Returns the specified named prepared statement (if any) that postmodern has put in the meta slot in the connection. Note that this is the statement itself, not the name.

Package

postmodern.

Source

prepare.lisp.

Function: find-primary-key-info (table &optional just-key)

Returns a list of sublists where the sublist contains two strings. If a table primary key consists of only one column, such as ’id’ there will be a single sublist where the first string is the name of the column and the second string is the string name for the datatype for that column. If the primary key for the table consists of more than one column, there will be a sublist for each column subpart of the key. The sublists will be in the order they are used in the key, not in the order they appear in the table. If just-key is set to t, the list being returned will contain just the column names in the primary key as string names with no sublists. If the table is not in the public schema, provide the fully qualified table name e.g. schema-name.table-name.

Package

postmodern.

Source

util.lisp.

Function: from-sql-name (str)

Convert a string to a symbol, upcasing and replacing underscores with hyphens.

Package

s-sql.

Source

s-sql.lisp.

Function: get-all-table-comments ()

Returns a list of lists, each list showing the schema, table and comment of all tables with comments.

Package

postmodern.

Source

util.lisp.

Function: get-column-comment (qualified-column-name)

Retrieves a string which is the comment applied to a particular column in a table in the currently connected database. The parameter can be in the form
of table.column, schema.table.column or database.schema.table.colum.

Package

postmodern.

Source

util.lisp.

Function: get-column-comments (fully-qualified-table-name)

Retrieves a list of lists of column names and comments, if any, from a table. Each sublist will be in the form of (column-name comment-string)

Package

postmodern.

Source

util.lisp.

Function: get-database-comment (&optional database-name)

Returns the comment, if any, attached to a database

Package

postmodern.

Source

util.lisp.

Function: get-pid ()

Get the process id used by postgresql for this connection.

Package

postmodern.

Source

prepare.lisp.

Function: get-pid-from-postmodern ()

Get the process id used by postgresql for this connection, but get it from the postmodern connection parameters.

Package

postmodern.

Source

prepare.lisp.

Function: get-postgresql-version (connection)

Retrieves the version number of the connected postgresql database as a
string. Some installations of Postgresql add additional information after the base version number, so hopefully this gets rid of the unwanted info.

Package

cl-postgres.

Source

public.lisp.

Function: get-schema-comment (schema-name)

If the schema has been commented, returns that string, else nil. Must be a schema in the currently connected database.

Package

postmodern.

Source

util.lisp.

Function: get-search-path ()

Returns the default schema search path for the current session.

Package

postmodern.

Source

namespace.lisp.

Function: get-table-comment (table-name &optional schema-name)

Retrieves the comment, if any attached to the table.

Package

postmodern.

Source

util.lisp.

Function: get-table-oid (table-name &optional schema-name)

Retrieves the oid identifier for a particular table from postgresql. Works for tables in all schemas.

Package

postmodern.

Source

util.lisp.

Function: grant-admin-permissions (schema-name role-name &optional table-name)

Grants all privileges to a role for the named schema. If the optional table-name parameter is provided, the privileges are only granted with respect to that table (or tables if table-name is a list of table names).

Package

postmodern.

Source

roles.lisp.

Function: grant-editor-permissions (schema-name role-name &optional table-name)

Grants select, insert, update and delete privileges to a role for the named schema. If the optional table-name parameter is provided, the privileges are only granted with respect to that table (or tables if table-name is a list of table names).

Note that we are giving some function execute
permissions if table-name is nil, but if the table-name is specified, those are not provided. Your mileage may vary on how many privileges you want to provide
to a editor role with access to only a limited number of tables.

Package

postmodern.

Source

roles.lisp.

Function: grant-readonly-permissions (schema-name role-name &optional table-name)

Grants select privileges to a role for the named schema. If the optional
table-name parameter is provided, the privileges are only granted with respect
to that table (or tables if table-name is a list of table names). Note that we are giving some function execute permissions if table-name is nil, but if the table-name is specified, those are not provided. Your mileage may vary on how many privileges you want to provide to a read-only role with access to only a limited number of tables.

Package

postmodern.

Source

roles.lisp.

Function: grant-role-permissions (role-type name &key schema tables databases)

Grant-role-permissions assumes that a role has already been created, but permissions need to be granted or revoked on a particular database.

A :superuser can create databases, roles, replication, etc. Returns nil. A :standard user has no particular privileges or restrictions. Returns nil. An :admin user can edit existing data, insert new data and create new tables in the specified databases/schemas/tables.
An :editor user can update fields or insert new records but cannot create new tables in the specified tables or databases.
A :readonly role can only read existing data in the specified schemas, tables or databases. Schema, tables or databases can be :all or a list of schemas, tables or databases to be granted permission.

Granting :all provides access to all future items of that type as well.

Note that the schema and table rights and revocations granted are limited to the connected database at the time of execution of this function.

Package

postmodern.

Source

roles.lisp.

Function: ignore-row-reader (g0 fields)
Package

cl-postgres.

Source

public.lisp.

Function: index-exists-p (index-name)

Tests whether an index with the given name exists. The name can be either a string or a symbol.

Package

postmodern.

Source

util.lisp.

Function: list-all-constraints (table-name &optional strings-p)

Uses information_schema to list all the constraints in a table. Table-name can be either a string or quoted. Turns constraints into keywords if strings-p is not true.

Package

postmodern.

Source

util.lisp.

Function: list-all-tables (&optional fully-qualified-names-only)

If fully-qualified-names-only is set to t, returns all schema.table names other than pg_catalog or the information_schema. Otherwise returns the following info:

schema-name, table-name, table-owner, tablespace, hasindexes, hasrules, hastriggers and rowsecurity

Package

postmodern.

Source

util.lisp.

Function: list-available-collations ()

Get a list of the collations available from the current database cluster. Collations are a mess as different operating systems provide different collations. We might get some sanity if Postgresql can use ICU as the default. See https://wiki.postgresql.org/wiki/Collations.

Package

postmodern.

Source

util.lisp.

Function: list-available-extensions ()

List the postgresql extensions which are available in the system to the currently connected database. The extensions may or may not be installed.

Package

postmodern.

Source

util.lisp.

Function: list-available-types ()

List the available data types in the connected postgresql version, It returns a list of lists, each sublist containing the oid (object identifier number) and the name of the data types. E.g. (21 "smallint")

Package

postmodern.

Source

util.lisp.

Function: list-check-constraints (table-name)

Takes a fully qualified table name and returns a list of lists of check constraints where each sublist has the form of (check-constraint-name check). See postmodern doc for example

Package

postmodern.

Source

util.lisp.

Function: list-columns (table-name)

Returns a list of strings of just the column names in a table.
Pulls info from the postmodern table-description function rather than directly. The table-name can be a string or quoted. Any table-name that is not fully qualified with the schema will be assumed to be in the public schema.

Package

postmodern.

Source

util.lisp.

Function: list-columns-with-types (table-name)

Returns a list of (name type) lists for the fields of a table. Returns a list of strings of just the column names and their sql data types in a table. Pulls info from the postmodern table-description function rather than directly. The table-name can be a string or quoted. Any table-name that is not fully qualified with the schema will be assumed to be in the public schema.

Package

postmodern.

Source

util.lisp.

Function: list-connections ()

List the current postgresql connections to the currently connected database. It does this by returningo info from pg_stat_activity on open connections.

Package

postmodern.

Source

util.lisp.

Function: list-database-access-rights (&optional database-name)

If the database parameter is specifed, this returns an list of lists where each sublist is a role name and whether they have access rights (T or NIL) to that particular database. If the database-name is not provided, the sublist is
a database name, a role name and whether they have access rights (T or NIL).

Package

postmodern.

Source

util.lisp.

Function: list-database-functions ()

Returns a list of the functions in the database from the information_schema.

Package

postmodern.

Source

util.lisp.

Function: list-database-users ()

List database users (Roles who can login).

Package

postmodern.

Source

roles.lisp.

Function: list-databases (&key order-by-size size names-only)

Returns a list of lists where each sub-list contains the name of the database, a pretty-print string of the size of that database and the size in bytes. The default order is by database name. Pass t as a parameter
to :order-by-size for order by size. Setting size to nil will return just the database names in a single list ordered by name. This function excludes the template databases.

Package

postmodern.

Source

util.lisp.

Function: list-detailed-triggers ()

DEPRECATED FOR DESCRIBE-TRIGGERS.List detailed information on the triggers from the information_schema table.

Package

postmodern.

Source

util.lisp.

Function: list-foreign-keys (table &optional schema)

Returns a list of sublists of foreign key info in the form of ’((constraint-name local-table local-table-column foreign-table-name foreign-column-name))

Package

postmodern.

Source

util.lisp.

Function: list-index-definitions (table-name)

Returns a list of the definitions used to create the current indexes for the table.

Package

postmodern.

Source

util.lisp.

Function: list-indexed-column-and-attributes (table-name)

List the indexed columns and their attributes in a table. Includes primary key.

Package

postmodern.

Source

util.lisp.

Function: list-indices (&optional strings-p)

Return a list of the indexs in a database. Turn them into keywords if strings-p is not true.

Package

postmodern.

Source

util.lisp.

Function: list-installed-extensions ()

List the postgresql extensions which are installed in the currently connected database.

Package

postmodern.

Source

util.lisp.

Function: list-postmodern-prepared-statements (&optional names-only)

List the prepared statements that postmodern has put in the meta slot in the connection.

If the names-only parameter is set to t, it will only return a list of the names of the prepared statements.

Package

postmodern.

Source

prepare.lisp.

Function: list-prepared-statements (&optional names-only)

This is syntactic sugar. A query that lists the prepared statements in the session in which the function is run. It will return a list of alists of form: ((:NAME . "SNY24")
(:STATEMENT . "(SELECT name, salary FROM employee WHERE (city = $1))") (:PREPARE-TIME . #<TIMESTAMP 25-11-2018T15:36:43,385>)
(:PARAMETER-TYPES . "{text}") (:FROM-SQL).

If the optional names-only parameter is
set to t, it will only return a list of the names of the prepared statements.

Package

postmodern.

Source

prepare.lisp.

Function: list-role-accessible-databases (role-name)

Returns a list of the databases to which the specified role can connect.

Package

postmodern.

Source

util.lisp.

Function: list-role-permissions (&optional role)

This returns a list of sublists of the permissions granted within the currently connected database. If an optional role is provided, the result is limited to that role. The sublist returned will be in the form of role-name, schema-name, table-name and then a string containing all the rights of that role on that table in that schema.

Package

postmodern.

Source

roles.lisp.

Function: list-roles (&optional lt)

Returns a list of alists of rolenames, role attributes and membership in roles. See https://www.postgresql.org/docs/current/role-membership.html for an explanation. The optional parameter can be used to set the return list types to :alists or :plists.

Package

postmodern.

Source

roles.lisp.

Function: list-row-reader (g0 fields)
Package

cl-postgres.

Source

public.lisp.

Function: list-schemas ()

List schemas in the current database, excluding the pg_* system schemas. Should have the same result as list-schemata even though it uses different system tables.

Package

postmodern.

Source

namespace.lisp.

Function: list-sequences (&optional strings-p)

Return a list of the sequences in a database. Turn them into keywords if strings-p is not true.

Package

postmodern.

Source

util.lisp.

Function: list-table-indices (table-name &optional strings-p)

List the index names and the related columns in a single table. Each index will be in a separate sublist.

Package

postmodern.

Source

util.lisp.

Function: list-table-sizes (&key schema order-by-size size)

Returns a list of lists (table-name, size in 8k pages) of tables in the current database. Providing a name to the schema parameter will return just the information for tables in that schema. It defaults to just the tables in the public schema. Setting schema to nil will return all tables, indexes etc in the database in descending order of size. This would include system tables, so there are a lot more than you would expect. If :size is set to nil, it returns only a flat list of table names. Setting order-by-size to t will return the result in order of size instead of by table name.

Package

postmodern.

Source

util.lisp.

Function: list-tables (&optional strings-p)

DEPRECATED FOR LIST-ALL-TABLES. Return a list of the tables in the public schema of a database. By default the table names are returned as keywords. They will be returned as lowercase strings if strings-p is true.

Package

postmodern.

Source

util.lisp.

Function: list-tables-in-schema (&optional schema-name strings-p)

Returns a list of tables in a particular schema, defaulting to public.
If schema-name is :all or "all", it will return all the non-system tables in the database in fully qualified form: e.g. ’public.test_table’. If string-p is t, the names will be returned as strings with underscores converted to hyphens.

Package

postmodern.

Source

util.lisp.

Function: list-tablespaces ()

Lists the tablespaces in the currently connected database. What are tablespace you ask? Per the Postgresql documentation https://www.postgresql.org/docs/current/manage-ag-tablespaces.html: Tablespaces in PostgreSQL allow database administrators to define locations in the file system where the files representing database objects can be stored. Once created, a tablespace can be referred to by name when creating database objects.

By using tablespaces, an administrator can control the disk layout of a PostgreSQL installation. This is useful in at least two ways. First, if the partition or volume on which the cluster was initialized runs out of space and cannot be extended, a tablespace can be created on a different partition and used until the system can be reconfigured.

Second, tablespaces allow an administrator to use knowledge of the usage pattern of database objects to optimize performance. For example, an index which is very heavily used can be placed on a very fast, highly available disk, such as an expensive solid state device. At the same time a table storing archived data which is rarely used or not performance critical could be stored on a less expensive, slower disk system.

Package

postmodern.

Source

util.lisp.

Function: list-templates ()

Returns a list of existing database template names.

Package

postmodern.

Source

util.lisp.

Function: list-triggers (&optional table-name)

List distinct trigger names from the information_schema table. Table-name can be either quoted or string. (A trigger is a specification that the database should automatically execute a particular function whenever a certain type of operation is performed. Triggers can be attached to tables (partitioned or not), views, and foreign tables.
See https://www.postgresql.org/docs/current/trigger-definition.html)

Package

postmodern.

Source

util.lisp.

Function: list-unique-or-primary-constraints (table-name &optional strings-p)

List constraints on a table. Table-name can be either a string or quoted. Turns constraints into keywords if strings-p is not true.

Package

postmodern.

Source

util.lisp.

Function: list-views (&optional strings-p)

Returns list of the user defined views in the current database. When strings-p is T, the names will be returned as strings, otherwise as keywords.

Package

postmodern.

Source

util.lisp.

Function: load-uuid-extension ()

Loads the Postgresql uuid-ossp contrib module. Once loaded, you can call uuid generation functions such as uuid_generate_v4 within a query. E.g.

(query "select uuid_generate_v4()")

See Postgresql documentation at https://www.postgresql.org/docs/current/uuid-ossp.html

Package

postmodern.

Source

util.lisp.

Function: log-query (query time-units)

This function is default value of *QUERY-CALLBACK* and logs queries to *QUERY-LOG* if it is not NIL.

Package

cl-postgres.

Source

errors.lisp.

Function: num-records-in-database ()

Returns a list of lists with schema, table name and approximate number of records in the currently connected database.

Package

postmodern.

Source

util.lisp.

Function: oid-types-match-p (x y)

Returns t if the two parameters have matching types

Package

cl-postgres.

Source

data-types.lisp.

Function: open-database (database user password host &optional port use-ssl service application-name use-binary)

Create and open a connection for the specified server, database, and user. use-ssl may be :no, :try, :yes, or :full; where :try means ’if the server supports it’. :require uses provided ssl certificate with no verification. :yes only verifies that the server cert is issued by a trusted CA,
but does not verify the server hostname. :full ’means expect a CA-signed cert for the supplied host name’ and verify the server hostname. When it is anything but :no, you must have the CL+SSL package loaded to initiate the connection.

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.

Package

cl-postgres.

Source

public.lisp.

Function: open-db-writer (db-spec table columns)

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.

Package

cl-postgres.

Source

bulk-copy.lisp.

Function: param-to-oid (param)

Returns the postgresql oid for parameters which are going to be passed
from postmodern to postgresql in binary. Currently that only includes integers, single-floats, double-floats and boolean. Everything else will be passed as
text for postgresql to interpret. We do not do arrays because passing them in Postgresql’s binary format is actually more overhead than sending the string literal version. See https://www.codesynthesis.com/pipermail/odb-users/2012-August/000688.html.

If you are wondering why text is not included in this function, many Postgresql
data types have no common lisp equivalent and therefore must be
passed as string literals. Specifying that something was text
when it is not will result in Postgresql throwing type mismatch errors.

Package

cl-postgres.

Source

data-types.lisp.

Function: parameter-list-types (lst)

Takes a list of parameters and returns the matching postgresql oid types

Package

cl-postgres.

Source

data-types.lisp.

Function: parameter-lists-match-oid-types-p (x y)

Takes two lists and validates that the lists have matching postgresql oid types.

Package

cl-postgres.

Source

data-types.lisp.

Function: parse-queries (file-content)

Read SQL queries in given string and split them, returns a list

Package

postmodern.

Source

execute-file.lisp.

Function: postgres-array-string-to-array (str)

Takes a postgresql array in the form of a string like "{wol=CTc/wol,a=c/wol,b=c/wol}" and returns a lisp array like #("wol=CTc/wol" "a=c/wol" "b=c/wol")

Package

postmodern.

Source

util.lisp.

Function: postgres-array-string-to-list (str)

Takes a postgresql array in the form of a string like "{wol=CTc/wol,a=c/wol,b=c/wol}" and returns a lisp list like ("wol=CTc/wol" "a=c/wol" "b=c/wol").

Package

postmodern.

Source

util.lisp.

Function: postgresql-version ()

Returns the version string provided by postgresql of the current postgresql server E.g. ’PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled
by gcc (Arch Linux 9.3.0-1) 9.3.0, 64-bit’. If you want just the postgresql version number, use cl-postgres:get-postgresql-version.

Package

postmodern.

Source

util.lisp.

Function: postgresql-version-at-least (desired-version connection)

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.

Package

cl-postgres.

Source

public.lisp.

Function: prepare-query (connection name query &optional parameters)

Parse and plan the given query, and store it with Postgresql 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. To make this useful in cl-postgres while (connection-use-binary connection) is true, you need to pass a list of parameters with the same type as you will be using when you call (exec-prepared).

For example:

(prepare-query connection "test6" "select $1, $2" ’(1 T))
(exec-prepared connection "test6" ’(42 nil) ’list-row-reader)

Package

cl-postgres.

Source

public.lisp.

Function: prepared-statement-exists-p (name)

Returns t if the prepared statement exists in the current postgresql session, otherwise nil.

Package

postmodern.

Source

prepare.lisp.

Function: read-queries (filename &key remove-comments)

Read SQL queries in given file and split them, returns a list. Track included files so there is no accidental infinite loop. The default setting is to remove sql comments from the file before executing the sql code. If that causes problems, the remove-comments parameter can be set to nil.

Package

postmodern.

Source

execute-file.lisp.

Function: read-utf-8-string (input &key null-terminated stop-at-eof char-length byte-length)

Read utf-8 encoded data from a byte stream and construct a string with the characters found. When null-terminated is given it will stop reading at a null character, stop-at-eof tells it to stop at the end of file without raising an error, and the char-length and byte-length parameters can be used to specify the max amount of characters or bytes to read.

Package

cl-postgres-trivial-utf-8.

Source

trivial-utf-8.lisp.

Function: release-savepoint (savepoint)

Immediately release a savepoint, commiting its results.

Package

postmodern.

Source

transaction.lisp.

Function: rename-column (table old-name new-name)

Rename a column in a table. Parameters can be strings or symbols. If the table
is not in the public schema, it needs to be fully qualified - e.g. schema.table.column Returns t if successful.

Package

postmodern.

Source

util.lisp.

Function: rename-table (old-name new-name)

Rename a table. Parameters can be strings or symbols. If you are renaming a table using a fully qualified schema.table-name, you do not need to specify the schema in the new-name. You cannot use this function to move tables from one schema to another.

Package

postmodern.

Source

util.lisp.

Function: reopen-database (conn &optional connection-attempts)

Re-establish a database connection for a previously closed connection object. (Calling this on a connection that is still open is harmless.)

Package

cl-postgres.

Source

public.lisp.

Function: reset-prepared-statement (condition &optional params)

If you have received an invalid-prepared-statement error or a prepared-statement already exists error but the prepared statement is still in the meta slot in the postmodern connection, this will try to regenerate the prepared statement at the database connection level and restart the connection.

Package

postmodern.

Source

prepare.lisp.

Function: retry-transaction (&optional condition)

Invokes the retry-transaction restart, if found.

Package

postmodern.

Source

transaction.lisp.

Function: revoke-all-on-table (table-name role-name)

Takes a table-name which could be a string, symbol or list of strings or symbols of tables names, a role name and revokes all privileges that role-name may have with that/those tables. This is limited to the currently connected database and can only revoke the privileges granted by the caller of the function.

Package

postmodern.

Source

roles.lisp.

Function: role-exists-p (role-name)

Does the named role exist in this database cluster? Returns t or nil

Package

postmodern.

Source

roles.lisp.

Function: rollback-savepoint (savepoint)

Immediately roll back a savepoint, aborting the results.

Package

postmodern.

Source

transaction.lisp.

Function: rollback-transaction (transaction)

Roll back the given transaction to the beginning, but the transaction
block is still active. Thus calling abort-transaction in the middle of a transaction does not end the transaction. Any subsequent statements will still be executed. Per the Postgresql documentation: this rolls back the current transaction and causes all the updates made by the transaction to be discarded.

Package

postmodern.

Source

transaction.lisp.

Function: saslprep-normalize (str &optional form)

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.

Package

cl-postgres.

Source

saslprep.lisp.

Function: save-dao (dao)

Tries to insert the given dao using insert-dao. If the dao has unbound slots, those slots will be updated and bound by default data triggered by the database. If this raises a unique key violation error, it tries to update it by using update-dao instead. In this case, if the dao has unbound slots, updating will fail with an unbound slots error.

Be aware that there is a possible race condition here ― if some other process deletes the row at just the right moment, the update fails as well. Returns a boolean telling you whether a new row was inserted.

This function is unsafe to use inside of a transaction ― when a row with the given keys already exists, the transaction will be aborted. Use save-dao/transaction instead in such a situation.

See also: upsert-dao.

Package

postmodern.

Source

table.lisp.

Function: save-dao/transaction (dao)

The transaction safe version of save-dao. Tries to insert the given dao using insert-dao. If this raises a unique key violation error, it tries to update it by using update-dao instead. If the dao has unbound slots, updating will fail with an unbound slots error. If the dao has unbound slots, those slots will be updated and bound by default data triggered by the database.

Acts exactly like save-dao, except that it protects its attempt to insert the object with a rollback point, so that a failure will not abort the transaction.

See also: upsert-dao.

Package

postmodern.

Source

table.lisp.

Function: schema-exists-p (name)

Tests for the existence of a given schema. Returns T if the schema exists or nil otherwise. The name provided can be either a string or quoted symbol.

Package

postmodern.

Source

namespace.lisp.

Function: sequence-exists-p (sequence)

Tests whether a sequence with the given name exists. The name can be either a string or a symbol.

Package

postmodern.

Source

util.lisp.

Function: sequence-next (sequence)

Shortcut for getting the next value from a sequence. The sequence identifier can be either a string or a symbol, in the latter case it will be converted to a string according to S-SQL rules.

Package

postmodern.

Source

util.lisp.

Function: set-search-path (path)

This changes the postgresql runtime parameter controlling what order schemas are searched. You can always use fully qualified names [schema.table]. By default, this function only changes the search path for the current session. This function is used by with-schema.

Package

postmodern.

Source

namespace.lisp.

Function: set-sql-datetime-readers (&key date timestamp timestamp-with-timezone interval time table)

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:

- :date (days)

Where days is the amount of days since January 1st, 2000.

- :timestamp (useconds)

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

- :timestamp-with-timezone

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

- :time (useconds)

Refers to a time of day, counting from midnight.

- :interval (months days useconds)

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.

Defaults are provided as follows:
#’default-date-reader
#’default-timestamp-reader
#’default-interval-reader
#’default-time-reader

e.g.
(defun make-temp-postgres-query-requiring-unix-timestamps ()
(flet ((temp-timestamp-reader (useconds-since-2000)
(- (+ +start-of-2000+ (floor useconds-since-2000 1000000)) (encode-universal-time 0 0 0 1 1 1970 0)))) (set-sql-datetime-readers
:date #’temp-timestamp-reader)
(let ((query (make-postgres-query-requiring-unix-timestamps)) (set-sql-datetime-readers
:date #’default-timestamp-reader)
query))))

Package

cl-postgres.

Source

interpret.lisp.

Function: set-sql-reader (oid function &key table binary-p)

Define a new reader for a given type. table defaults to *sql-readtable*. 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.

Package

cl-postgres.

Source

interpret.lisp.

Function: split-fully-qualified-tablename (name)

Take a tablename of the form database.schema.table or schema.table or table and return the tablename and the schema name. The name can be a symbol or a string. Returns a list of form ’(table schema database. If the tablename is not fully qualified, it will assume that the schema should be "public".

Package

postmodern.

Source

util.lisp.

Function: sql-compile (form)

This is the run-time variant of the sql macro. It converts the given list to an SQL query, with the same rules except that symbols in this list do not have to be quoted to be interpreted as identifiers. For example:

(sql-compile ’(:select ’* :from ’country :where (:= ’a 1)))
"(SELECT * FROM country WHERE (a = 1))"

but

(sql (:select ’* :from ’country :where (:= ’a 1)))

would throw an error. For the later case you need to use sql.

Package

s-sql.

Source

s-sql.lisp.

Function: sql-error (control &rest args)
Package

s-sql.

Source

s-sql.lisp.

Function: sql-escape-string (string &optional prefix)

Escape string data so it can be used in a query. Example:

(sql-escape-string "Puss in ’Boots’")

"E’Puss in ”Boots”’"

Package

s-sql.

Source

s-sql.lisp.

Function: sql-template (form)

In cases where you do need to build the query at run time, yet you do not want to re-compile it all the time, this function can be used to compile it once and store the result. It takes an S-SQL form, which may contain
$$ placeholder symbols, and returns a function that takes one argument for every $$. When called, this returned function produces an SQL string in which the placeholders have been replaced by the values of the arguments.

Package

s-sql.

Source

s-sql.lisp.

Function: string-mapped-to-nothing (str)

Reads a string and removes any character that should be mapped to nothing per RFC 3454 and RFC 4013.

Package

cl-postgres.

Source

saslprep.lisp.

Function: string-mapped-to-space (str)

Reads a string and converts any character which should be mapped to a space pre RFC 3454 and RFC 4013 to a space.

Package

cl-postgres.

Source

saslprep.lisp.

Function: string-printable-ascii-p (str)

Returns t if every character in the string is printable ascii.

Package

cl-postgres.

Source

saslprep.lisp.

Function: string-to-utf-8-bytes (string &key null-terminate)

Convert a string into an array of unsigned bytes containing its utf-8 representation.

Package

cl-postgres-trivial-utf-8.

Source

trivial-utf-8.lisp.

Function: table-description (table-name &optional schema-name)

Returns a list of the fields in the named table. Each field is represented by a list of three elements: the field name, the type, and a boolean indicating whether the field may be NULL.

Table can be either a string or quoted. Table-names can be fully qualified with the schema or not. If the table-name is not fully qualified and a schema name is not provided, the table will be assumed to be in the public schema.

Package

postmodern.

Source

util.lisp.

Function: table-description-menu (table-name &key char-max-length data-type-length has-default default-value not-null numeric-precision numeric-scale storage primary primary-key-name unique unique-key-name fkey fkey-name fkey-col-id fkey-table fkey-local-col-id identity generated collation col-comments locally-defined inheritance-count stat-collection)

Takes a fully qualified table name which can be either a string or a symbol.
Returns three values.

1. A list of plists of each row’s parameters. This will always
include :column-name and :data-type-name but all other parameters can be set or unset
and are set by default (set to t).

2. The comment string attached to the table itself (if any).

3. A list of the check constraints applied to the rows in the table. See documentation for
list-check-constraints for an example.

The available keyword parameters are:

- char-max-length (Typically used for something like a varchar and shows the maximum length)
- data-type-length (For a fixed-size type, typlen is the number of bytes in the internal representation of the type. But for a variable-length type, typlen is negative. -1 indicates a “varlena” type (one that has a length word), -2 indicates a null-terminated C string.)
- has-default (value T if this column has a default value and :NULL if not)
- default-value (value is the default value as string. A default of 9.99 will still be a string)
- not-null (value is T if the column must have a value or :NULL otherwise)
- numeric-precision (value is the total number of digits for a numeric type if that precision was specified)
- numeric-scale (value is the number of digits in the fraction part of a numeric type if that scale was specified)
- storage (value is the storage setting for a column. Result can be plain, extended, main or external)
- primary (value is T if the column is the primary key for the table, :NULL otherwise)
- primary-key-name (value is the name of the primary-key itself, not the column, if the column is the primary key for the table, :NULL otherwise)
- unique (value is T if the column is subject to a unique key, :NULL otherwise)
- unique-key-name (value is the name of the unique-key itself, not the column, applied to the column, :NULL otherwise)
- fkey (value is T if the column is a foreign key, :NULL otherwise)
- fkey-name (value is the name of the foreign key, :NULL otherwise)
- fkey-col-id (value is the column id of the foreign table used as the foreign key. Probably easier to use the Postmodern function list-foreign-keys if you are looking for the name of the columns)
- fkey-table (value is the name of the foreign table, :NULL otherwise)
- fkey-local-col-id (value is the column id of this column. Probably easier to use the Postmodern function list-foreign-keys if you are looking for the name of the columns involved in the foreign key)
- identity (if the column is an identity column, the values can be ’generated always’ or ’generated by default’. Otherwise :NULL)
- generated (columns can be generated, if this column is generated and stored on disk, the value will be ’stored’, otherwise :NULL)
- collation (columns with collations which are not the default collation for the database will show that collation here, otherwise :NULL)
- col-comments (value is any comment that has been applied to the column, :NULL otherwise)
- locally-defined (value is T if locally defined. It might be both locally defined and inherited)
- inheritance-count (the number of direct ancestors this column has inherited)
- stat-collection (stat-collection returns the value of attstattarget which controls the level of detail of statistics accumulated for this column by ANALYZE. A zero value indicates that no statistics should be collected. A negative value says to use the system default statistics target. The exact meaning of positive values is data type-dependent. For scalar data types, attstattarget is both the target number of most common values to collect, and the target number of histogram bins to create. Attstorage is normally a copy of pg_type.typstorage of this column’s type. For TOAST-able data types, this can be altered after column creation to control storage policy.)

Package

postmodern.

Source

util.lisp.

Function: table-description-plus (table-name &optional schema-name)

Returns more table info than table-description. It defaults to returning column-name, data-type, character-maximum-length, modifier,
whether it is not-null and the default value.

Table can be either a string or quoted. Table-names can be fully qualified with the schema or not. If the table-name is not fully qualified and a schema name is not provided, the table will be assumed to be in the public schema.

Package

postmodern.

Source

util.lisp.

Function: table-exists-p (table-name &optional schema-name)

Check whether a table exists in a particular schema. Defaults to the search path. Takes either a string or a symbol for the table name. The table-name can be fully qualified in the form of schema.table-name or database.schema.table-name. If the schema is specified either in a qualified table-name or in the optional schema-name parameter, we look directly to the information schema tables. Otherwise we use the search path which can be controlled by being within a with-schema form.

Package

postmodern.

Source

util.lisp.

Function: table-size (table-name)

Return the size of a given postgresql table in k or m. Table-name can be either a string or quoted.

Package

postmodern.

Source

util.lisp.

Function: terminate-backend (pid &optional database)

Less polite way of terminating at the database (as opposed to calling close-database). Faster than (cancel-backend pid) and more reliable.

Package

postmodern.

Source

prepare.lisp.

Function: to-sql-name (name &optional escape-p ignore-reserved-words)

Convert a symbol or string into a name that can be a sql table, column, or operation name. Add quotes when escape-p is true, or escape-p is :auto and the name contains reserved words. Quoted or delimited identifiers can be used by passing :literal as the value of escape-p. If escape-p is :literal, and the name is a string then the string is still escaped but the symbol or string is not downcased, regardless of the setting for *downcase-symbols* and the hyphen and forward slash characters are not replaced with underscores.

Ignore-reserved-words is only used internally for column names which are allowed to be reserved words, but it is not recommended.

Package

s-sql.

Source

s-sql.lisp.

Function: types-match-p (x y)
Package

cl-postgres.

Source

data-types.lisp.

Function: unprepare-query (connection name)

Close the prepared query given by name by closing the session connection.
Does not remove the query from the meta slot in connection. This is not the same as keeping the connection open and sending Postgresql query to deallocate the named prepared query.

Package

cl-postgres.

Source

public.lisp.

Function: use-binary-parameters (db-connection param)

Accepts a database connection and nil or t. The default for cl-postgres/Postmodern is pass parameters to Postgresql as text (not in binary format). This is how it has been since the beginning of Postmodern and the default is set this way in order to avoid breaking existing user code. You can set Postmodern to pass integer, float
or boolean parameters to Postgresql in binary format on a connection basis when
the connection is created or you can use this function to change the existing connection to use or not use binary parameter passing.

Package

cl-postgres.

Source

public.lisp.

Function: utf-8-byte-length (string)

Calculate the amount of bytes needed to encode a string.

Package

cl-postgres-trivial-utf-8.

Source

trivial-utf-8.lisp.

Function: utf-8-bytes-to-string (bytes-in &key start end)

Convert a byte array containing utf-8 encoded characters into the string it encodes.

Package

cl-postgres-trivial-utf-8.

Source

trivial-utf-8.lisp.

Function: utf-8-group-size (byte)

Determine the amount of bytes that are part of the character starting with a given byte.

Package

cl-postgres-trivial-utf-8.

Source

trivial-utf-8.lisp.

Function: uuip-p (item)

Checking whether a string is a uuid. It does require the uuid string to be in hyphenated form. Like Postgresql, it will accept both upper and lower case, so looser than the specification which requires lower case only.

Package

cl-postgres.

Source

data-types.lisp.

Function: valid-sql-identifier-p (str)

Takes a string and returns it if it is a valid sql identifier. See https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS. First test is for a quoted string, which has less restrictions.

Package

postmodern.

Source

util.lisp.

Function: vector-row-reader (g0 fields)
Package

cl-postgres.

Source

public.lisp.

Function: view-exists-p (view)

Tests whether a view with the given name exists. Takes either a string or a symbol for the view name.

Package

postmodern.

Source

util.lisp.

Function: wait-for-notification (connection)

This function blocks until asynchronous notification is received on the connection. Return the channel string, the payload and notifying pid as multiple values. The PostgreSQL LISTEN command must be used to enable listening for notifications.

Package

cl-postgres.

Source

public.lisp.

Function: write-utf-8-bytes (string output &key null-terminate)

Write a string to a byte-stream, encoding it as utf-8.

Package

cl-postgres-trivial-utf-8.

Source

trivial-utf-8.lisp.


6.1.5 Generic functions

Generic Reader: abort-hooks (obj)

An accessor for the transaction or savepoint’s list of abort
hooks, each of which should be a function with no required arguments. These functions will be executed when a transaction is aborted or a savepoint rolled back (whether via a non-local transfer of control or explicitly by either abort-transaction or rollback-savepoint).

Package

postmodern.

Source

transaction.lisp.

Methods
Reader Method: abort-hooks ((transaction-handle transaction-handle))

automatically generated reader method

Target Slot

abort-hooks.

Generic Writer: (setf abort-hooks) (object)
Package

postmodern.

Methods
Writer Method: (setf abort-hooks) ((transaction-handle transaction-handle))

automatically generated writer method

Source

transaction.lisp.

Target Slot

abort-hooks.

Generic Function: abort-logical-transaction (obj)

Roll back the given logical transaction, regardless of whether it is an actual transaction or a savepoint.

Package

postmodern.

Source

transaction.lisp.

Methods
Method: abort-logical-transaction ((transaction transaction-handle))
Method: abort-logical-transaction ((savepoint savepoint-handle))
Generic Reader: commit-hooks (obj)

An accessor for the transaction or savepoint’s list of commit
hooks, each of which should be a function with no required arguments. These functions will be executed when a transaction is committed or a savepoint released.

Package

postmodern.

Source

transaction.lisp.

Methods
Reader Method: commit-hooks ((transaction-handle transaction-handle))

automatically generated reader method

Target Slot

commit-hooks.

Generic Writer: (setf commit-hooks) (object)
Package

postmodern.

Methods
Writer Method: (setf commit-hooks) ((transaction-handle transaction-handle))

automatically generated writer method

Source

transaction.lisp.

Target Slot

commit-hooks.

Generic Function: commit-logical-transaction (obj)

Commit the given logical transaction, regardless of whether it is an actual transaction or a savepoint.

Package

postmodern.

Source

transaction.lisp.

Methods
Method: commit-logical-transaction ((transaction transaction-handle))
Method: commit-logical-transaction ((savepoint savepoint-handle))
Generic Reader: connection-parameters (obj)

This method returns a mapping (string to string) containing all the configuration parameters for the connection.

Package

cl-postgres.

Source

public.lisp.

Methods
Reader Method: connection-parameters ((database-connection database-connection))

automatically generated reader method

Target Slot

parameters.

Generic Writer: (setf connection-parameters) (object)
Package

cl-postgres.

Methods
Writer Method: (setf connection-parameters) ((database-connection database-connection))

automatically generated writer method

Source

public.lisp.

Target Slot

parameters.

Generic Reader: connection-use-binary (object)
Package

cl-postgres.

Methods
Reader Method: connection-use-binary ((database-connection database-connection))

automatically generated reader method

Source

public.lisp.

Target Slot

use-binary.

Generic Writer: (setf connection-use-binary) (object)
Package

cl-postgres.

Methods
Writer Method: (setf connection-use-binary) ((database-connection database-connection))

automatically generated writer method

Source

public.lisp.

Target Slot

use-binary.

Generic Function: dao-exists-p (dao)

Test whether a row with the same primary key as the given
dao exists in the database. Will also return NIL when any of the key slots in the object are unbound.

Package

postmodern.

Source

table.lisp.

Generic Function: dao-keys (class)

Returns list of slot names that are the primary key of DAO
class. Explicit keys takes priority over col-identity which takes priority over col-primary-key.

This is likely interesting if you have primary keys which are composed of more than one slot. Pay careful attention to situations where the primary key not only has more than one column, but they are actually in a different order than they are in the database table itself. Obviously the table needs to have been defined. You can provide a quoted class-name or an instance of a dao.

Package

postmodern.

Source

table.lisp.

Methods
Method: dao-keys (dao)
Method: dao-keys ((class-name symbol))
Reader Method: dao-keys :before ((class dao-class))
Target Slot

effective-keys.

Method: dao-keys ((dao-class dao-class))

automatically generated reader method

Generic Reader: database-error-cause (condition)
Package

cl-postgres.

Methods
Reader Method: database-error-cause ((condition database-error))
Source

errors.lisp.

Target Slot

cause.

Generic Reader: database-error-code (condition)
Package

cl-postgres.

Methods
Reader Method: database-error-code ((condition database-error))
Source

errors.lisp.

Target Slot

error-code.

Generic Reader: database-error-detail (condition)
Package

cl-postgres.

Methods
Reader Method: database-error-detail ((condition database-error))
Source

errors.lisp.

Target Slot

detail.

Generic Reader: database-error-message (condition)
Generic Writer: (setf database-error-message) (condition)
Package

cl-postgres.

Methods
Reader Method: database-error-message ((condition database-error))
Writer Method: (setf database-error-message) ((condition database-error))
Source

errors.lisp.

Target Slot

message.

Generic Reader: database-error-query (condition)
Package

cl-postgres.

Methods
Reader Method: database-error-query ((condition database-error))
Source

errors.lisp.

Target Slot

query.

Generic Function: delete-dao (dao)

Delete the given dao from the database.

Package

postmodern.

Source

table.lisp.

Generic Function: disconnect (database)

Disconnects a normal database connection, or moves a pooled connection into the pool.

Package

postmodern.

Source

connect.lisp.

Methods
Method: disconnect ((connection pooled-database-connection))

Add the connection to the corresponding pool, or drop it when the pool is full.

Method: disconnect ((connection database-connection))
Generic Function: fetch-defaults (object)

Used to fetch the default values of an object on
creation. An example would be creating a dao object with unbounded slots. Fetch-defaults could then be used to fetch the default values from the database and bind the unbound slots which have default values. E.g.

(let ((dao (make-instance ’test-data :a 23)))
(pomo:fetch-defaults dao))

Returns dao if there were unbound slots with default values, nil otherwise.

Package

postmodern.

Source

table.lisp.

Generic Reader: field-name (field)

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.

Package

cl-postgres.

Source

protocol.lisp.

Methods
Reader Method: field-name ((field-description field-description))

automatically generated reader method

Target Slot

name.

Generic Writer: (setf field-name) (object)
Package

cl-postgres.

Methods
Writer Method: (setf field-name) ((field-description field-description))

automatically generated writer method

Source

protocol.lisp.

Target Slot

name.

Generic Reader: field-type (field)

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.

Package

cl-postgres.

Source

protocol.lisp.

Methods
Reader Method: field-type ((field-description field-description))

automatically generated reader method

Target Slot

type-id.

Generic Writer: (setf field-type) (object)
Package

cl-postgres.

Methods
Writer Method: (setf field-type) ((field-description field-description))

automatically generated writer method

Source

protocol.lisp.

Target Slot

type-id.

Generic Function: find-primary-key-column (class)

Loops through a class’s column definitions and returns
the first column name that has bound either col-identity or col-primary-key. Returns a symbol.

Package

postmodern.

Source

table.lisp.

Methods
Method: find-primary-key-column (dao)
Method: find-primary-key-column ((class symbol))
Method: find-primary-key-column ((class dao-class))
Generic Function: get-dao (type &rest args)

Select the DAO object from the row that has the given primary
key values, or NIL if no such row exists. Objects created by this function will have initialize-instance called on them (after loading in the values from the database) without any arguments ― even :default-initargs are skipped.
The same goes for select-dao and query-dao.

Package

postmodern.

Source

table.lisp.

Methods
Method: get-dao ((class-name symbol) &rest args)
Generic Function: insert-dao (dao)

Insert the given dao into the database. Column slots of the
object which are unbound implies the database defaults. Hence, if these columns has no defaults defined in the database, the the insertion of the dao will be failed. (This feature only works on PostgreSQL 8.2 and up.)

Package

postmodern.

Source

table.lisp.

Generic Function: make-dao (type &rest args &key &allow-other-keys)

Combines make-instance with insert-dao. Make the instance of
the given class and insert it into the database, returning the created instance.

Package

postmodern.

Source

table.lisp.

Methods
Method: make-dao ((class-name symbol) &rest args &key &allow-other-keys)
Method: make-dao ((class dao-class) &rest args &key &allow-other-keys)
Generic Reader: postgresql-notification-channel (condition)
Generic Writer: (setf postgresql-notification-channel) (condition)
Package

cl-postgres.

Methods
Reader Method: postgresql-notification-channel ((condition postgresql-notification))
Writer Method: (setf postgresql-notification-channel) ((condition postgresql-notification))
Source

protocol.lisp.

Target Slot

channel.

Generic Reader: postgresql-notification-payload (condition)
Generic Writer: (setf postgresql-notification-payload) (condition)
Package

cl-postgres.

Methods
Reader Method: postgresql-notification-payload ((condition postgresql-notification))
Writer Method: (setf postgresql-notification-payload) ((condition postgresql-notification))
Source

protocol.lisp.

Target Slot

payload.

Generic Reader: postgresql-notification-pid (condition)
Generic Writer: (setf postgresql-notification-pid) (condition)
Package

cl-postgres.

Methods
Reader Method: postgresql-notification-pid ((condition postgresql-notification))
Writer Method: (setf postgresql-notification-pid) ((condition postgresql-notification))
Source

protocol.lisp.

Target Slot

pid.

Generic Function: reconnect (database)

Reconnect a disconnected database connection. This is not
allowed for pooled connections ― after they are disconnected they might be in use by some other process, and should no longer be used.

Package

postmodern.

Source

connect.lisp.

Methods
Method: reconnect ((database database-connection))
Method: reconnect ((connection pooled-database-connection))
Generic Function: serialize-for-postgres (arg)

Conversion function used to turn a lisp value into a value
that PostgreSQL understands when sent through its socket connection. May return a string or a (vector (unsigned-byte 8)).

Package

cl-postgres.

Source

sql-string.lisp.

Methods
Method: serialize-for-postgres ((arg integer))
Method: serialize-for-postgres ((arg single-float))
Method: serialize-for-postgres ((arg double-float))
Method: serialize-for-postgres (arg)
Generic Function: sql-escape (arg)

A generalisation of sql-escape-string looks at the type of
the value passed, and properly writes it out it for inclusion in an SQL query. Symbols will be converted to SQL names. Examples:

(sql-escape "tr’-x")

"E’tr”-x’"

(sql-escape (/ 1 13))

"0.0769230769230769230769230769230769230"

(sql-escape #("Baden-Wurttemberg" "Bavaria" "Berlin" "Brandenburg"))

"ARRAY[E’Baden-Wurttemberg’, E’Bavaria’, E’Berlin’, E’Brandenburg’]"

Package

s-sql.

Source

s-sql.lisp.

Methods
Method: sql-escape ((arg symbol))
Method: sql-escape ((arg vector))
Method: sql-escape ((arg cons))
Method: sql-escape (arg)
Generic Function: sql-type-name (lisp-type &rest args)

Transform a lisp type into a string containing something SQL understands. Default is to just use the type symbol’s name.

Package

s-sql.

Source

s-sql.lisp.

Methods
Method: sql-type-name ((lisp-type symbol) &rest args)
Method: sql-type-name ((lisp-type (eql string)) &rest args)
Method: sql-type-name ((lisp-type (eql s-sql:varchar)) &rest args)
Method: sql-type-name ((lisp-type (eql s-sql:numeric)) &rest args)
Method: sql-type-name ((lisp-type (eql float)) &rest args)
Method: sql-type-name ((lisp-type (eql double-float)) &rest args)
Method: sql-type-name ((lisp-type (eql s-sql:double-precision)) &rest args)
Method: sql-type-name ((lisp-type (eql s-sql::serial)) &rest args)
Method: sql-type-name ((lisp-type (eql s-sql::serial8)) &rest args)
Method: sql-type-name ((lisp-type (eql array)) &rest args)
Method: sql-type-name ((lisp-type (eql s-sql:db-null)) &rest args)
Generic Reader: text (condition)
Package

s-sql.

Methods
Reader Method: text ((condition inconsistent-schema-name))
Source

util.lisp.

Target Slot

text.

Reader Method: text ((condition invalid-database-name))
Source

util.lisp.

Target Slot

text.

Reader Method: text ((condition malformed-composite-type-error))
Source

s-sql.lisp.

Target Slot

text.

Generic Function: to-sql-string (arg)

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.

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 different from s-sql::to-s-sql-string only in the handling of cons lists.

Package

cl-postgres.

Source

sql-string.lisp.

Methods
Method: to-sql-string ((arg string))
Method: to-sql-string ((arg vector))
Method: to-sql-string ((arg cons))
Method: to-sql-string ((arg array))
Method: to-sql-string ((arg integer))
Method: to-sql-string ((arg float))
Method: to-sql-string ((arg double-float))
Method: to-sql-string ((arg ratio))
Method: to-sql-string ((arg (eql t)))
Method: to-sql-string ((arg (eql nil)))
Method: to-sql-string ((arg (eql :null)))
Method: to-sql-string (arg)
Generic Function: update-dao (dao)

Update the representation of the given dao in the database
to the values in the object. This is not defined for tables that do not have any non-primary-key columns. Raises an error when no row matching the dao exists.

Package

postmodern.

Source

table.lisp.

Generic Function: upsert-dao (dao)

Like save-dao or save-dao/transaction but using a different
method that doesn’t involve a database exception. This is safe to use both in and outside a transaction, though it’s advisable to always do it in a transaction to prevent a race condition. The way it works is:

If the object contains unbound slots, we call insert-dao directly, thus the behavior is like save-dao.

Otherwise we try to update a record with the same primary key. If the
PostgreSQL returns a non-zero number of rows updated it treated as the
record is already exists in the database, and we stop here.

If the PostgreSQL returns a zero number of rows updated, it treated as the record does not exist and we call insert-dao.

The race condition might occur at step 3 if there’s no transaction: if UPDATE returns zero number of rows updated and another thread inserts the record at that moment, the insertion implied by step 3 will fail.

Note, that triggers and rules may affect the number of inserted or updated
rows returned by PostgreSQL, so zero or non-zero number of affected rows may
not actually indicate the existence of record in the database.

This method returns two values: the DAO object and a boolean (T if the object was inserted, NIL if it was updated).

IMPORTANT: This is not the same as insert on conflict (sometimes called an upsert) in Postgresq. An upsert in Postgresql terms is an insert with a fallback of updating the row if the insert key conflicts with an already existing row. An upsert-dao in Postmodern terms is the reverse. First you try updating an existing object. If there is no existing object to oupdate, then you insert a new object.

Package

postmodern.

Source

table.lisp.


6.1.6 Standalone methods

Method: compute-effective-slot-definition ((class dao-class) name direct-slot-definitions)
Package

sb-mop.

Source

table.lisp.

Method: direct-slot-definition-class ((class dao-class) &key column col-type &allow-other-keys)

Slots that have a :col-type option are column-slots.

Package

sb-mop.

Source

table.lisp.

Method: effective-slot-definition-class ((class dao-class) &rest initargs)
Package

sb-mop.

Source

table.lisp.

Method: finalize-inheritance :after ((class dao-class))

Building a row reader and a set of methods can only be done after inheritance has been finalised.

Package

sb-mop.

Source

table.lisp.

Method: print-object ((self bulk-copier) stream)
Source

bulk-copy.lisp.

Method: print-object ((object bad-char-error) stream)
Source

saslprep.lisp.

Method: print-object ((p parser) stream)
Source

execute-file.lisp.

Method: shared-initialize :before ((class dao-class) slot-names &key table-name &allow-other-keys)
Source

table.lisp.

Method: shared-initialize :after ((slot direct-column-slot) slot-names &key col-type col-default col-name &allow-other-keys)
Source

table.lisp.

Method: validate-superclass ((class dao-class) (super-class standard-class))
Package

sb-mop.

Source

table.lisp.


6.1.7 Conditions

Condition: admin-shutdown
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

server-shutdown.

Condition: cannot-connect-now
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

operator-intervention.

Condition: check-violation
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

integrity-violation.

Condition: columns-error
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

syntax-error-or-access-violation.

Condition: connection-does-not-exist
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

database-error.

Condition: connection-exception
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

database-error.

Condition: connection-failure
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

database-error.

Condition: crash-shutdown
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

server-shutdown.

Condition: data-exception
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

database-error.

Direct subclasses
Condition: database-connection-error

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.

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.

(defun call-with-single-reconnect (fun)
(let ((reconnected nil))
(handler-bind
((database-connection-error
(lambda (err)
(when (not reconnected)
(setf reconnected t)
(invoke-restart :reconnect)))))
(funcall fun))))

Package

cl-postgres.

Source

errors.lisp.

Direct superclasses

database-error.

Direct subclasses
Condition: database-connection-lost

Raised when a query is initiated on a disconnected connection object.

Package

cl-postgres.

Source

errors.lisp.

Direct superclasses

database-connection-error.

Condition: database-error

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.

Package

cl-postgres.

Source

errors.lisp.

Direct superclasses

error.

Direct subclasses
Direct methods
Direct slots
Slot: error-code

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

Initform

(quote nil)

Initargs

:code

Readers

database-error-code.

Writers

This slot is read-only.

Slot: message

Message: the primary human-readable error message.
This should be accurate but terse (typically one line). Always present.

Initargs

:message

Readers

database-error-message.

Writers

(setf database-error-message).

Slot: detail

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

Initform

(quote nil)

Initargs

:detail

Readers

database-error-detail.

Writers

This slot is read-only.

Slot: hint

Hint: an optional suggestion what to do about the problem.

Initform

(quote nil)

Initargs

:hint

Readers

database-error-hint.

Writers

This slot is read-only.

Slot: context

Where: an indication of the context in which the
error occurred. Presently this includes a call stack traceback of active procedural language functions and internally-generated queries. The trace is one entry per line, most recent first.

Initform

(quote nil)

Initargs

:context

Readers

database-error-context.

Writers

This slot is read-only.

Slot: query

Query that led to the error, or NIL if no query was involved.

Initform

(quote cl-postgres::*current-query*)

Readers

database-error-query.

Writers

This slot is read-only.

Slot: position

Position: the field value is a decimal ASCII
integer, indicating an error cursor position as an index into the original query string. The first character has index 1, and positions are measured in characters not bytes.

Package

common-lisp.

Initform

(quote nil)

Initargs

:position

Readers

database-error-position.

Writers

This slot is read-only.

Slot: cause

The condition that caused this error, or NIL when it was not caused by another condition.

Initform

(quote nil)

Initargs

:cause

Readers

database-error-cause.

Writers

This slot is read-only.

Condition: database-socket-error

Used to wrap stream-errors and socket-errors, giving them a database-connection-error superclass.

Package

cl-postgres.

Source

errors.lisp.

Direct superclasses

database-connection-error.

Condition: db-division-by-zero
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

data-exception.

Condition: deadlock-detected
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

transaction-rollback.

Condition: duplicate-alias
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

syntax-error-or-access-violation.

Condition: duplicate-column
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

syntax-error-or-access-violation.

Condition: duplicate-cursor
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

syntax-error-or-access-violation.

Condition: duplicate-database
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

syntax-error-or-access-violation.

Condition: duplicate-function
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

syntax-error-or-access-violation.

Condition: duplicate-object
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

syntax-error-or-access-violation.

Condition: duplicate-prepared-statement
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

syntax-error-or-access-violation.

Condition: duplicate-schema
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

syntax-error-or-access-violation.

Condition: duplicate-table
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

syntax-error-or-access-violation.

Condition: feature-not-supported
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

database-error.

Condition: floating-point-exception
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

data-exception.

Condition: foreign-key-violation
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

integrity-violation.

Condition: insufficient-privilege
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

syntax-error-or-access-violation.

Condition: insufficient-resources
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

database-error.

Condition: integrity-violation
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

database-error.

Direct subclasses
Condition: internal-error
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

database-error.

Condition: invalid-datetime-format
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

data-exception.

Condition: invalid-sql-statement-name
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

database-error.

Condition: lock-not-available
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

object-state-error.

Condition: not-null-violation
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

integrity-violation.

Condition: numeric-value-out-of-range
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

data-exception.

Condition: object-in-use
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

object-state-error.

Condition: object-state-error
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

database-error.

Direct subclasses
Condition: operator-intervention
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

database-error.

Direct subclasses
Condition: postgresql-notification

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.

Package

cl-postgres.

Source

protocol.lisp.

Direct superclasses

simple-warning.

Direct methods
Direct slots
Slot: pid
Initargs

:pid

Readers

postgresql-notification-pid.

Writers

(setf postgresql-notification-pid).

Slot: channel
Initargs

:channel

Readers

postgresql-notification-channel.

Writers

(setf postgresql-notification-channel).

Slot: payload
Initargs

:payload

Readers

postgresql-notification-payload.

Writers

(setf postgresql-notification-payload).

Condition: postgresql-warning
Package

cl-postgres.

Source

protocol.lisp.

Direct superclasses

simple-warning.

Condition: program-limit-exceeded
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

database-error.

Condition: protocol-violation
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

database-error.

Condition: query-canceled
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

operator-intervention.

Condition: restrict-violation
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

integrity-violation.

Condition: serialization-failure
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

transaction-rollback.

Condition: server-shutdown
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses
Direct subclasses
Condition: sql-error
Package

s-sql.

Source

s-sql.lisp.

Direct superclasses

simple-error.

Condition: sqlclient-unable-to-establish-sqlconnection
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

database-error.

Condition: sqlserver-rejected-establishment-of-sqlconnection
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

database-error.

Condition: statement-completion-unknown
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

transaction-rollback.

Condition: syntax-error-or-access-violation
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

database-error.

Direct subclasses
Condition: system-error
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

database-error.

Condition: transaction-integrity-constraint-violation
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

transaction-rollback.

Condition: transaction-resolution-unknown
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

database-error.

Condition: transaction-rollback
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

database-error.

Direct subclasses
Condition: undefined-column
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

syntax-error-or-access-violation.

Condition: undefined-table
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

syntax-error-or-access-violation.

Condition: unique-violation
Package

cl-postgres-error.

Source

errors.lisp.

Direct superclasses

integrity-violation.

Condition: utf-8-decoding-error
Package

cl-postgres-trivial-utf-8.

Source

trivial-utf-8.lisp.

Direct superclasses

simple-error.

Direct slots
Slot: message
Initargs

:message

Slot: byte
Package

common-lisp.

Initform

(quote nil)

Initargs

:byte


6.1.8 Classes

Class: dao-class

At the heart of Postmodern’s DAO system is the dao-class
metaclass. It allows you to define classes for your database-access objects as regular CLOS classes. Some of the slots in these classes will refer to columns
in the database. To specify that a slot refers to a column, give it a :col-type option containing an S-SQL type expression (useful if you want to be able to
derive a table definition from the class definition), or simply a :column
option with value T. Such slots can also take a :col-default option, used to
provide a database-side default value as an S-SQL expression. You can use the :col-name initarg (whose unevaluated value will be passed to to-sql-name) to
specify the slot’s column’s name.

DAO class definitions support two extra class options: :table-name to give the
name of the table that the class refers to (defaults to the class name), and
:keys to provide a set of primary keys for the table. If more than one key is provided, this creates a multi-column primary key and all keys must be
specified when using operations such as update-dao and get-dao. When no primary
keys are defined, operations such as update-dao and get-dao will not work.

IMPORTANT: Class finalization for a dao class instance are wrapped with a
thread lock. However, any time you are using threads and a class that
inherits from other classes, you should ensure that classes are finalized
before you start generating threads that create new instances of that class.

The (or db-null integer) form is used to indicate a column can have NULL values otherwise the column will be treated as NOT NULL.

Simple example:

(defclass users ()
((name :col-type string :initarg :name :accessor user-name)
(creditcard :col-type (or db-null integer) :initarg :card :col-default :null) (score :col-type bigint :col-default 0 :accessor user-score))
(:metaclass dao-class)
(:keys name))

In this case the name of the users will be treated as the primary key and the database table is assume to be users.

Now look at a slightly more complex example.

(defclass country ()
((id :col-type integer :col-identity t :accessor id)
(name :col-type text :col-unique t :initarg :country :accessor country) (region-id :col-type integer :col-references ((regions id)) :initarg :region-id :accessor region-id))
(:metaclass dao-class)
(:table-name countries))

In this example we have an id column which is specified to be an identity column. Postgresql will automatically generate a sequence of of integers and this will
be the primary key.

We have a name column which is specified as unique and is not null.

We have a region-id column which references the id column in the regions table.
This is a foreign key constraint and Postgresql will not accept inserting a country into the database unless there is an existing region with an id that matches this number. Postgresql will also not allow deleting a region if there are countries
that reference that region’s id. If we wanted Postgresql to delete countries when regions are deleted, that column would be specified as:

(region-id :col-type integer :col-references ((regions id) :cascade)
:initarg :region-id :accessor region-id)

Now you can see why the double parens.

We also specify that the table name is not ’country’ but ’countries’. (Some style guides recommend that table names be plural and references to rows be singular.)

When inheriting from DAO classes, a subclass’ set of columns also contains
all the columns of its superclasses. The primary key for such a class is the
union of its own keys and all the keys from its superclasses. Classes
inheriting from DAO classes should probably always use the dao-class metaclass themselves.

When a DAO is created with make-instance, the :fetch-defaults keyword argument
can be passed, which, when T, will cause a query to fetch the default values
for all slots that refers to columns with defaults and were not bound through initargs. In some cases, such as serial columns, which have an implicit default, this will not work. You can work around this by creating your own sequence,
e.g. ’my_sequence’, and defining a (:nextval "my_sequence") default.

Finally, DAO class slots can have an option :ghost t to specify them as ghost
slots. These are selected when retrieving instances, but not written when
updating or inserting, or even included in the table definition. The only known
use for this to date is for creating the table with (oids=true), and specify a
slot like this:

(oid :col-type integer :ghost t :accessor get-oid)

Package

postmodern.

Source

table.lisp.

Direct superclasses

standard-class.

Direct methods
Direct slots
Slot: direct-keys
Initargs

:keys

Readers

direct-keys.

Writers

This slot is read-only.

Slot: effective-keys
Readers

dao-keys.

Writers

This slot is read-only.

Slot: table-name
Slot: column-map
Readers

dao-column-map.

Writers

This slot is read-only.

Class: database-connection

Representation of a database connection. Contains
login information in order to be able to automatically re-establish a connection when it is somehow closed.

Package

cl-postgres.

Source

public.lisp.

Direct subclasses

pooled-database-connection.

Direct methods
Direct Default Initargs
InitargValue
:application-namepostmodern-default
Direct slots
Slot: host
Initargs

:host

Readers

connection-host.

Writers

This slot is read-only.

Slot: port
Initargs

:port

Readers

connection-port.

Writers

This slot is read-only.

Slot: database
Initargs

:db

Readers

connection-db.

Writers

This slot is read-only.

Slot: user
Initargs

:user

Readers

connection-user.

Writers

This slot is read-only.

Slot: password
Initargs

:password

Readers

connection-password.

Writers

This slot is read-only.

Slot: use-ssl
Initargs

:ssl

Readers

connection-use-ssl.

Writers

This slot is read-only.

Slot: use-binary
Initargs

:binary

Readers

connection-use-binary.

Writers

(setf connection-use-binary).

Slot: service
Initargs

:service

Readers

connection-service.

Writers

(setf connection-service).

Slot: application-name
Initargs

:application-name

Readers

connection-application-name.

Writers

(setf connection-application-name).

Slot: socket
Initargs

:socket

Readers

connection-socket.

Writers

(setf connection-socket).

Slot: meta
Slot: available
Initform

t

Readers

connection-available.

Writers

(setf connection-available).

Slot: parameters
Readers

connection-parameters.

Writers

(setf connection-parameters).

Slot: timestamp-format
Readers

connection-timestamp-format.

Writers

(setf connection-timestamp-format).


6.1.9 Types

Type: bigint ()

Also know as int8

Package

s-sql.

Source

s-sql.lisp.

Type: bytea ()
Package

s-sql.

Source

s-sql.lisp.

Type: db-null ()

Type for representing NULL values. Use like (or integer db-null) for declaring a type to be an integer that may be null.

Package

s-sql.

Source

s-sql.lisp.

Type: double-precision ()
Package

s-sql.

Source

s-sql.lisp.

Type: int4 ()
Package

cl-postgres.

Source

data-types.lisp.

Type: int8 ()
Package

cl-postgres.

Source

data-types.lisp.

Type: numeric (&optional precision/scale scale)
Package

s-sql.

Source

s-sql.lisp.

Type: smallint ()

Also known as int2

Package

s-sql.

Source

s-sql.lisp.

Type: text ()
Package

s-sql.

Source

s-sql.lisp.

Type: uuid-string ()
Package

cl-postgres.

Source

data-types.lisp.

Type: varchar (length)
Package

s-sql.

Source

s-sql.lisp.


6.2 Internals


6.2.1 Constants

Constant: +seconds-in-day+
Package

cl-postgres.

Source

interpret.lisp.

Constant: +start-of-2000+
Package

cl-postgres.

Source

interpret.lisp.


6.2.2 Special variables

Special Variable: *alter-all-default-editor-privileges*
Package

postmodern.

Source

config.lisp.

Special Variable: *alter-all-default-select-privileges*
Package

postmodern.

Source

config.lisp.

Special Variable: *character-sets*
Package

postmodern.

Source

config.lisp.

Special Variable: *class-finalize-lock*
Package

postmodern.

Source

query.lisp.

Special Variable: *client-encoding*
Package

cl-postgres.

Source

strings-utf-8.lisp.

Special Variable: *collations*
Package

postmodern.

Source

config.lisp.

Special Variable: *connection-params*

Bound to the current connection’s parameter table when executing a query.

Package

cl-postgres.

Source

protocol.lisp.

Special Variable: *connection-pools*

Maps pool specifiers to lists of pooled connections.

Package

postmodern.

Source

connect.lisp.

Special Variable: *current-query*
Package

cl-postgres.

Source

errors.lisp.

Special Variable: *custom-column-writers*

A hook for locally overriding/adding behaviour to DAO row readers. Should be an alist mapping strings (column names) to symbols or functions. Symbols are interpreted as slot names that values should be written to, functions are called with the new object and the value as arguments.

Package

postmodern.

Source

config.lisp.

Special Variable: *default-sql-readtable*

A copy of the default readtable that client code can fall back on.

Package

cl-postgres.

Source

interpret.lisp.

Special Variable: *direct-column-slot*

This is used to communicate the fact that a slot is a column to effective-slot-definition-class.

Package

postmodern.

Source

table.lisp.

Special Variable: *disallowed-role-names*

A set of words that maybe should be disallowed from user names. Edit as you please.

Package

postmodern.

Source

config.lisp.

Special Variable: *effected-rows*
Package

cl-postgres.

Source

protocol.lisp.

Special Variable: *error-table*
Package

cl-postgres-error.

Source

errors.lisp.

Special Variable: *execute-privileges-list*
Package

postmodern.

Source

config.lisp.

Special Variable: *expand-runtime*
Package

s-sql.

Source

s-sql.lisp.

Special Variable: *identifier-name-to-key*

Designator for a function which, during decoding, maps the *json-identifier-name-to-lisp* -transformed key to the value it will have in the result object.

Package

postmodern.

Source

config.lisp.

Special Variable: *json-aggregate-context*

NIL outside of any aggregate environment, ’ARRAY or ’OBJECT within the respective environments.

Package

postmodern.

Source

config.lisp.

Special Variable: *json-aggregate-first*

T when the first member of a JSON Object or Array is encoded, afterwards NIL.

Package

postmodern.

Source

config.lisp.

Special Variable: *json-identifier-name-to-lisp*

Designator for a function which maps string (a JSON Object key) to string (name of a Lisp symbol).

Package

postmodern.

Source

config.lisp.

Special Variable: *json-list-encoder-fn*
Package

postmodern.

Source

config.lisp.

Special Variable: *json-output*

The default output stream for encoding operations.

Package

postmodern.

Source

config.lisp.

Special Variable: *json-symbols-package*

The package where JSON Object keys etc. are interned. Default KEYWORD, NIL = use current *PACKAGE*.

Package

postmodern.

Source

config.lisp.

Special Variable: *lisp-identifier-name-to-json*

Designator for a function which maps string (name of a Lisp symbol) to string (e. g. JSON Object key).

Package

postmodern.

Source

config.lisp.

Special Variable: *optimize*
Package

cl-postgres.

Source

package.lisp.

Special Variable: *optimize*
Package

cl-postgres-trivial-utf-8.

Source

trivial-utf-8.lisp.

Special Variable: *pool-lock*

A lock to prevent multiple threads from messing with the connection pool at the same time.

Package

postmodern.

Source

connect.lisp.

Special Variable: *postgres-reserved-words*

A set of all PostgreSQL’s reserved words, for automatic escaping. Probably not a good idea to use these words as identifiers anyway.

Package

s-sql.

Source

config.lisp.

Special Variable: *printable-ascii-chars*
Package

cl-postgres.

Source

saslprep.lisp.

Special Variable: *result-styles*

Mapping from keywords identifying result styles to the row-reader that should be used and whether all values or only one value should be returned.

Package

postmodern.

Source

config.lisp.

Special Variable: *schema-path*

If the default path is reset, it will also reset this parameter which will get read by reconnect.

Package

postmodern.

Source

config.lisp.

Special Variable: *string-file*
Package

cl-postgres-system.

Source

cl-postgres.asd.

Special Variable: *tables*

Unexported ordered list containing the known table definitions.

Package

postmodern.

Source

deftable.lisp.

Special Variable: *timestamp-format*

This is used to communicate the format (integer or float) used for timestamps and intervals in the current connection, so that the interpreters for those types know how to parse them.

Package

cl-postgres.

Source

interpret.lisp.

Special Variable: *transaction-level*
Package

postmodern.

Source

config.lisp.

Special Variable: *unicode*
Package

cl-postgres-system.

Source

cl-postgres.asd.

Special Variable: +json-lisp-symbol-tokens+

Mapping between JSON literal names and Lisp boolean values.

Package

postmodern.

Source

config.lisp.

Special Variable: single-line-comment-scanner
Package

postmodern.

Source

execute-file.lisp.


6.2.3 Macros

Macro: all-rows (form)
Package

postmodern.

Source

query.lisp.

Macro: as-array-member ((&optional stream) &body body)

BODY should be a program which encodes exactly one JSON datum to STREAM. AS-ARRAY-MEMBER ensures that the datum is properly formatted as a Member of an Array, i. e. separated by comma from any preceding or following Member.

Package

postmodern.

Source

json-encoder.lisp.

Macro: as-object-member ((key &optional stream) &body body)

BODY should be a program which writes exactly one JSON datum to STREAM. AS-OBJECT-MEMBER ensures that the datum is properly formatted as a Member of an Object, i. e. preceded by the (encoded) KEY and colon, and separated by comma from any preceding or following Member.

Package

postmodern.

Source

json-encoder.lisp.

Macro: as-utf-8-bytes (char writer)

Given a character, calls the writer function for every byte in the encoded form of that character.

Package

cl-postgres-trivial-utf-8.

Source

trivial-utf-8.lisp.

Macro: binary-reader (fields &body value)

A slightly convoluted macro for defining interpreter functions. It allows two forms. The first is to pass a single type identifier, in which case a value of this type will be read and returned directly. The second is to pass a list of lists containing names and types, and then a body. In this case the names will be bound to values read from the socket and interpreted as the given types, and then the body will be run in the resulting environment. If the last field is of type bytes, string, or uint2s, all remaining data will be read and interpreted as an array of the given type.

Package

cl-postgres.

Source

interpret.lisp.

Macro: dao-row-reader-with-body ((type type-var) &body body)
Package

postmodern.

Source

table.lisp.

Macro: def-drop-op (op-name word)

Def-drop-op accepts variables, strings or symbols as the identifier.

Package

s-sql.

Source

s-sql.lisp.

Macro: def-sql-op (name arglist &body body)

Macro to make defining syntax a bit more straightforward. Name should be the keyword identifying the operator, arglist a lambda list to apply to the arguments, and body something that produces a list of strings and forms that evaluate to strings.

Package

s-sql.

Source

s-sql.lisp.

Macro: deferror (code typename &optional superclass)
Package

cl-postgres-error.

Source

errors.lisp.

Macro: define-interpreter (oid name fields &body value)

Shorthand for defining binary readers.

Package

cl-postgres.

Source

interpret.lisp.

Macro: define-message (name id (&rest arglist) &body parts)

This macro synthesizes a function to send messages of a specific type. It takes care of the plumbing – calling writer functions on a stream, keeping track of the length of the message – so that the message definitions themselves stay readable.

Package

cl-postgres.

Source

messages.lisp.

Macro: integer-reader (bytes)

Create a function to read integers from a binary stream.

Package

cl-postgres.

Source

communicate.lisp.

Macro: integer-writer (bytes)

Create a function to write integers to a binary stream.

Package

cl-postgres.

Source

communicate.lisp.

Macro: make-exists-query (relkind name)

Helper macro for the functions that check whether an object exists. Only works for public schema

Package

postmodern.

Source

util.lisp.

Macro: make-list-query (relkind)

Helper macro for the functions that list tables, sequences, and views.

Package

postmodern.

Source

util.lisp.

Macro: message-case (socket &body clauses)

Helper macro for reading messages from the server. A list of cases (characters that identify the message) can be given, each with a body that handles the message, or the keyword :skip to skip the message. Cases for error and warning messages are always added.

The body may contain an initial parameter of the form :LENGTH-SYM SYMBOL where SYMBOL is a symbol to which the remaining length of the packet is bound. This value indicates the number of bytes that have to be read from the socket.

Package

cl-postgres.

Source

protocol.lisp.

Macro: returning-effected-rows (value &body body)

Computes a value, then runs a body, then returns, as multiple values, that value and the amount of effected rows, if any (see *effected rows*).

Package

cl-postgres.

Source

protocol.lisp.

Macro: single-row (form)
Package

postmodern.

Source

query.lisp.

Macro: single-row! (form)
Package

postmodern.

Source

query.lisp.

Macro: split-on-keywords (words form &body body)

Handles arguments to some complex SQL operations. Arguments
are divided by keywords, which are interned with the name of the non-keyword symbols in words, and bound to these symbols. After the naming symbols, a ? can be used to indicate this argument group is optional, an * to indicate it can consist of more than one element, and a - to indicate it does not take any elements. When used, keywords must appear in the order defined.

Package

s-sql.

Source

s-sql.lisp.

Macro: using-connection (connection &body body)

This is used to prevent a row-reader from recursively calling some query function. Because the connection is still returning results from the previous query when a row-reading is being executed, starting another query will not work as expected (or at all, in general). This might also raise an error when you are using a single database connection from multiple threads, but you should not do that at all. Also binds *timestamp-format* and *connection-params*, which might be needed by the code interpreting the query results.

Package

cl-postgres.

Source

public.lisp.

Macro: with-aggregate ((context begin-char end-char &optional stream) &body body)

Run BODY to encode a JSON aggregate type, delimited by BEGIN-CHAR and END-CHAR.

Package

postmodern.

Source

json-encoder.lisp.

Macro: with-array ((&optional stream) &body body)

Open a JSON Array, run BODY, then close the Array. Inside the BODY, AS-ARRAY-MEMBER or ENCODE-ARRAY-MEMBER should be called to encode Members of the Array.

Package

postmodern.

Source

json-encoder.lisp.

Macro: with-explicit-encoder (&body body)
Package

postmodern.

Source

json-encoder.lisp.

Macro: with-guessing-encoder (&body body)
Package

postmodern.

Source

json-encoder.lisp.

Macro: with-local-encoder (&body body)
Package

postmodern.

Source

json-encoder.lisp.

Macro: with-object ((&optional stream) &body body)

Open a JSON Object, run BODY, then close the Object. Inside the BODY, AS-OBJECT-MEMBER or ENCODE-OBJECT-MEMBER should be called to encode Members of the Object.

Package

postmodern.

Source

json-encoder.lisp.

Macro: with-pool-lock (&body body)

Aquire a lock for the pool when evaluating body (if thread support is present).

Package

postmodern.

Source

connect.lisp.

Macro: with-query ((query) &body body)
Package

cl-postgres.

Source

errors.lisp.

Macro: with-reconnect-restart (connection &body body)

When, inside the body, an error occurs that breaks the connection socket, a condition of type database-connection-error is raised, offering a :reconnect restart.

Package

cl-postgres.

Source

public.lisp.

Macro: with-substitute-printed-representation-restart ((object stream) &body body)

Establish a SUBSTITUTE-PRINTED-REPRESENTATION restart for OBJECT and execute BODY.

Package

postmodern.

Source

json-encoder.lisp.

Macro: with-syncing (&body body)

Macro to wrap a block in a handler that will try to re-sync the connection if something in the block raises a condition. Not hygienic at all, only used right below here.

Package

cl-postgres.

Source

protocol.lisp.


6.2.4 Ordinary functions

Function: %build-foreign-reference (target on-delete on-update match)
Package

s-sql.

Source

s-sql.lisp.

Function: %eval (code)
Package

postmodern.

Source

table.lisp.

Function: add-table-definition (symbol func)
Package

postmodern.

Source

deftable.lisp.

Function: aggregated-gen-final-client-message (user-name client-nonce server-message password &key response-type salt-type)

Takes a user-name, a client-nonce, a server response and a password. If the server response is not in the form of an array of bytes which are encoded in base64, the response type must be specified as either :base64-string or :utf8-string. The client-nonce should be a normal utf8 string.
It returns the server-response as a normal string, the server-provided-salt as a normal string, and the server-iterations as an integer.

The allowed response-types are :base64-string, :base64-usb8-array and :utf8-string.

Package

cl-postgres.

Source

scram.lisp.

Function: alter-table-column (column-name args)

Generates the sql string for the portion of altering a column.

Package

s-sql.

Source

s-sql.lisp.

Function: array-hash-row-reader (g0 fields)
Package

postmodern.

Source

query.lisp.

Function: authenticate (socket conn)

Try to initiate a connection. Caller should close the socket if this raises a condition.

Package

cl-postgres.

Source

protocol.lisp.

Function: bad-char-error (message &key value normalization-form)
Package

cl-postgres.

Source

saslprep.lisp.

Function: begin-transaction (&optional isolation-level)
Package

postmodern.

Source

transaction.lisp.

Function: bind-message (socket name result-formats parameters binary-parameters-p)

Bind a prepared statement, ask for the given formats, and pass the given parameters, that can be either string or byte vector.
(vector (unsigned-byte 8)) parameters will be sent as binary data, useful for binding data for binary long object columns.

Package

cl-postgres.

Source

messages.lisp.

Function: build-dao-methods (class)

Synthesise a number of methods for a newly defined DAO class. (Done this way because some of them are not defined in every situation, and each of them needs to close over some pre-computed values. Notes for future maintenance: Fields are the slot names in a dao class. Field-sql-name returns the col-name for the postgresql table, which may or may not be the same as the slot names in the class and also may have no relation to the initarg or accessor or reader.)

Package

postmodern.

Source

table.lisp.

Function: build-row-reader (function-form fields body)

Helper for the following two macros.

Package

cl-postgres.

Source

protocol.lisp.

Function: bytes-to-hex-string (bytes)

Convert an array of 0-255 numbers into the corresponding string of (lowercase) hex codes.

Package

cl-postgres.

Source

messages.lisp.

Function: call-with-ensured-transaction (thunk &optional isolation-level)
Package

postmodern.

Source

transaction.lisp.

Function: call-with-logical-transaction (name body &optional isolation-level)
Package

postmodern.

Source

transaction.lisp.

Function: call-with-savepoint (name body)
Package

postmodern.

Source

transaction.lisp.

Function: call-with-transaction (body &optional isolation-level)
Package

postmodern.

Source

transaction.lisp.

Function: camel-case-split (string)

Assume STRING is in camel case, and split it into largest possible “homogenous” parts. A homogenous part consists either a) of upper-case alphabetic chars; or b) of lower-case alphabetic chars with an optional initial upper-case; or c) of decimal digits; or d) of a single non-alphanumeric char. The return value is a list of
pairs (CATEGORY . PART) where CATEGORY is one of the keywords :UPPER, :UPPER-1, :LOWER, :NUMERIC, :MIXED, and PART is a substring of STRING.

Package

postmodern.

Source

json-encoder.lisp.

Function: camel-case-to-lisp (string)

Take a camel-case string and convert it into a string with Lisp-style hyphenation.

Package

postmodern.

Source

json-encoder.lisp.

Function: camel-case-transform (parts &optional cat-before)

Take a list of PARTS (as returned by CAMEL-CASE-SPLIT) and transform it into a string with Lisp-style hyphenation, assuming that some initial portion of it does not contain :UPPER parts.

Package

postmodern.

Source

json-encoder.lisp.

Function: camel-case-transform-all-caps (parts &optional cat-before from-numeric)

Take a list of PARTS (as returned by CAMEL-CASE-SPLIT) and transform it into a string with Lisp-style hyphenation, assuming that some initial portion of it does not contain :MIXED parts.

Package

postmodern.

Source

json-encoder.lisp.

Function: char-mapped-to-nothing-p (chr)

Returns t if the character should be mapped to nothing per RFC 3454 Table B.1 and RFC 4013

Package

cl-postgres.

Source

saslprep.lisp.

Function: char-mapped-to-space-p (chr)

If character is mapped to space per RFC 3454 Table C.1.2 and RFC 4013, then return t, else nil

Package

cl-postgres.

Source

saslprep.lisp.

Function: char-printable-ascii-p (ch)

Returns t if the char is printable ascii.

Package

cl-postgres.

Source

saslprep.lisp.

Function: character-set-exists-p (char-support)

There is no good way that I know to determine the available character sets on a remote server so we just assume any postgresql usable set is available.

Package

postmodern.

Source

util.lisp.

Function: close-prepared-message (g0 name)
Package

cl-postgres.

Source

messages.lisp.

Function: code-char-0-p (chr)

Returns t if character has char-code 0 (generally #Nul)

Package

postmodern.

Source

util.lisp.

Function: code-point-printable-ascii-p (int)

Returns t if the int is a printable ascii code-point.

Package

cl-postgres.

Source

saslprep.lisp.

Function: collation-exists-p (collation)

This function does require the parameter to be a string and properly upper and lower cased.

Package

postmodern.

Source

util.lisp.

Function: collect-export-functions (class)

Collects the export functions for a class (if any) and returns a list of lists of form (sql_name_of_field . export-function)

Package

postmodern.

Source

table.lisp.

Function: collect-import-functions (class)

Collects the import functions for a class (if any) and returns a list of lists of form (sql_name_of_field . export-function)

Package

postmodern.

Source

table.lisp.

Function: column-row-reader (g0 fields)
Package

postmodern.

Source

query.lisp.

Reader: comment-parser-buffer (instance)
Writer: (setf comment-parser-buffer) (instance)
Package

postmodern.

Source

execute-file.lisp.

Target Slot

buffer.

Function: comment-parser-p (object)
Package

postmodern.

Source

execute-file.lisp.

Reader: comment-parser-state (instance)
Writer: (setf comment-parser-state) (instance)
Package

postmodern.

Source

execute-file.lisp.

Target Slot

state.

Reader: comment-parser-stream (instance)
Writer: (setf comment-parser-stream) (instance)
Package

postmodern.

Source

execute-file.lisp.

Target Slot

stream.

Function: connection-pid (connection)

Retrieves a list consisting of the pid and the secret-key from the connection, not from the database itself. These are needed for cancelling connections and error processing with respect to prepared statements.

Package

cl-postgres.

Source

public.lisp.

Function: cons-to-sql-name-strings (item)

Takes a list of two items and returns a single string separated by a space. The items will be converted to sql compatible namestrings.

Package

s-sql.

Source

s-sql.lisp.

Function: copier-write-sequence (s vector)
Package

cl-postgres.

Source

bulk-copy.lisp.

Function: copier-write-value (s val)
Package

cl-postgres.

Source

bulk-copy.lisp.

Function: copy-comment-parser (instance)
Package

postmodern.

Source

execute-file.lisp.

Function: copy-data-message (socket data)
Package

cl-postgres.

Source

messages.lisp.

Function: copy-done-message (g0)
Package

cl-postgres.

Source

messages.lisp.

Function: copy-fail-message (g0 reason)
Package

cl-postgres.

Source

messages.lisp.

Function: copy-from-csv (tablename filename &key delimiter header-p database user password host port)

Runs the psql copy command against a file. Assuming you are already connected to the desired database and the *database* global variable is set to that, then the mMinimum parameters required are the postgresql table-name and the file name including its absolute path. The delimiter parameter should be either ’comma or ’tab. Set the header-p parameter t if the first line of the csv file is a header that should not get imported into the database table. The table name can be either a string or quoted symbol.

Package

postmodern.

Source

util.lisp.

Function: copy-parser (instance)
Package

postmodern.

Source

execute-file.lisp.

Function: copy-query (self)
Package

cl-postgres.

Source

bulk-copy.lisp.

Function: create-role-helper (role-type name password &key schema tables databases)

Create-role-helper creates a user, then calls grant-role-permission

A :superuser can create databases, roles, replication, etc.
An :admin user can edit existing data, insert new data and create new tables in the specified databases/schemas/tables.
An :editor user can update fields or insert new records but cannot create new tables in the specified tables or databases.
A :readonly user can only read existing data in the specified schemas, tables or databases. Schema, tables or databases can be :all or a list of schemas, tables or databases to be granted permission.

Granting :all provides access to all future items of that type as well.

Note that the schema and table rights and revocations granted are limited to the connected database at the time of execution of this function.

Package

postmodern.

Source

roles.lisp.

Function: dao-column-fields (class)

Returns a list of symbols of the names of the slots in a class.

Package

postmodern.

Source

table.lisp.

Function: dao-column-slots (class)

Enumerate the slots in a class that refer to table rows.

Package

postmodern.

Source

table.lisp.

Function: dao-from-fields (class column-map query-fields result-next-field-generator-fn)
Package

postmodern.

Source

table.lisp.

Function: dao-row-reader (class)

Defines a row-reader for objects of a given class. Note that query fields are the Postgresql column names, not the dao slot names.

Package

postmodern.

Source

table.lisp.

Function: dao-spec-for-format (format)
Package

postmodern.

Source

query.lisp.

Function: dao-superclasses (class)

Build a list of superclasses of a given class that are DAO classes.

Package

postmodern.

Source

table.lisp.

Function: database-parameters-to-list (databases)

Returns a list of databases where the parameter may be a list of databases, a single string name or :current, :all or "all".

Package

postmodern.

Source

roles.lisp.

Function: default-date-reader (days-since-2000)
Package

cl-postgres.

Source

interpret.lisp.

Function: default-interval-reader (months days useconds)
Package

cl-postgres.

Source

interpret.lisp.

Function: default-time-reader (usecs)
Package

cl-postgres.

Source

interpret.lisp.

Function: default-timestamp-reader (useconds-since-2000)
Package

cl-postgres.

Source

interpret.lisp.

Function: dequote (val)

Helper function for macros which look for ’something but that has been converted to (quote something).

Package

s-sql.

Source

s-sql.lisp.

Function: describe-prepared-message (g0 name)
Package

cl-postgres.

Source

messages.lisp.

Function: disallowed-tag-char-p (char)

Returns t if char is a character we are going to disallow in a tag

Package

postmodern.

Source

execute-file.lisp.

Function: dissect-type (type)

Return the type and whether it may be NULL. TYPE may be a list starting with ’or’ containing two, and only two, potential types to test.

Package

s-sql.

Source

s-sql.lisp.

Function: do-with-schema (schema thunk &key strict if-not-exist drop-after)
Package

postmodern.

Source

namespace.lisp.

Function: enc-byte-length (sequence)
Package

cl-postgres.

Source

strings-utf-8.lisp.

Function: enc-read-string (input &key null-terminated byte-length)
Package

cl-postgres.

Source

strings-utf-8.lisp.

Function: enc-string-bytes (string &key null-terminate)
Package

cl-postgres.

Source

strings-utf-8.lisp.

Function: enc-write-string (string output &key null-terminate)
Package

cl-postgres.

Source

strings-utf-8.lisp.

Function: encode-array-member (object &optional stream)

Encode OBJECT as the next Member of the innermost JSON Array opened with WITH-ARRAY in the dynamic context. OBJECT is encoded using the ENCODE-JSON generic function, so it must be of a type for which an ENCODE-JSON method is defined.

Package

postmodern.

Source

json-encoder.lisp.

Function: encode-json-alist (alist &optional stream)

Write the JSON representation (Object) of ALIST to STREAM (or to *JSON-OUTPUT*). Return NIL.

Package

postmodern.

Source

json-encoder.lisp.

Function: encode-json-alist-to-string (alist)

Return the JSON representation (Object) of ALIST as a string.

Package

postmodern.

Source

json-encoder.lisp.

Function: encode-json-list-explicit-encoder (s stream)
Package

postmodern.

Source

json-encoder.lisp.

Function: encode-json-list-guessing-encoder (s stream)

Write the JSON representation of the list S to STREAM (or to *JSON-OUTPUT*). If S is not encodable as a JSON Array, try to encode it as an Object (per ENCODE-JSON-ALIST).

Package

postmodern.

Source

json-encoder.lisp.

Function: encode-json-plist (plist &optional stream)

Write the JSON representation (Object) of PLIST to STREAM (or to *JSON-OUTPUT*). Return NIL.

Package

postmodern.

Source

json-encoder.lisp.

Function: encode-json-plist-to-string (plist)

Return the JSON representation (Object) of PLIST as a string.

Package

postmodern.

Source

json-encoder.lisp.

Function: encode-object-member (key value &optional stream)

Encode KEY and VALUE as a Member pair of the innermost JSON Object opened with WITH-OBJECT in the dynamic context. KEY and VALUE are encoded using the ENCODE-JSON generic function, so they both must be of a type for which an ENCODE-JSON method is defined. If KEY does not encode to a String, its JSON representation (as a string) is encoded over again.

Package

postmodern.

Source

json-encoder.lisp.

Function: ensure-connection (conn &optional connection-attempts)

Used to make sure a connection object is connected before doing anything with it.

Package

cl-postgres.

Source

public.lisp.

Function: ensure-prepared (connection id query &optional overwrite params)

Make sure a statement has been prepared for this connection. If overwrite is set to t (not the default), it will overwrite the existing query of the same name. Reminder that the meta info is a list of query, params.

Package

postmodern.

Source

prepare.lisp.

Function: ensure-socket-is-closed (socket &key abort)
Package

cl-postgres.

Source

communicate.lisp.

Function: escape-bytes (bytes)

Escape an array of octets in PostgreSQL’s horribly inefficient textual format for binary data.

Package

cl-postgres.

Source

sql-string.lisp.

Function: escape-sql-expression (expr)

Try to escape an expression at compile-time, if not possible, delay to runtime. Used to create stored procedures.

Package

s-sql.

Source

s-sql.lisp.

Function: expand-composite-table-name (frm)

Helper function for building a composite table name

Package

s-sql.

Source

s-sql.lisp.

Function: expand-create-index (name args)

Available parameters - in order after name - are :concurrently, :on, :using, :fields and :where.The advantage to using the keyword :concurrently is that writes to the table from other sessions are not locked out while the index is is built. The disadvantage is that the table will need to be scanned twice. Everything is a trade-off.

Package

s-sql.

Source

s-sql.lisp.

Function: expand-extended-table-constraint (option args)

Process table constraints that follow the closing parentheses in the table definition.

Package

s-sql.

Source

s-sql.lisp.

Function: expand-foreign-on* (action)
Package

s-sql.

Source

s-sql.lisp.

Function: expand-identity (keywd)
Package

s-sql.

Source

s-sql.lisp.

Function: expand-interval (option)

Provide interval limit options

Package

s-sql.

Source

s-sql.lisp.

Function: expand-joins (args)

Helper for the select operator. Turns the part following :from into the proper SQL syntax for joining tables.

Package

s-sql.

Source

s-sql.lisp.

Function: expand-rows (rows length)
Package

s-sql.

Source

s-sql.lisp.

Function: expand-table-column (column-name args)
Package

s-sql.

Source

s-sql.lisp.

Function: expand-table-constraint (option args)

Process table constraints that precede the closing parentheses in the table definition for the base level create table. The difference between this and the expand-table-constraint-sok function is the parameter list signature. This expects to receive no sublists. The expand-table-constraint-sok function expects to list of sublists. This is done to maintain backwards compatibility and most general users do not need the extended version.

Foreign keys have defaults on-delete restrict, on-update restrict, and match simple. If you want to change those defaults, you need to specify them in that order.

Per the postgresql documentation at https://www.postgresql.org/docs/10/static/sql-createtable.html

A value inserted into the referencing column(s) is matched against the values of the referenced table and referenced columns using the given match type. There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE (which is the default). MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null; if they are all null, the row is not required to have a match in the referenced table. MATCH SIMPLE allows any of the foreign key columns to be null; if any of them are null, the row is not required to have a match in the referenced table. MATCH PARTIAL is not yet implemented. (Of course, NOT NULL constraints can be applied to the referencing column(s) to prevent these cases from arising.)

Package

s-sql.

Source

s-sql.lisp.

Function: expand-table-constraint-sok (args)

Expand-table-constraint for the create-extended-table sql-op. The difference between the two is the parameter list signature. This expects a list of sublists. The regular expand-table-constraint expects to receive no sublists.
DOES NOT IMPLEMENT POSTGRESQL FUNCTION EXCLUDE.

Package

s-sql.

Source

s-sql.lisp.

Function: expand-table-name (name &optional tableset)

Note: temporary tables are unlogged tables. Having both :temp and :unlogged would be redundant.

Package

s-sql.

Source

s-sql.lisp.

Function: field-name-to-slot-name (class field-name)

Takes a Postgresql column name and tries to match it to a dao slot name symbol. This is trying to deal with the hyphens and underscores problem where Postgresql table field names cannot use hyphens but that is normal CL practice. The slot name symbol will be the full package::slot-name. field-name must be a string.

Package

postmodern.

Source

table.lisp.

Function: find-export-function (class column-name)

Returns the export function, if any, for a class and a column-name. Column name must be a symbol

Package

postmodern.

Source

table.lisp.

Function: find-import-function (class column-name)

Returns the import function, if any, for a class and a column-name. Column name must be a symbol

Package

postmodern.

Source

table.lisp.

Function: find-included-filename (meta-cmd new-filename base-filename)

Create full pathname if included using a ir metacommand or include_relative.

Package

postmodern.

Source

execute-file.lisp.

Function: find-postgresql-prepared-query (connection name)

Returns a list of (name, query, parameters) for a named prepared query.
Note the somewhat similar Postmodern version (find-postgresql-prepared-statement name) only returns the query, not the parameters or name.

Package

cl-postgres.

Source

public.lisp.

Function: find-postgresql-prepared-statement-by-query (query)

Returns the name of prepared statement (if any) postgresql has for this session that matches the query .

Package

postmodern.

Source

prepare.lisp.

Function: flat-table-name (&optional table)
Package

postmodern.

Source

deftable.lisp.

Function: flush-message (g0)
Package

cl-postgres.

Source

messages.lisp.

Function: for-update/share (share-or-update form &rest args)
Package

s-sql.

Source

s-sql.lisp.

Function: formats-to-bytes (formats)

Formats have to be passed as arrays of 2-byte integers, with 1 indicating binary and 0 indicating plain text.

Package

cl-postgres.

Source

messages.lisp.

Function: gen-auth-message (client-initial-response server-response final-message-part1)

Currently assumes all parameters are normal strings

Package

cl-postgres.

Source

scram.lisp.

Function: gen-client-initial-response (user-name client-nonce)
Package

cl-postgres.

Source

scram.lisp.

Function: gen-client-key (salted-password &optional message sha-method)

Returns a byte array

Package

cl-postgres.

Source

scram.lisp.

Function: gen-client-nonce (&optional nonce-length)

Generate a random alphanumeric nonce with a default length of 32.

Package

cl-postgres.

Source

scram.lisp.

Function: gen-client-proof (client-key client-signature)

The eventual client-proof needs to be base64 encoded

Package

cl-postgres.

So