The postmodern Reference Manual

Table of Contents

Next: , Previous: , Up: (dir)   [Contents][Index]

The postmodern Reference Manual

This is the postmodern Reference Manual, version 1.32.9, generated automatically by Declt version 3.0 "Montgomery Scott" on Mon Apr 19 17:26:02 2021 GMT+0.


Next: , Previous: , Up: Top   [Contents][Index]

1 Introduction

Postmodern

A Common Lisp PostgreSQL programming interface

Version 1.38

Postmodern is a Common Lisp library for interacting with PostgreSQL databases. It is under active development. Features are:

The biggest differences between this library and CLSQL/CommonSQL or cl-dbi are that Postmodern has no intention of being portable across different SQL implementations (it embraces non-standard PostgreSQL features), and approaches extensions like lispy SQL and database access objects in a quite different way. This library was written because the CLSQL approach did not really work for me. Your mileage may vary.

Contents


Dependencies


The library depends on usocket (except on SBCL and ACL, where the built-in socket library is used), md5, closer-mop, bordeaux-threads if you want thread-safe connection pools, and CL+SSL when SSL connections are needed. As of version 1.3 it also depends on ironclad, base64 and uax-15 because of the requirement to support scram-sha-256 authentication.

Postmodern itself is split into four different packages, some of which can be used independently.

Simple-date is a very basic implementation of date and time objects, used to support storing and retrieving time-related SQL types. It is not loaded by default and you can use local-time (which has support for timezones) instead.

CL-postgres is the low-level library used for interfacing with a PostgreSQL server over a socket.

S-SQL is used to compile s-expressions to strings of SQL code, escaping any Lisp values inside, and doing as much as possible of the work at compile time.

Finally, Postmodern itself is a wrapper around these packages and provides higher level functions, a very simple data access object that can be mapped directly to database tables and some convient utilities. It then tries to put all these things together into a convenient programming interface.

License


Postmodern is released under a zlib-style license. Which approximately means you can use the code in whatever way you like, except for passing it off as your own or releasing a modified version without indication that it is not the original.

The functions execute-file.lisp were ported from [[https://github.com/dimitri/pgloader][pgloader]] with grateful thanks to Dimitri Fontaine and are released under a BSD-3 license.

Download and installation


We suggest using quicklisp for installation.

A git repository with the most recent changes can be viewed or checked out at https://github.com/marijnh/Postmodern

Quickstart


This quickstart is intended to give you a feel of the way coding with Postmodern works. Further details about the workings of the library can be found in the reference manual.

Assuming you have already installed it, first load and use the system:

(ql:quickload :postmodern)
(use-package :postmodern)

If you have a PostgreSQL server running on localhost, with a database called 'testdb' on it, which is accessible for user 'foucault' with password 'surveiller', there are two basic ways to connect to a database. If your role/application/database(s) looks like a 1:1 relationship, you can connect like this:

(connect-toplevel "testdb" "foucault" "surveiller" "localhost")

Which will establish a connection to be used by all code, except for that wrapped in a with-connection form, which takes the same arguments but only establishes the connection within that lexical scope.

Connect-toplevel will maintain a single connection for the life of the session.

If the Postgresql server is running on a port other than 5432, you would also pass the appropriate keyword port parameter. E.g.:

(connect-toplevel "testdb" "foucault" "surveiller" "localhost" :port 5434)

Ssl connections would similarly use the keyword parameter :use-ssl and pass :yes, :no or :try

If you have multiple roles connecting to one or more databases, i.e. 1:many or many:1, (in other words, changing connections) then with-connection form which establishes a connection with a lexical scope is more appropriate.

(with-connection '("testdb" "foucault" "surveiller" "localhost")
  ...)

For example, if you are creating a database, you need to have established a connection to a currently existing database (typically "postgres"). Assuming the foucault role is a superuser and you want to stay in a development connection with your new database afterwards, you would first use with-connection to connect to postgres, create the database and then switch to connect-toplevel for development ease.

(with-connection '("postgres" "foucault" "surveiller" "localhost")
  (create-database 'testdb :limit-public-access t
                     :comment "This database is for testing silly theories"))

(connect-toplevel "testdb" "foucault" "surveiller" "localhost")

Note: (create-database) functionality is new to postmodern v. 1.32. Setting the :limit-public-access parameter to t will block connections to that database from anyone who you have not explicitly given permission (except other superusers).

A word about Postgresql connections. Postgresql connections are not lightweight threads. They actually consume about 10 MB of memory per connection and Postgresql can be tuned to limit the number of connections allowed at any one time. In addition, any connections which require security (ssl or scram authentication) will take additiona time and create more overhead.

If you have an application like a web app which will make many connections, you also generally do not want to create and drop connections for every query. The usual solution is to use connection pools so that the application is grabbing an already existing connection and returning it to the pool when finished, saving connection time and memory.

To use postmodern's simple connection pooler, the with-connection call would look like:

(with-connection '("testdb" "foucault" "surveiller" "localhost" :pooled-p t)
  ...)

The maximum number of connections in the pool is set in the special variable *max-pool-size*, which defaults to nil (no maximum).

Now for a basic sanity test which does not need a database connection at all:

(query "select 22, 'Folie et déraison', 4.5")
;; => ((22 "Folie et déraison" 9/2))

That should work. query is the basic way to send queries to the database. The same query can be expressed like this:

(query (:select 22 "Folie et déraison" 4.5))
;; => ((22 "Folie et déraison" 9/2))

In many contexts, query strings and lists starting with keywords can be used interchangeably. The lists will be compiled to SQL. The S-SQL manual describes the syntax used by these expressions. Lisp values occurring in them are automatically escaped. In the above query, only constant values are used, but it is possible to transparently use run-time values as well:

(defun database-powered-addition (a b)
  (query (:select (:+ a b)) :single))
(database-powered-addition 1030 204)
;; => 1234

That last argument, :single, indicates that we want the result not as a list of lists (for the result rows), but as a single value, since we know that we are only selecting one value. Some other options are :rows, :row, :column, :alists, and :none. Their precise effect is documented in the reference manual.

You do not have to pull in the whole result of a query at once, you can also iterate over it with the doquery macro:

(doquery (:select 'x 'y :from 'some-imaginary-table) (x y)
  (format t "On this row, x = ~A and y = ~A.~%" x y))

You can work directly with the database or you can use a simple database-access-class (aka dao) which would cover all the columns in a row. This is what a database-access class looks like:

(defclass country ()
  ((name :col-type string :initarg :name
         :reader country-name)
   (inhabitants :col-type integer :initarg :inhabitants
                :accessor country-inhabitants)
   (sovereign :col-type (or db-null string) :initarg :sovereign
              :accessor country-sovereign))
  (:metaclass dao-class)
  (:keys name))

The above defines a class that can be used to handle records in a table with three columns: name, inhabitants and sovereign. The :keys parameter specifies which column(s) are used for the primary key. Once you have created the class, you can return an instance of the country class by calling

(get-dao 'country "Croatia")

You can also define classes that use multiple columns in the primary key:

(defclass points ()
  ((x :col-type integer :initarg :x
      :reader point-x)
   (y :col-type integer :initarg :y
      :reader point-y)
   (value :col-type integer :initarg :value
          :accessor value))
  (:metaclass dao-class)
  (:keys x y))

In this case, retrieving a points record would look like the following where 12 and 34 would be the values you are looking to find in the x column and y column respectively.:

(get-dao 'points 12 34)

Consider a slightly more complicated version of country:

(defclass country ()
  ((id :col-type integer :col-identity t :accessor id)
   (name :col-type string :col-unique t :check (:<> 'name "")
         :initarg :name :reader country-name)
   (inhabitants :col-type integer :initarg :inhabitants
                :accessor country-inhabitants)
   (sovereign :col-type (or db-null string) :initarg :sovereign
              :accessor country-sovereign)
   (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 and the check will ensure that the database refuses to accept an empty string as the name.

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

Table Creation

You can create tables directly without the need to define a class, and in more complicated cases, you may need to use the s-sql :create-table operator or plain vanilla sql. Staying with examples that will match our slightly more complicated dao-class above (but ignoring the fact that the references parameter would actually require us to create the regions table first) and using s-sql rather than plain vanilla sql would be the following:

(query (:create-table 'countries
      ((id :type integer  :primary-key t :identity-always t)
       (name :type string :unique t :check (:<> 'name ""))
       (inhabitants :type integer)
       (sovereign :type (or db-null string))
       (region-id :type integer :references ((regions id))))))

Restated using vanilla sql:

(query "CREATE TABLE countries (
         id INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
         name TEXT NOT NULL UNIQUE CHECK (NAME <> E''),
         inhabitants INTEGER NOT NULL,
         sovereign TEXT,
         region_id INTEGER NOT NULL REFERENCES regions(id)
           MATCH SIMPLE ON DELETE RESTRICT ON UPDATE RESTRICT)")

Let's look at a slightly different example:

(query (:create-table so-items
         ((item-id :type integer)
          (so-id :type (or integer db-null) :references ((so-headers id)))
          (product-id :type (or integer db-null))
          (qty :type (or integer db-null))
          (net-price :type (or numeric db-null)))
         (:primary-key item-id so-id)))

Restated using plain sql:

(query "CREATE TABLE so_items (
 item_id INTEGER NOT NULL,
 so_id INTEGER REFERENCES so_headers(id)
               MATCH SIMPLE ON DELETE RESTRICT ON UPDATE RESTRICT,
 product_id INTEGER,
 qty INTEGER,
 net_price NUMERIC,
 PRIMARY KEY (item_id, so_id)
 );"
)

In the above case, the new table's name will be so_items because sql does not allow hyphens and plain vanilla sql will require that. Postmodern will generally allow you to use the quoted symbol 'so-items. This is also true for all the column names. The column item-id is an integer and cannot be null. The column so-id is also an integer, but is allowed to be null and is a foreign key to the id field in the so-headers table so-headers. The primary key is actually a composite of item-id and so-id. (If we wanted the primary key to be just item-id, we could have specified that in the form defining item-id.)

You can also use a previously defined dao to create a table as well using the dao-table-definition function which generates the plain vanilla sql for creating plain vanilla sql for creating a table described above. Using the slightly more complicated version of the country dao above:

(dao-table-definition 'country)


;; => "CREATE TABLE countries (
;;       id INTEGER NOT NULL PRIMARY KEY generated always as identity,
;;       name TEXT NOT NULL UNIQUE,
;;       inhabitants INTEGER NOT NULL,
;;       sovereign TEXT DEFAULT NULL,
;;       region_id INTEGER NOT NULL REFERENCES regions(id)
;;         MATCH SIMPLE ON DELETE RESTRICT ON UPDATE RESTRICT)

(execute (dao-table-definition 'country))

This defines our table in the database. execute works like query, but does not expect any results back.

See Introduction to Multi-table dao class objects in the postmodern.org or postmodern.html manual for a further discussion of multi-table use of daos.

Inserting Data

Similarly to table creation, you can insert data using the s-sql wrapper, plain vanilla sql or daos. Because we have not created a regions table, we are just going to use the simple version of country without the region-id.

The s-sql approach would be:

(query (:insert-into 'country :set 'name "The Netherlands"
                                   'inhabitants 16800000
                                   'sovereign "Willem-Alexander"))

(query (:insert-into 'country :set 'name "Croatia"
                                   'inhabitants 4400000))

You could also insert multiple rows at a time but that requires the same columns for each row:

(query (:insert-rows-into 'country :columns 'name 'inhabitants 'sovereign
                                   :values '(("The Netherlands" 16800000 "Willem-Alexander")
                                             ("Croatia" 4400000 :null))))

The sql approach would be:

(query "insert into country (name, inhabitants, sovereign)
                            values ('The Netherlands', 16800000, 'Willem-Alexander')")

(query "insert into country (name, inhabitants)
                            values ('Croatia', 4400000)")

The multiple row sql approach would be:

(query "insert into country (name, inhabitants, sovereign)
                            values
                              ('The Netherlands', 16800000, 'Willem-Alexander'),
                              ('Croatia', 4400000, NULL)")

Using dao classes would look like:

(insert-dao (make-instance 'country :name "The Netherlands"
                                    :inhabitants 16800000
                                    :sovereign "Willem-Alexander"))
(insert-dao (make-instance 'country :name "Croatia"
                                    :inhabitants 4400000))

Postmodern does not yet have an insert-daos (plural) function.

Staying with the dao class approach, to update Croatia's population, we could do this:

(let ((croatia (get-dao 'country "Croatia")))
  (setf (country-inhabitants croatia) 4500000)
  (update-dao croatia))
(query (:select '* :from 'country))
;; => (("The Netherlands" 16800000 "Willem-Alexander")
;;     ("Croatia" 4500000 :NULL))

Next, to demonstrate a bit more of the S-SQL syntax, here is the query the utility function list-tables uses to get a list of the tables in a database:

(sql (:select 'relname :from 'pg-catalog.pg-class
      :inner-join 'pg-catalog.pg-namespace :on (:= 'relnamespace 'pg-namespace.oid)
      :where (:and (:= 'relkind "r")
                   (:not-in 'nspname (:set "pg_catalog" "pg_toast"))
                   (:pg-catalog.pg-table-is-visible 'pg-class.oid))))

;; => "(SELECT relname FROM pg_catalog.pg_class
;;      INNER JOIN pg_catalog.pg_namespace ON (relnamespace = pg_namespace.oid)
;;      WHERE ((relkind = 'r') and (nspname NOT IN ('pg_catalog', 'pg_toast'))
;;             and pg_catalog.pg_table_is_visible(pg_class.oid)))"

sql is a macro that will simply compile a query, it can be useful for seeing how your queries are expanded or if you want to do something unexpected with them.

As you can see, lists starting with keywords are used to express SQL commands and operators (lists starting with something else will be evaluated and then inserted into the query). Quoted symbols name columns or tables (keywords can also be used but might introduce ambiguities). The syntax supports subqueries, multiple joins, stored procedures, etc. See the S-SQL reference manual for a complete treatment.

Finally, here is an example of the use of prepared statements:

(defprepared sovereign-of
  (:select 'sovereign :from 'country :where (:= 'name '$1))
  :single!)
(sovereign-of "The Netherlands")
;; => "Willem-Alexander"

The defprepared macro creates a function that takes the same amount of arguments as there are $X placeholders in the given query. The query will only be parsed and planned once (per database connection), which can be faster, especially for complex queries.

(disconnect-toplevel)

Authentication

Postmodern can use either md5 or scram-sha-256 authentication. Scram-sha-256 authentication is obviously more secure, but slower than md5, so take that into account if you are planning on opening and closing many connections without using a connection pooling setup..

Other authentication methods have not been tested. Please let us know if there is a authentication method that you believe should be considered.

Reference


The reference manuals for the different components of Postmodern are kept in separate files. For using the library in the most straightforward way, you only really need to read the Postmodern reference and glance over the S-SQL reference. The simple-date reference explains the time-related data types included in Postmodern, and the CL-postgres reference might be useful if you just want a low-level library for talking to a PostgreSQL server.

Data Types


For a short comparison of lisp and Postgresql data types (date and time datatypes are described in the next section)

| Lisp type | SQL type | Description | | ------------- | ---------------- | ---------------------------------------------------------- | | integer | smallint | -32,768 to +32,768 2-byte storage | | integer | integer | -2147483648 to +2147483647 integer, 4-byte storage | | integer | bigint | -9223372036854775808 to 9223372036854775807 8-byte storage | | (numeric X Y) | numeric(X, Y) | user specified. See below | | float, real | real | float, 6 decimal digit precision 4-byte storage | | double-float | double-precision | double float 15 decimal digit precision 8-byte storage | | string, text | text | variable length string, no limit specified | | string | char(X) | char(length), blank-padded string, fixed storage length | | string | varchar(X) | varchar(length), non-blank-padded string, variable storage | | boolean | boolean | boolean, 'true'/'false', 1 byte | | bytea | bytea | binary strings allowing non-printable octets | | date | date | date range: 4713 BC to 5874897 AD | | interval | interval | time intervals | | array | array | See discussion at Array-Notes.html|

Numeric and decimal are variable storage size numbers with user specified precision. Up to 131072 digits before the decimal point; up to 16383 digits after the decimal point. The syntax is numeric(precision, scale). Numeric columns with a specified scale will coerce input values to that scale. For more detail, see https://www.postgresql.org/docs/current/datatype-numeric.html

| PG Type | Sample Postmodern Return Value | Lisp Type (per sbcl) | | --------------- | --------------------------------------------------------------------------- | ------------------------------------ | | boolean | T | BOOLEAN | | boolean | NIL (Note: within Postgresql this will show 'f') | BOOLEAN | | int2 | 273 | (INTEGER 0 4611686018427387903) | | int4 | 2 | (INTEGER 0 4611686018427387903) | | char | A | (VECTOR CHARACTER 64) | | varchar | id&wl;19 | (VECTOR CHARACTER 64) | | numeric | 78239/100 | RATIO | | json | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}} | (VECTOR CHARACTER 64) | | jsonb | {"title": "Sleeping Beauties", "genres": ["Fiction", "Thriller", "Horror"]} | (VECTOR CHARACTER 128) | | float | 782.31 | SINGLE-FLOAT | | point | (0.0d0 0.0d0) | CONS | | lseg | ((-1.0d0 0.0d0) (2.0d0 4.0d0)) | CONS | | path | ((1,0),(2,4)) | (VECTOR CHARACTER 64) | | box | ((1.0d0 1.0d0) (0.0d0 0.0d0)) | CONS | | polygon | ((21,0),(2,4)) | (VECTOR CHARACTER 64) | | line | {2,-1,0} | (VECTOR CHARACTER 64) | | double_precision | 2.38921379231d8 | DOUBLE-FLOAT | | double_float | 2.3892137923231d8 | DOUBLE-FLOAT | | circle | <(0,0),2> | (VECTOR CHARACTER 64) | | cidr | 100.24.10.0/24 | (VECTOR CHARACTER 64) | | inet | 100.24.10.0/24 | (VECTOR CHARACTER 64) | | interval | # | INTERVAL | | bit | #*1 | (SIMPLE-BIT-VECTOR 1) | | int4range | [11,24) | (VECTOR CHARACTER 64) | | uuid | 40e6215d-b5c6-4896-987c-f30f3678f608 | (VECTOR CHARACTER 64) | | text_array | #(text one text two text three) | (SIMPLE-VECTOR 3) | | integer_array | #(3 5 7 8) | (SIMPLE-VECTOR 4) | | bytea | #(222 173 190 239) | (SIMPLE-ARRAY (UNSIGNED-BYTE 8) (4)) | | text | Lorem ipsum dolor sit amet, consectetur adipiscing elit | (VECTOR CHARACTER 64) | | enum_mood | happy (Note: enum_mood was defined as 'sad','ok' or 'happy') | (VECTOR CHARACTER 64) |


Arrays

See array-notes.html


Timezones

It is important to understand how postgresql (not postmodern) handles timestamps and timestamps with time zones. Postgresql keeps everything in UTC, it does not store a timezone even in a timezone aware column. If you use a timestamp with timezone column, postgresql will calculate the UTC time and will normalize the timestamp data to UTC. When you later select the record, postgresql will look at the timezone for the postgresql session, retrieve the data and then provide the data recalculated from UTC to the timezone for that postgresql session. There is a good writeup of timezones at http://blog.untrod.com/2016/08/actually-understanding-timezones-in-postgresql.html and http://phili.pe/posts/timestamps-and-time-zones-in-postgresql/.

Without simple-date or local-time properly loaded, sample date and time data from postgresql will look like:

| PG Type | Return Value | Lisp Type | | ----------------------------- | -------------------------------- | -------------------- | | date | #<DATE 16-05-2020> | DATE | | time_without_timezone | #<TIME-OF-DAY 09:47:09.926531> | TIME-OF-DAY | | time_with_timezone | 09:47:16.510459-04 | (VECTOR CHARACTER 64) | | timestamp_without_timezone | #<TIMESTAMP 16-05-2020T09:47:33,315> | TIMESTAMP | | timestamp_with_timezone | #<TIMESTAMP 16-05-2020T13:47:27,855> | TIMESTAMP |

The Simple-date add-on library (not enabled by default) provides types (CLOS classes) for dates, timestamps, and intervals similar to the ones SQL databases use, in order to be able to store and read these to and from a database in a straighforward way. A few obvious operations are defined on these types.

To use simple-date with cl-postgres or postmodern, load simple-date-cl-postgres-glue and register suitable SQL readers and writers for the associated database types.

(ql:quickload :simple-date/postgres-glue)

(setf cl-postgres:*sql-readtable*
        (cl-postgres:copy-sql-readtable
         simple-date-cl-postgres-glue:*simple-date-sql-readtable*))

With simple date loaded, the same data will look like this:

| PG Type | Return Value | Lisp Type | | -------------------------- | -------------------------------- | -------------------- | | date | #<DATE 16-05-2020> | DATE | | time_without_timezone | #<TIME-OF-DAY 09:47:09.926531> | TIME-OF-DAY | | time_with_timezone | 09:47:16.510459-04 | (VECTOR CHARACTER 64) | | timestamp_without_timezone | #<TIMESTAMP 16-05-2020T09:47:33,315> | TIMESTAMP | | timestamp_with_timezone | #<TIMESTAMP 16-05-2020T13:47:27,855> | TIMESTAMP |

To get back to the default cl-postgres reader:

(setf cl-postgres:*sql-readtable*
        (cl-postgres:copy-sql-readtable
         cl-postgres::*default-sql-readtable*))

However Simple-date has no concept of time zones. Many users use another library, local-time, which solves the same problem as simple-date, but does understand time zones.

For those who want to use local-time, to enable the local-time reader:

(ql:quickload :cl-postgres+local-time)
(local-time:set-local-time-cl-postgres-readers)

With that set postgresql time datatype returns look like: With local-time loaded and local-time:set-local-time-cl-postgres-readers run, the same sample data looks like:

| PG Type | Return Value | Lisp Type | | ---------------------------- | -------------------------------- | -------------------- | | date | 2020-05-15T20:00:00.000000-04:00 | TIMESTAMP | | time_without_timezone | 2000-03-01T04:47:09.926531-05:00 | TIMESTAMP | | time_with_timezone | 09:47:16.510459-04 | (VECTOR CHARACTER 64) | | timestamp_without_timezone | 2020-05-16T05:47:33.315622-04:00 | TIMESTAMP | | timestamp_with_timezone | 2020-05-16T09:47:27.855146-04:00 | TIMESTAMP |

Portability

The Lisp code in Postmodern is theoretically portable across implementations, and seems to work on all major ones as well as some minor ones such as Genera. It is regularly tested on ccl, sbcl, ecl and cmucl. ABCL currently has issues with utf-8 and :null.

Please let us know if it does not work on the implementation that you normally use. Implementations that do not have meta-object protocol support will not have DAOs, but all other parts of the library should work (all widely used implementations do support this).

The library is not likely to work for PostgreSQL versions older than 8.4. Other features only work in newer Postgresql versions as the features were only introduced in those newer versions.

Reserved Words

It is highly suggested that you do not use words that are reserved by Postgresql as identifiers (e.g. table names, columns). The reserved words are:

"all" "analyse" "analyze" "and" "any" "array" "as" "asc" "asymmetric" "authorization" "between" "binary" "both" "case" "cast" "check" "collate" "column" "concurrently" "constraint" "create" "cross" "current-catalog" "current-date" "current-role" "current-schema" "current-time" "current-timestamp" "current-user" "default" "deferrable" "desc" "distinct" "do" "else" "end" "except" "false" "fetch" "filter" "for" "foreign" "freeze" "from" "full" "grant" "group" "having" "ilike" "in" "initially" "inner" "intersect" "into" "is" "isnull" "join" "lateral" "leading" "left" "like" "limit" "localtime" "localtimestamp" "natural" "new" "not" "notnull" "nowait" "null" "off" "offset" "old" "on" "only" "or" "order" "outer" "overlaps" "placing" "primary" "references" "returning" "right" "select" "session-user" "share" "similar" "some" "symmetric" "table" "then" "to" "trailing" "true" "union" "unique" "user" "using" "variadic" "verbose" "when" "where" "window" "with"

Feature Requests

Postmodern is under active development so issues and feature requests should be flagged on [[https://github.com/marijnh/Postmodern](Postmodern's site on github).

Resources


Running tests


Postmodern uses FiveAM for testing. The different component systems of Postmodern have tests defined in corresponding test systems, each defining a test suite. The test systems and corresponding top-level test suites are:

Before running the tests make sure PostgreSQL is running and a test database is created. By default tests use the following connection parameters to run the tests:

If connection with these parameters fails then you will be asked to provide the connection parameters interactively. The parameters will be stored in cl-postgres-tests:*test-connection* variable and automatically used on successive test runs. This variable can also be set manually before running the tests.

To test a particular component one would first load the corresponding test system, and then run the test suite. For example, to test the postmodern system in the REPL one would do the following:

(ql:quickload "postmodern/tests")
(5am:run! :postmodern)
;; ... test output ...

It is also possible to test multiple components at once by first loading test systems and then running all tests:

(ql:quickload '("cl-postgres/tests" "s-sql/tests"))
(5am:run-all-tests)
;; ... test output ...

To run the tests from command-line specify the same forms using your implementation's command-line syntax. For instance, to test all Postmodern components on SBCL, use the following command:

env DB_USER=$USER sbcl --noinform \
    --eval '(ql:quickload "postmodern/tests")' \
    --eval '(ql:quickload "cl-postgres/tests")' \
    --eval '(ql:quickload "s-sql/tests")' \
    --eval '(ql:quickload "simple-date/tests")' \
    --eval '(progn (setq 5am:*print-names* nil) (5am:run-all-tests))' \
    --eval '(sb-ext:exit)'

As you can see from above, database connection parameters can be provided using environment variables:


Next: , Previous: , Up: Top   [Contents][Index]

2 Systems

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


Next: , Previous: , Up: Systems   [Contents][Index]

2.1 postmodern

Maintainer

Sabra Crolleton <sabra.crolleton@gmail.com>

Author

Marijn Haverbeke <marijnh@gmail.com>

Home Page

https://github.com/marijnh/Postmodern

License

zlib

Description

PostgreSQL programming API

Version

1.32.9

Dependencies
Source

postmodern.asd (file)

Component

postmodern (module)


Next: , Previous: , Up: Systems   [Contents][Index]

2.2 s-sql

Maintainer

Sabra Crolleton <sabra.crolleton@gmail.com>

Author

Marijn Haverbeke <marijnh@gmail.com>

License

zlib

Description

Lispy DSL for SQL

Version

1.32.9

Dependencies
Source

s-sql.asd (file)

Component

s-sql (module)


Previous: , Up: Systems   [Contents][Index]

2.3 cl-postgres

Maintainer

Sabra Crolleton <sabra.crolleton@gmail.com>

Author

Marijn Haverbeke <marijnh@gmail.com>

License

zlib

Description

Low-level client library for PostgreSQL

Version

1.32.9

Dependencies
Source

cl-postgres.asd (file)

Component

cl-postgres (module)


Next: , Previous: , Up: Top   [Contents][Index]

3 Modules

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


Next: , Previous: , Up: Modules   [Contents][Index]

3.1 postmodern/postmodern

Parent

postmodern (system)

Location

postmodern/

Components

Next: , Previous: , Up: Modules   [Contents][Index]

3.2 s-sql/s-sql

Parent

s-sql (system)

Location

s-sql/

Components

Previous: , Up: Modules   [Contents][Index]

3.3 cl-postgres/cl-postgres

Parent

cl-postgres (system)

Location

cl-postgres/

Components

Next: , Previous: , Up: Top   [Contents][Index]

4 Files

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


Previous: , Up: Files   [Contents][Index]

4.1 Lisp


Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.1 postmodern.asd

Location

postmodern.asd

Systems

postmodern (system)

Packages

postmodern-system


Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.2 s-sql.asd

Location

s-sql.asd

Systems

s-sql (system)

Packages

s-sql-system


Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.3 cl-postgres.asd

Location

cl-postgres.asd

Systems

cl-postgres (system)

Packages

cl-postgres-system

Internal Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.4 postmodern/postmodern/package.lisp

Parent

postmodern (module)

Location

postmodern/package.lisp

Packages

postmodern


Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.5 postmodern/postmodern/connect.lisp

Dependency

package.lisp (file)

Parent

postmodern (module)

Location

postmodern/connect.lisp

Exported Definitions
Internal Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.6 postmodern/postmodern/json-encoder.lisp

Dependency

package.lisp (file)

Parent

postmodern (module)

Location

postmodern/json-encoder.lisp

Exported Definitions

encode-json-to-string (function)

Internal Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.7 postmodern/postmodern/query.lisp

Dependencies
Parent

postmodern (module)

Location

postmodern/query.lisp

Exported Definitions
Internal Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.8 postmodern/postmodern/prepare.lisp

Dependency

query.lisp (file)

Parent

postmodern (module)

Location

postmodern/prepare.lisp

Exported Definitions
Internal Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.9 postmodern/postmodern/roles.lisp

Dependency

query.lisp (file)

Parent

postmodern (module)

Location

postmodern/roles.lisp

Exported Definitions
Internal Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.10 postmodern/postmodern/util.lisp

Dependencies
Parent

postmodern (module)

Location

postmodern/util.lisp

Exported Definitions
Internal Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.11 postmodern/postmodern/transaction.lisp

Dependency

query.lisp (file)

Parent

postmodern (module)

Location

postmodern/transaction.lisp

Exported Definitions
Internal Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.12 postmodern/postmodern/namespace.lisp

Dependency

query.lisp (file)

Parent

postmodern (module)

Location

postmodern/namespace.lisp

Exported Definitions
Internal Definitions

do-with-schema (function)


Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.13 postmodern/postmodern/execute-file.lisp

Dependency

query.lisp (file)

Parent

postmodern (module)

Location

postmodern/execute-file.lisp

Exported Definitions
Internal Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.14 postmodern/postmodern/table.lisp

If Feature

postmodern-use-mop

Dependencies
Parent

postmodern (module)

Location

postmodern/table.lisp

Exported Definitions
Internal Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.15 postmodern/postmodern/deftable.lisp

Dependencies
Parent

postmodern (module)

Location

postmodern/deftable.lisp

Exported Definitions
Internal Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.16 s-sql/s-sql/package.lisp

Parent

s-sql (module)

Location

s-sql/package.lisp

Packages

s-sql


Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.17 s-sql/s-sql/s-sql.lisp

Parent

s-sql (module)

Location

s-sql/s-sql.lisp

Exported Definitions
Internal Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.18 cl-postgres/cl-postgres/package.lisp

Parent

cl-postgres (module)

Location

cl-postgres/package.lisp

Packages
Internal Definitions

*optimize* (special variable)


Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.19 cl-postgres/cl-postgres/features.lisp

Parent

cl-postgres (module)

Location

cl-postgres/features.lisp


Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.20 cl-postgres/cl-postgres/errors.lisp

Dependency

package.lisp (file)

Parent

cl-postgres (module)

Location

cl-postgres/errors.lisp

Exported Definitions
Internal Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

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

Dependency

package.lisp (file)

Parent

cl-postgres (module)

Location

cl-postgres/sql-string.lisp

Exported Definitions
Internal Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

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

Dependency

package.lisp (file)

Parent

cl-postgres (module)

Location

cl-postgres/trivial-utf-8.lisp

Exported Definitions
Internal Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

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

Dependencies
Parent

cl-postgres (module)

Location

cl-postgres/strings-utf-8.lisp

Internal Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.24 cl-postgres/cl-postgres/communicate.lisp

Dependencies
Parent

cl-postgres (module)

Location

cl-postgres/communicate.lisp

Internal Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.25 cl-postgres/cl-postgres/messages.lisp

Dependency

communicate.lisp (file)

Parent

cl-postgres (module)

Location

cl-postgres/messages.lisp

Internal Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.26 cl-postgres/cl-postgres/oid.lisp

Dependency

package.lisp (file)

Parent

cl-postgres (module)

Location

cl-postgres/oid.lisp

Exported Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

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

Parent

cl-postgres (module)

Location

cl-postgres/ieee-floats.lisp

Exported Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.28 cl-postgres/cl-postgres/interpret.lisp

Dependencies
Parent

cl-postgres (module)

Location

cl-postgres/interpret.lisp

Exported Definitions
Internal Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.29 cl-postgres/cl-postgres/saslprep.lisp

Parent

cl-postgres (module)

Location

cl-postgres/saslprep.lisp

Exported Definitions
Internal Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.30 cl-postgres/cl-postgres/scram.lisp

Dependencies
Parent

cl-postgres (module)

Location

cl-postgres/scram.lisp

Internal Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.31 cl-postgres/cl-postgres/protocol.lisp

Dependencies
Parent

cl-postgres (module)

Location

cl-postgres/protocol.lisp

Exported Definitions
Internal Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

4.1.32 cl-postgres/cl-postgres/public.lisp

Dependencies
Parent

cl-postgres (module)

Location

cl-postgres/public.lisp

Exported Definitions
Internal Definitions

Previous: , Up: Lisp files   [Contents][Index]

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

Dependencies
Parent

cl-postgres (module)

Location

cl-postgres/bulk-copy.lisp

Exported Definitions
Internal Definitions

Next: , Previous: , Up: Top   [Contents][Index]

5 Packages

Packages are listed by definition order.


Next: , Previous: , Up: Packages   [Contents][Index]

5.1 postmodern-system

Source

postmodern.asd

Use List

Next: , Previous: , Up: Packages   [Contents][Index]

5.2 postmodern

Source

package.lisp (file)

Nickname

pomo

Use List
Exported Definitions
Internal Definitions

Next: , Previous: , Up: Packages   [Contents][Index]

5.3 s-sql-system

Source

s-sql.asd

Use List

Next: , Previous: , Up: Packages   [Contents][Index]

5.4 s-sql

Source

package.lisp (file)

Use List

common-lisp

Used By List

postmodern

Exported Definitions
Internal Definitions

Next: , Previous: , Up: Packages   [Contents][Index]

5.5 cl-postgres-system

Source

cl-postgres.asd

Use List
Internal Definitions

Next: , Previous: , Up: Packages   [Contents][Index]

5.6 cl-postgres-error

Source

package.lisp (file)

Use List
Exported Definitions
Internal Definitions

Next: , Previous: , Up: Packages   [Contents][Index]

5.7 cl-postgres-oid

Source

package.lisp (file)

Nickname

oid

Use List

common-lisp

Exported Definitions

Next: , Previous: , Up: Packages   [Contents][Index]

5.8 cl-postgres

Source

package.lisp (file)

Use List

common-lisp

Used By List
Exported Definitions
Internal Definitions

Next: , Previous: , Up: Packages   [Contents][Index]

5.9 cl-postgres.features

Source

package.lisp (file)

Use List

common-lisp


Next: , Previous: , Up: Packages   [Contents][Index]

5.10 cl-postgres-ieee-floats

Source

package.lisp (file)

Nickname

clp-ieee-floats

Use List

common-lisp

Exported Definitions

Previous: , Up: Packages   [Contents][Index]

5.11 cl-postgres-trivial-utf-8

Source

package.lisp (file)

Nickname

clp-utf8

Use List

common-lisp

Exported Definitions
Internal Definitions

Next: , Previous: , Up: Top   [Contents][Index]

6 Definitions

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


Next: , Previous: , Up: Definitions   [Contents][Index]

6.1 Exported definitions


Next: , Previous: , Up: Exported definitions   [Contents][Index]

6.1.1 Constants

Constant: +abstime+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +aclitem+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +any+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +any-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +anyelement+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +anyenum+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +anynon-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +anyrange+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +bit+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +bit-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +bool+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +bool-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +box+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +box-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +bpchar+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +bpchar-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +bytea+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +bytea-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +cash+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +char+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +char-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +cid+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +cidr+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +circle+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +cstring+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +cstring-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +date+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +date-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +evttrigger+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +fdw-handler+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +float4+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +float4-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +float8+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +float8-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +gtsvector+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +index-am-handler+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +inet+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +int2+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +int2-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +int2vector+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +int4+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +int4-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +int4range+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +int8+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +int8-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +internal+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +interval+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +interval-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +json+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +jsonb+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +language-handler+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +line+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +lseg+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +lseg-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +lsn+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +macaddr+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +name+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +name-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +numeric+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +numeric-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +oid+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +oid-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +oid-vector+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +opaque+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +path+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +pgddlcommand+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +pgnodetree+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +point+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +point-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +polygon+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +record+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +record-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +refcursor+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +regclass+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +regconfig+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +regdictionary+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +regnamespace+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +regoper+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +regoperator+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +regproc+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +regprocedure+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +regrole+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +regtype+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +regtype-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +reltime+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +text+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +text-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +tid+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +time+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +time-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +timestamp+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +timestamp-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +timestamptz+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +timestamptz-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +timetz+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +tinterval+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +trigger+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +tsm-handler+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +tsquery+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +tsvector+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +unknown+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +uuid+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +v-oid+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +varbit+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +varbit-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +varchar+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +varchar-array+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +xid+
Package

cl-postgres-oid

Source

oid.lisp (file)

Constant: +xml+
Package

cl-postgres-oid

Source

oid.lisp (file)


Next: , Previous: , Up: Exported definitions   [Contents][Index]

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

prepare.lisp (file)

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 (file)

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 (file)

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

connect.lisp (file)

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

s-sql.lisp (file)

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

s-sql.lisp (file)

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. THIS IS NOT ALWAYS THROWING AN ERROR AND IT IS NOT OBVIOUS WHY. Setting this variable to a non-NIL will cause it to
simply ignore the unknown column.

Package

postmodern

Source

table.lisp (file)

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

transaction.lisp (file)

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

connect.lisp (file)

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

errors.lisp (file)

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

errors.lisp (file)

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 (file)

Special Variable: *retry-connect-delay*

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

Package

cl-postgres

Source

public.lisp (file)

Special Variable: *retry-connect-times*

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

Package

cl-postgres

Source

public.lisp (file)

Special Variable: *silently-truncate-rationals*

When a rational number is passed into a query (as per to-sql-string), but it
can not be expressed within 38 decimal digits (for example 1/3), it will be truncated, and lose some precision. Set this variable to nil to suppress that behaviour and raise an error instead.

Package

cl-postgres

Source

sql-string.lisp (file)

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

sql-string.lisp (file)

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 (file)

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 (file)

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 (file)

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

s-sql.lisp (file)

Special Variable: *table-name*

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

Package

postmodern

Source

deftable.lisp (file)

Special Variable: *table-symbol*

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

Package

postmodern

Source

deftable.lisp (file)

Special Variable: *unix-socket-dir*

Directory where the Unix domain socket for PostgreSQL be found.

Package

cl-postgres

Source

public.lisp (file)


Next: , Previous: , Up: Exported definitions   [Contents][Index]

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 (file)

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 (file)

Macro: defprepared NAME QUERY &optional FORMAT

This is the macro-style 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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 | | :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 |

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 (file)

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 (file)

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 (file)

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.Create a row-reader, using the given name for the fields argument and the given body for reading the rows. 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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)


Next: , Previous: , Up: Exported definitions   [Contents][Index]

6.1.4 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 (file)

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 (file)

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 (file)

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 (file)

Function: !unique-index &rest FIELDS

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

Package

postmodern

Source

deftable.lisp (file)

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 (file)

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 (file)

Function: alist-row-reader G0 FIELDS
Package

cl-postgres

Source

public.lisp (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

Function: clear-connection-pool ()

Disconnect and remove all connections in the connection pools.

Package

postmodern

Source

connect.lisp (file)

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 (file)

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 (file)

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 (file)

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 (file)

Function: commit-transaction TRANSACTION

Immediately commit an open transaction.

Package

postmodern

Source

transaction.lisp (file)

Function: connect DATABASE-NAME USER-NAME PASSWORD HOST &key PORT POOLED-P USE-SSL SERVICE

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 (file)

Function: connect-toplevel DATABASE-NAME USER-NAME PASSWORD HOST &key PORT USE-SSL

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 (file)

Function: connected-p DATABASE

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

Package

postmodern

Source

connect.lisp (file)

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 (file)

Function: copy-sql-readtable &optional TABLE

Copies a given readtable.

Package

cl-postgres

Source

interpret.lisp (file)

Function: create-all-tables ()

Create all defined tables.

Package

postmodern

Source

deftable.lisp (file)

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 (file)

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 (file)

Function: create-package-tables PACKAGE

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

Package

postmodern

Source

deftable.lisp (file)

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 (file)

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 (file)

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 (file)

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 (file)

Function: current-database ()

Returns the string name of the current database.

Package

postmodern

Source

util.lisp (file)

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 (file)

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 (file)

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 (file)

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 (file)

Function: database-exists-p DATABASE

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

Package

postmodern

Source

util.lisp (file)

Function: database-open-p CONNECTION

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

Package

cl-postgres

Source

public.lisp (file)

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 (file)

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 (file)

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 (file)

Function: decode-float32 BITS
Package

cl-postgres-ieee-floats

Source

ieee-floats.lisp (file)

Function: decode-float64 BITS
Package

cl-postgres-ieee-floats

Source

ieee-floats.lisp (file)

Function: default-sql-readtable ()

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

Package

cl-postgres

Source

interpret.lisp (file)

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 (file)

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 (file)

Function: describe-triggers ()

List detailed information on the triggers from the information_schema table.

Package

postmodern

Source

util.lisp (file)

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 (file)

Function: disconnect-toplevel ()

Disconnect *database*.

Package

postmodern

Source

connect.lisp (file)

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 (file)

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 (file)

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 (file)

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. Returns t if successful. Will not drop the postgres role.

As a minor matter of note, a role can own objects in databases it is not granted connection rights.

Package

postmodern

Source

roles.lisp (file)

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 (file)

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 (file)

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 (file)

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 (file)

Function: encode-float32 FLOAT
Package

cl-postgres-ieee-floats

Source

ieee-floats.lisp (file)

Function: encode-float64 FLOAT
Package

cl-postgres-ieee-floats

Source

ieee-floats.lisp (file)

Function: encode-json-to-string OBJECT

Return the JSON representation of OBJECT as a string.

Package

postmodern

Source

json-encoder.lisp (file)

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 (file)

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 (file)

Function: execute-file PATHNAME &optional PRINT

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.

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.

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

Function: from-sql-name STR

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

Package

s-sql

Source

s-sql.lisp (file)

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 (file)

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 (file)

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 (file)

Function: get-database-comment &optional DATABASE-NAME

Returns the comment, if any, attached to a database

Package

postmodern

Source

util.lisp (file)

Function: get-pid ()

Get the process id used by postgresql for this connection.

Package

postmodern

Source

prepare.lisp (file)

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 (file)

Function: get-postgresql-version CONNECTION

Retrieves the version number of the connected postgresql database as a string.

Package

cl-postgres

Source

public.lisp (file)

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 (file)

Function: get-search-path ()

Returns the default schema search path for the current session.

Package

postmodern

Source

namespace.lisp (file)

Function: get-table-comment TABLE-NAME &optional SCHEMA-NAME

Retrieves the comment, if any attached to the table.

Package

postmodern

Source

util.lisp (file)

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 (file)

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.

Package

postmodern

Source

roles.lisp (file)

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

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

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 (file)

Function: ignore-row-reader G0 FIELDS
Package

cl-postgres

Source

public.lisp (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

Function: list-database-functions ()

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

Package

postmodern

Source

util.lisp (file)

Function: list-database-users ()

List database users (actually ’roles’ in Postgresql terminology).

Package

postmodern

Source

roles.lisp (file)

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 (file)

Function: list-detailed-triggers ()

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

Package

postmodern

Source

util.lisp (file)

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 (file)

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 (file)

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 (file)

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 (file)

Function: list-installed-extensions ()

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

Package

postmodern

Source

util.lisp (file)

Function: list-postmodern-prepared-statements &optional NAMES-ONLY

List the prepared statements that postmodern has put in the meta slot in the connection. 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 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 (file)

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

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 (file)

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 (file)

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 (file)

Function: list-row-reader G0 FIELDS
Package

cl-postgres

Source

public.lisp (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

Function: list-templates ()

Returns a list of existing database template names.

Package

postmodern

Source

util.lisp (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

Function: open-database DATABASE USER PASSWORD HOST &optional PORT USE-SSL SERVICE

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 (file)

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 (file)

Function: parse-queries FILE-CONTENT

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

Package

postmodern

Source

execute-file.lisp (file)

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 (file)

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 (file)

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 (file)

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 (file)

Function: prepare-query CONNECTION NAME QUERY

Parse and plan the given query, and store it under the given name. Note that prepared statements are per-connection, so they can only be executed through the same connection that prepared them.

Package

cl-postgres

Source

public.lisp (file)

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 (file)

Function: read-queries FILENAME

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

Package

postmodern

Source

execute-file.lisp (file)

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 (file)

Function: release-savepoint SAVEPOINT

Immediately release a savepoint, commiting its results.

Package

postmodern

Source

transaction.lisp (file)

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. Returns t if successful.

Package

postmodern

Source

util.lisp (file)

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 (file)

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 (file)

Function: reset-prepared-statement CONDITION

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 (file)

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 (file)

Function: role-exists-p ROLE-NAME

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

Package

postmodern

Source

roles.lisp (file)

Function: rollback-savepoint SAVEPOINT

Immediately roll back a savepoint, aborting the results.

Package

postmodern

Source

transaction.lisp (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

Function: sql-error CONTROL &rest ARGS
Package

s-sql

Source

s-sql.lisp (file)

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 (file)

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 (file)

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 (file)

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 (file)

Function: string-printable-ascii-p STR

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

Package

cl-postgres

Source

saslprep.lisp (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

Function: to-sql-name ()

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 (file)

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.

Package

cl-postgres

Source

public.lisp (file)

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 (file)

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 (file)

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 (file)

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 (file)

Function: vector-row-reader G0 FIELDS
Package

cl-postgres

Source

public.lisp (file)

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 (file)

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 (file)

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 (file)


Next: , Previous: , Up: Exported definitions   [Contents][Index]

6.1.5 Generic functions

Generic Function: 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 (file)

Writer

(setf abort-hooks) (generic function)

Methods
Method: abort-hooks (TRANSACTION-HANDLE transaction-handle)

automatically generated reader method

Generic Function: (setf abort-hooks) NEW-VALUE OBJECT
Package

postmodern

Reader

abort-hooks (generic function)

Methods
Method: (setf abort-hooks) NEW-VALUE (TRANSACTION-HANDLE transaction-handle)

automatically generated writer method

Source

transaction.lisp (file)

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 (file)

Methods
Method: abort-logical-transaction (TRANSACTION transaction-handle)
Method: abort-logical-transaction (SAVEPOINT savepoint-handle)
Generic Function: 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 (file)

Writer

(setf commit-hooks) (generic function)

Methods
Method: commit-hooks (TRANSACTION-HANDLE transaction-handle)

automatically generated reader method

Generic Function: (setf commit-hooks) NEW-VALUE OBJECT
Package

postmodern

Reader

commit-hooks (generic function)

Methods
Method: (setf commit-hooks) NEW-VALUE (TRANSACTION-HANDLE transaction-handle)

automatically generated writer method

Source

transaction.lisp (file)

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 (file)

Methods
Method: commit-logical-transaction (TRANSACTION transaction-handle)
Method: commit-logical-transaction (SAVEPOINT savepoint-handle)
Generic Function: 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 (file)

Writer

(setf connection-parameters) (generic function)

Methods
Method: connection-parameters (DATABASE-CONNECTION database-connection)

automatically generated reader method

Generic Function: (setf connection-parameters) NEW-VALUE OBJECT
Package

cl-postgres

Reader

connection-parameters (generic function)

Methods
Method: (setf connection-parameters) NEW-VALUE (DATABASE-CONNECTION database-connection)

automatically generated writer method

Source

public.lisp (file)

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 (file)

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 (file)

Methods
Method: dao-keys DAO
Method: dao-keys (CLASS-NAME symbol)
Method: dao-keys (CLASS dao-class) before
Method: dao-keys (DAO-CLASS dao-class)

automatically generated reader method

Generic Function: database-error-cause CONDITION
Package

cl-postgres

Methods
Method: database-error-cause (CONDITION database-error)
Source

errors.lisp (file)

Generic Function: database-error-code CONDITION
Package

cl-postgres

Methods
Method: database-error-code (CONDITION database-error)
Source

errors.lisp (file)

Generic Function: database-error-detail CONDITION
Package

cl-postgres

Methods
Method: database-error-detail (CONDITION database-error)
Source

errors.lisp (file)

Generic Function: database-error-message CONDITION
Generic Function: (setf database-error-message) NEW-VALUE CONDITION
Package

cl-postgres

Methods
Method: database-error-message (CONDITION database-error)
Method: (setf database-error-message) NEW-VALUE (CONDITION database-error)
Source

errors.lisp (file)

Generic Function: database-error-query CONDITION
Package

cl-postgres

Methods
Method: database-error-query (CONDITION database-error)
Source

errors.lisp (file)

Generic Function: delete-dao DAO

Delete the given dao from the database.

Package

postmodern

Source

table.lisp (file)

Generic Function: disconnect DATABASE

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

Package

postmodern

Source

connect.lisp (file)

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 (file)

Generic Function: 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 (file)

Writer

(setf field-name) (generic function)

Methods
Method: field-name (FIELD-DESCRIPTION field-description)

automatically generated reader method

Generic Function: (setf field-name) NEW-VALUE OBJECT
Package

cl-postgres

Reader

field-name (generic function)

Methods
Method: (setf field-name) NEW-VALUE (FIELD-DESCRIPTION field-description)

automatically generated writer method

Source

protocol.lisp (file)

Generic Function: 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 (file)

Writer

(setf field-type) (generic function)

Methods
Method: field-type (FIELD-DESCRIPTION field-description)

automatically generated reader method

Generic Function: (setf field-type) NEW-VALUE OBJECT
Package

cl-postgres

Reader

field-type (generic function)

Methods
Method: (setf field-type) NEW-VALUE (FIELD-DESCRIPTION field-description)

automatically generated writer method

Source

protocol.lisp (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 Function: postgresql-notification-channel CONDITION
Generic Function: (setf postgresql-notification-channel) NEW-VALUE CONDITION
Package

cl-postgres

Methods
Method: postgresql-notification-channel (CONDITION postgresql-notification)
Method: (setf postgresql-notification-channel) NEW-VALUE (CONDITION postgresql-notification)
Source

protocol.lisp (file)

Generic Function: postgresql-notification-payload CONDITION
Generic Function: (setf postgresql-notification-payload) NEW-VALUE CONDITION
Package

cl-postgres

Methods
Method: postgresql-notification-payload (CONDITION postgresql-notification)
Method: (setf postgresql-notification-payload) NEW-VALUE (CONDITION postgresql-notification)
Source

protocol.lisp (file)

Generic Function: postgresql-notification-pid CONDITION
Generic Function: (setf postgresql-notification-pid) NEW-VALUE CONDITION
Package

cl-postgres

Methods
Method: postgresql-notification-pid (CONDITION postgresql-notification)
Method: (setf postgresql-notification-pid) NEW-VALUE (CONDITION postgresql-notification)
Source

protocol.lisp (file)

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 (file)

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 (file)

Methods
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 (file)

Methods
Method: sql-escape (ARG symbol)
Method: sql-escape (ARG vector)
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 (file)

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 varchar)) &rest ARGS
Method: sql-type-name (LISP-TYPE (eql 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 double-precision)) &rest ARGS
Method: sql-type-name (LISP-TYPE (eql serial)) &rest ARGS
Method: sql-type-name (LISP-TYPE (eql serial8)) &rest ARGS
Method: sql-type-name (LISP-TYPE (eql array)) &rest ARGS
Method: sql-type-name (LISP-TYPE (eql db-null)) &rest ARGS
Generic Function: text CONDITION
Package

s-sql

Methods
Method: text (CONDITION inconsistent-schema-name)
Source

util.lisp (file)

Method: text (CONDITION invalid-database-name)
Source

util.lisp (file)

Method: text (CONDITION malformed-composite-type-error)
Source

s-sql.lisp (file)

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 used by S-SQL.)

Package

cl-postgres

Source

sql-string.lisp (file)

Methods
Method: to-sql-string (ARG string)
Method: to-sql-string (ARG vector)
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 (file)

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 (file)


Next: , Previous: , Up: Exported definitions   [Contents][Index]

6.1.6 Conditions

Condition: admin-shutdown ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

server-shutdown (condition)

Condition: cannot-connect-now ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

operator-intervention (condition)

Condition: check-violation ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

integrity-violation (condition)

Condition: columns-error ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

syntax-error-or-access-violation (condition)

Condition: connection-does-not-exist ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

database-error (condition)

Condition: connection-exception ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

database-error (condition)

Condition: connection-failure ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

database-error (condition)

Condition: crash-shutdown ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

server-shutdown (condition)

Condition: data-exception ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

database-error (condition)

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 (file)

Direct superclasses

database-error (condition)

Direct subclasses
Condition: database-connection-lost ()

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

Package

cl-postgres

Source

errors.lisp (file)

Direct superclasses

database-connection-error (condition)

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 (file)

Direct superclasses

error (condition)

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.

Initargs

:code

Initform

(quote nil)

Readers

database-error-code (generic function)

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 (generic function)

Writers

(setf database-error-message) (generic function)

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.

Initargs

:detail

Initform

(quote nil)

Readers

database-error-detail (generic function)

Slot: hint

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

Initargs

:hint

Initform

(quote nil)

Readers

database-error-hint (generic function)

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.

Initargs

:context

Initform

(quote nil)

Readers

database-error-context (generic function)

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 (generic function)

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.

Initargs

:position

Initform

(quote nil)

Readers

database-error-position (generic function)

Slot: cause

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

Initargs

:cause

Initform

(quote nil)

Readers

database-error-cause (generic function)

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 (file)

Direct superclasses

database-connection-error (condition)

Condition: db-division-by-zero ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

data-exception (condition)

Condition: deadlock-detected ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

transaction-rollback (condition)

Condition: duplicate-alias ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

syntax-error-or-access-violation (condition)

Condition: duplicate-column ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

syntax-error-or-access-violation (condition)

Condition: duplicate-cursor ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

syntax-error-or-access-violation (condition)

Condition: duplicate-database ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

syntax-error-or-access-violation (condition)

Condition: duplicate-function ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

syntax-error-or-access-violation (condition)

Condition: duplicate-object ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

syntax-error-or-access-violation (condition)

Condition: duplicate-prepared-statement ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

syntax-error-or-access-violation (condition)

Condition: duplicate-schema ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

syntax-error-or-access-violation (condition)

Condition: duplicate-table ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

syntax-error-or-access-violation (condition)

Condition: feature-not-supported ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

database-error (condition)

Condition: floating-point-exception ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

data-exception (condition)

Condition: foreign-key-violation ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

integrity-violation (condition)

Condition: insufficient-privilege ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

syntax-error-or-access-violation (condition)

Condition: insufficient-resources ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

database-error (condition)

Condition: integrity-violation ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

database-error (condition)

Direct subclasses
Condition: internal-error ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

database-error (condition)

Condition: invalid-datetime-format ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

data-exception (condition)

Condition: invalid-sql-statement-name ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

database-error (condition)

Condition: lock-not-available ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

object-state-error (condition)

Condition: not-null-violation ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

integrity-violation (condition)

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

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

data-exception (condition)

Condition: object-in-use ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

object-state-error (condition)

Condition: object-state-error ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

database-error (condition)

Direct subclasses
Condition: operator-intervention ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

database-error (condition)

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 (file)

Direct superclasses

simple-warning (condition)

Direct methods
Direct slots
Slot: pid
Initargs

:pid

Readers

postgresql-notification-pid (generic function)

Writers

(setf postgresql-notification-pid) (generic function)

Slot: channel
Initargs

:channel

Readers

postgresql-notification-channel (generic function)

Writers

(setf postgresql-notification-channel) (generic function)

Slot: payload
Initargs

:payload

Readers

postgresql-notification-payload (generic function)

Writers

(setf postgresql-notification-payload) (generic function)

Condition: postgresql-warning ()
Package

cl-postgres

Source

protocol.lisp (file)

Direct superclasses

simple-warning (condition)

Condition: program-limit-exceeded ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

database-error (condition)

Condition: protocol-violation ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

database-error (condition)

Condition: query-canceled ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

operator-intervention (condition)

Condition: restrict-violation ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

integrity-violation (condition)

Condition: serialization-failure ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

transaction-rollback (condition)

Condition: server-shutdown ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses
Direct subclasses
Condition: sql-error ()
Package

s-sql

Source

s-sql.lisp (file)

Direct superclasses

simple-error (condition)

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

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

database-error (condition)

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

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

database-error (condition)

Condition: statement-completion-unknown ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

transaction-rollback (condition)

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

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

database-error (condition)

Direct subclasses
Condition: system-error ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

database-error (condition)

Condition: transaction-integrity-constraint-violation ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

transaction-rollback (condition)

Condition: transaction-resolution-unknown ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

database-error (condition)

Condition: transaction-rollback ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

database-error (condition)

Direct subclasses
Condition: undefined-column ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

syntax-error-or-access-violation (condition)

Condition: undefined-table ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

syntax-error-or-access-violation (condition)

Condition: unique-violation ()
Package

cl-postgres-error

Source

errors.lisp (file)

Direct superclasses

integrity-violation (condition)

Condition: utf-8-decoding-error ()
Package

cl-postgres-trivial-utf-8

Source

trivial-utf-8.lisp (file)

Direct superclasses

simple-error (condition)

Direct slots
Slot: message
Initargs

:message

Slot: byte
Initargs

:byte

Initform

(quote nil)


Next: , Previous: , Up: Exported definitions   [Contents][Index]

6.1.7 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 (file)

Direct superclasses

standard-class (class)

Direct methods
Direct slots
Slot: direct-keys
Initargs

:keys

Readers

direct-keys (generic function)

Slot: effective-keys
Readers

dao-keys (generic function)

Slot: table-name
Slot: column-map
Readers

dao-column-map (generic function)

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 (file)

Direct superclasses

standard-object (class)

Direct subclasses

pooled-database-connection (class)

Direct methods
Direct slots
Slot: host
Initargs

:host

Readers

connection-host (generic function)

Slot: port
Initargs

:port

Readers

connection-port (generic function)

Slot: database
Initargs

:db

Readers

connection-db (generic function)

Slot: user
Initargs

:user

Readers

connection-user (generic function)

Slot: password
Initargs

:password

Readers

connection-password (generic function)

Slot: use-ssl
Initargs

:ssl

Readers

connection-use-ssl (generic function)

Slot: service
Initargs

:service

Readers

connection-service (generic function)

Writers

(setf connection-service) (generic function)

Slot: socket
Initargs

:socket

Readers

connection-socket (generic function)

Writers

(setf connection-socket) (generic function)

Slot: meta
Slot: available
Initform

t

Readers

connection-available (generic function)

Writers

(setf connection-available) (generic function)

Slot: parameters
Readers

connection-parameters (generic function)

Writers

(setf connection-parameters) (generic function)

Slot: timestamp-format
Readers

connection-timestamp-format (generic function)

Writers

(setf connection-timestamp-format) (generic function)


Previous: , Up: Exported definitions   [Contents][Index]

6.1.8 Types

Type: bigint ()

Also know as int8

Package

s-sql

Source

s-sql.lisp (file)

Type: bytea ()
Package

s-sql

Source

s-sql.lisp (file)

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 (file)

Type: double-precision ()
Package

s-sql

Source

s-sql.lisp (file)

Type: numeric &optional PRECISION/SCALE SCALE
Package

s-sql

Source

s-sql.lisp (file)

Type: smallint ()

Also known as int2

Package

s-sql

Source

s-sql.lisp (file)

Type: text ()
Package

s-sql

Source

s-sql.lisp (file)

Type: varchar LENGTH
Package

s-sql

Source

s-sql.lisp (file)


Previous: , Up: Definitions   [Contents][Index]

6.2 Internal definitions


Next: , Previous: , Up: Internal definitions   [Contents][Index]

6.2.1 Constants

Constant: +seconds-in-day+
Package

cl-postgres

Source

interpret.lisp (file)

Constant: +start-of-2000+
Package

cl-postgres

Source

interpret.lisp (file)


Next: , Previous: , Up: Internal definitions   [Contents][Index]

6.2.2 Special variables

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

postmodern

Source

roles.lisp (file)

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

postmodern

Source

roles.lisp (file)

Special Variable: *character-sets*
Package

postmodern

Source

util.lisp (file)

Special Variable: *class-finalize-lock*
Package

postmodern

Source

query.lisp (file)

Special Variable: *client-encoding*
Package

cl-postgres

Source

strings-utf-8.lisp (file)

Special Variable: *collations*
Package

postmodern

Source

util.lisp (file)

Special Variable: *connection-params*

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

Package

cl-postgres

Source

protocol.lisp (file)

Special Variable: *connection-pools*

Maps pool specifiers to lists of pooled connections.

Package

postmodern

Source

connect.lisp (file)

Special Variable: *current-query*
Package

cl-postgres

Source

errors.lisp (file)

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

table.lisp (file)

Special Variable: *default-sql-readtable*

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

Package

cl-postgres

Source

interpret.lisp (file)

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 (file)

Special Variable: *disallowed-role-names*

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

Package

postmodern

Source

roles.lisp (file)

Special Variable: *effected-rows*
Package

cl-postgres

Source

protocol.lisp (file)

Special Variable: *error-table*
Package

cl-postgres-error

Source

errors.lisp (file)

Special Variable: *execute-privileges-list*
Package

postmodern

Source

roles.lisp (file)

Special Variable: *expand-runtime*
Package

s-sql

Source

s-sql.lisp (file)

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

json-encoder.lisp (file)

Special Variable: *json-aggregate-context*

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

Package

postmodern

Source

json-encoder.lisp (file)

Special Variable: *json-aggregate-first*

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

Package

postmodern

Source

json-encoder.lisp (file)

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

json-encoder.lisp (file)

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

postmodern

Source

json-encoder.lisp (file)

Special Variable: *json-output*

The default output stream for encoding operations.

Package

postmodern

Source

json-encoder.lisp (file)

Special Variable: *json-symbols-package*

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

Package

postmodern

Source

json-encoder.lisp (file)

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

json-encoder.lisp (file)

Special Variable: *optimize*
Package

cl-postgres

Source

package.lisp (file)

Special Variable: *optimize*
Package

cl-postgres-trivial-utf-8

Source

trivial-utf-8.lisp (file)

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 (file)

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

s-sql.lisp (file)

Special Variable: *printable-ascii-chars*
Package

cl-postgres

Source

saslprep.lisp (file)

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

query.lisp (file)

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

connect.lisp (file)

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 (file)

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 (file)

Special Variable: *transaction-level*
Package

postmodern

Source

transaction.lisp (file)

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

json-encoder.lisp (file)


Next: , Previous: , Up: Internal definitions   [Contents][Index]

6.2.3 Macros

Macro: all-rows FORM
Package

postmodern

Source

query.lisp (file)

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 (file)

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 (file)

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 (file)

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 (file)

Macro: dao-row-reader-with-body (TYPE TYPE-VAR) &body BODY
Package

postmodern

Source

table.lisp (file)

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 (file)

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 (file)

Macro: deferror CODE TYPENAME &optional SUPERCLASS
Package

cl-postgres-error

Source

errors.lisp (file)

Macro: define-interpreter OID NAME FIELDS &body VALUE

Shorthand for defining binary readers.

Package

cl-postgres

Source

interpret.lisp (file)

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 (file)

Macro: integer-reader BYTES

Create a function to read integers from a binary stream.

Package

cl-postgres

Source

communicate.lisp (file)

Macro: integer-writer BYTES

Create a function to write integers to a binary stream.

Package

cl-postgres

Source

communicate.lisp (file)

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 (file)

Macro: make-list-query RELKIND

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

Package

postmodern

Source

util.lisp (file)

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 (file)

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 (file)

Macro: single-row FORM
Package

postmodern

Source

query.lisp (file)

Macro: single-row! FORM
Package

postmodern

Source

query.lisp (file)

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 (file)

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 (file)

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 (file)

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 (file)

Macro: with-explicit-encoder &body BODY
Package

postmodern

Source

json-encoder.lisp (file)

Macro: with-guessing-encoder &body BODY
Package

postmodern

Source

json-encoder.lisp (file)

Macro: with-local-encoder &body BODY
Package

postmodern

Source

json-encoder.lisp (file)

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 (file)

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 (file)

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

cl-postgres

Source

errors.lisp (file)

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 (file)

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 (file)

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 (file)


Next: , Previous: , Up: Internal definitions   [Contents][Index]

6.2.4 Functions

Function: %build-foreign-reference TARGET ON-DELETE ON-UPDATE MATCH
Package

s-sql

Source

s-sql.lisp (file)

Function: %eval CODE
Package

postmodern

Source

table.lisp (file)

Function: add-table-definition SYMBOL FUNC
Package

postmodern

Source

deftable.lisp (file)

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 (file)

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 (file)

Function: array-hash-row-reader G0 FIELDS
Package

postmodern

Source

query.lisp (file)

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 (file)

Function: bad-char-error MESSAGE &key VALUE NORMALIZATION-FORM
Package

cl-postgres

Source

saslprep.lisp (file)

Function: begin-transaction &optional ISOLATION-LEVEL
Package

postmodern

Source

transaction.lisp (file)

Function: bind-message SOCKET NAME RESULT-FORMATS PARAMETERS

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 (file)

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 (file)

Function: build-row-reader FUNCTION-FORM FIELDS BODY

Helper for the following two macros.

Package

cl-postgres

Source

protocol.lisp (file)

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 (file)

Function: call-with-ensured-transaction THUNK &optional ISOLATION-LEVEL
Package

postmodern

Source

transaction.lisp (file)

Function: call-with-logical-transaction NAME BODY &optional ISOLATION-LEVEL
Package

postmodern

Source

transaction.lisp (file)

Function: call-with-savepoint NAME BODY
Package

postmodern

Source

transaction.lisp (file)

Function: call-with-transaction BODY &optional ISOLATION-LEVEL
Package

postmodern

Source

transaction.lisp (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

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 (file)

Function: char-printable-ascii-p CH

Returns t if the char is printable ascii.

Package

cl-postgres

Source

saslprep.lisp (file)

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 (file)

Function: close-prepared-message G0 NAME
Package

cl-postgres

Source

messages.lisp (file)

Function: code-char-0-p CHR

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

Package

postmodern

Source

util.lisp (file)

Function: code-point-printable-ascii-p INT

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

Package

cl-postgres

Source

saslprep.lisp (file)

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 (file)

Function: column-row-reader G0 FIELDS
Package

postmodern

Source

query.lisp (file)

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 (file)

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 (file)

Function: copier-write-sequence S VECTOR
Package

cl-postgres

Source

bulk-copy.lisp (file)

Function: copier-write-value S VAL
Package

cl-postgres

Source

bulk-copy.lisp (file)

Function: copy-data-message SOCKET DATA
Package

cl-postgres

Source

messages.lisp (file)

Function: copy-done-message G0
Package

cl-postgres

Source

messages.lisp (file)

Function: copy-fail-message G0 REASON
Package

cl-postgres

Source

messages.lisp (file)

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 (file)

Function: copy-parser INSTANCE
Package

postmodern

Source

execute-file.lisp (file)

Function: copy-query SELF
Package

cl-postgres

Source

bulk-copy.lisp (file)

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 (file)

Function: dao-column-fields CLASS
Package

postmodern

Source

table.lisp (file)

Function: dao-column-slots CLASS

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

Package

postmodern

Source

table.lisp (file)

Function: dao-from-fields CLASS COLUMN-MAP QUERY-FIELDS RESULT-NEXT-FIELD-GENERATOR-FN
Package

postmodern

Source

table.lisp (file)

Function: dao-row-reader CLASS

Defines a row-reader for objects of a given class.

Package

postmodern

Source

table.lisp (file)

Function: dao-spec-for-format FORMAT
Package

postmodern

Source

query.lisp (file)

Function: dao-superclasses CLASS

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

Package

postmodern

Source

table.lisp (file)

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 (file)

Function: default-date-reader DAYS-SINCE-2000
Package

cl-postgres

Source

interpret.lisp (file)

Function: default-interval-reader MONTHS DAYS USECONDS
Package

cl-postgres

Source

interpret.lisp (file)

Function: default-time-reader USECS
Package

cl-postgres

Source

interpret.lisp (file)

Function: default-timestamp-reader USECONDS-SINCE-2000
Package

cl-postgres

Source

interpret.lisp (file)

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 (file)

Function: describe-prepared-message G0 NAME
Package

cl-postgres

Source

messages.lisp (file)

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 (file)

Function: do-with-schema SCHEMA THUNK &key STRICT IF-NOT-EXIST DROP-AFTER
Package

postmodern

Source

namespace.lisp (file)

Function: enc-byte-length SEQUENCE
Package

cl-postgres

Source

strings-utf-8.lisp (file)

Function: enc-read-string INPUT &key NULL-TERMINATED BYTE-LENGTH
Package

cl-postgres

Source

strings-utf-8.lisp (file)

Function: enc-string-bytes STRING &key NULL-TERMINATE
Package

cl-postgres

Source

strings-utf-8.lisp (file)

Function: enc-write-string STRING OUTPUT &key NULL-TERMINATE
Package

cl-postgres

Source

strings-utf-8.lisp (file)

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 (file)

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 (file)

Function: encode-json-alist-to-string ALIST

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

Package

postmodern

Source

json-encoder.lisp (file)

Function: encode-json-list-explicit-encoder S STREAM
Package

postmodern

Source

json-encoder.lisp (file)

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 (file)

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 (file)

Function: encode-json-plist-to-string PLIST

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

Package

postmodern

Source

json-encoder.lisp (file)

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 (file)

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 (file)

Function: ensure-prepared CONNECTION ID QUERY &optional OVERWRITE

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.

Package

postmodern

Source

prepare.lisp (file)

Function: ensure-socket-is-closed SOCKET &key ABORT
Package

cl-postgres

Source

communicate.lisp (file)

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 (file)

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 (file)

Function: expand-composite-table-name FRM

Helper function for building a composite table name

Package

s-sql

Source

s-sql.lisp (file)

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 (file)

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 (file)

Function: expand-foreign-on* ACTION
Package

s-sql

Source

s-sql.lisp (file)

Function: expand-identity KEYWD
Package

s-sql

Source

s-sql.lisp (file)

Function: expand-interval OPTION

Provide interval limit options

Package

s-sql

Source

s-sql.lisp (file)

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 (file)

Function: expand-rows ROWS LENGTH
Package

s-sql

Source

s-sql.lisp (file)

Function: expand-table-column COLUMN-NAME ARGS
Package

s-sql

Source

s-sql.lisp (file)

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 (file)

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 (file)

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 (file)

Function: flat-table-name &optional TABLE
Package

postmodern

Source

deftable.lisp (file)

Function: flush-message G0
Package

cl-postgres

Source

messages.lisp (file)

Function: for-update/share SHARE-OR-UPDATE FORM &rest ARGS
Package

s-sql

Source

s-sql.lisp (file)

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 (file)

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 (file)

Function: gen-client-initial-response USER-NAME CLIENT-NONCE
Package

cl-postgres

Source

scram.lisp (file)

Function: gen-client-key SALTED-PASSWORD &optional MESSAGE SHA-METHOD

Returns a byte array

Package

cl-postgres

Source

scram.lisp (file)

Function: gen-client-nonce &optional NONCE-LENGTH

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

Package

cl-postgres

Source

scram.lisp (file)

Function: gen-client-proof CLIENT-KEY CLIENT-SIGNATURE

The eventual client-proof needs to be base64 encoded

Package

cl-postgres

Source

scram.lisp (file)

Function: gen-client-signature STORED-KEY AUTH-MESSAGE &optional SHA-METHOD
Package

cl-postgres

Source

scram.lisp (file)

Function: gen-final-message FINAL-MESSAGE-PART1 CLIENT-PROOF

Assuming client-proof is in a usb8 array, returns client-proof as part of the final message as a base64 string

Package

cl-postgres

Source

scram.lisp (file)

Function: gen-final-message-part-1 SERVER-NONCE

Assumes the server-nonce is a utf8 string

Package

cl-postgres

Source

scram.lisp (file)

Function: gen-salted-password PASSWORD SERVER-SALT ITERATIONS &ke