S-SQL reference manual

This is the reference manual for the S-SQL component of the postmodern library.

S-SQL provides a lispy syntax for SQL queries, and knows how to convert various lisp types to their textual SQL representation. It takes care to do as much of the work as possible at compile-time, so that at runtime a string concatenation is all that is needed to produce the final SQL query.

Contents

  1. Interface
  2. SQL Types
  3. SQL Syntax
  4. Symbol-index

Interface

macro sql (form)
→ string

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

function sql-compile (form)
→ string

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.

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.

macro enable-s-sql-syntax (&optional (char #\Q))

Modifies the current readtable to add a #Q syntax that is read as (sql ...). The character to use can be overridden by passing an argument.

function sql-escape-string (string)
→ string

Escapes a string for inclusion in a PostgreSQL query.

method sql-escape (value)
→ string

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.

variable *standard-sql-strings*

Used to configure whether S-SQL will use standard SQL strings (just replace #\' with ''), or backslash-style escaping. Setting this to NIL is always safe, but when the server is configured to allow standard strings (compile-time parameter 'standard_conforming_strings' is 'on', which will become the default in future versions of PostgreSQL), the noise in queries can be reduced by setting this to T.

variable *escape-sql-names-p*

Determines whether double quotes are added around column, table, and function names in queries. May be T, in which case every name is escaped, NIL, in which case none is, or :auto, which causes only reserved words to be escaped.. 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.

function sql-type-name (type)
→ string

Create the SQL equivalent of the given Lisp type, if one is known. See types.

function to-sql-name (name &optional (escape-p *escape-sql-names-p*))
→ string

Convert a symbol or string to a name that can be used as an SQL identifier by converting all non-alphanumeric characters to underscores. Also lowercases the name to make queries look a bit less hideous. When a second argument is given, this overrides the current value of *escape-sql-names-p*.

function from-sql-name (string)
→ keyword

Convert a string that represents an SQL identifier to a keyword by uppercasing it and converting the underscores to dashes.

macro register-sql-operators (arity &rest names)

Define simple SQL 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 may follow any number of operators, either just a keyword, in which case the downcased symbol name is used as the SQL operator, or a two-element list containing a keyword and a name string.

SQL Types

S-SQL knows the SQL equivalents to a number of Lisp types, and defines some extra types that can be used to denote other SQL types. The following table shows the correspondence:

Lisp typeSQL type
smallintsmallint
integerinteger
bigintbigint
(numeric X Y)numeric(X, Y)
float, realreal
double-float, double-precisiondouble-precision
string, texttext
(string X)char(X)
(varchar X)varchar(X)
booleanboolean
byteabytea
datedate
timestamptimestamp
intervalinterval

type db-null

This is a type of which only the keyword :null is a member. It is used to represent NULL values from the database.

SQL Syntax

An S-SQL form is converted to a query through the following rules:

The following operators are defined:

sql-op :+, :*, :%, :&, :|, :||, :and, :or, :=, :/, :!=, :<, :>, :<=, :>=, :^, :union, :union-all, :intersect, :intersect-all, :except, :except-all (&rest args)

These are expanded as infix operators. When meaningful, they allow more than two arguments. :- can also be used as a unary operator to negate a value. Note that the arguments to :union, :union-all, :intersect, and :except should be queries (:select forms).

Note that you'll have to escape pipe characters to enter them as keywords. S-SQL handles the empty keyword symbol (written :||) specially, and treats it like :\|\|, so that it can be written without escapes. With :\|, this doesn't work.

sql-op :~, :not (arg)

Unary operators for bitwise and logical negation.

sql-op :~, :~*, :!~, :!~* (string pattern)

Regular expression matching operators. The exclamation mark means 'does not match', the asterisk makes the match case-insensitive.

sql-op :like, :ilike (string pattern)

Simple SQL string matching operators (:ilike is case-insensitive).

sql-op :@@

Fast Text Search match operator.

sql-op :desc (column)

Used to invert the meaning of an operator in an :order-by clause.

sql-op :nulls-first, :nulls-last (column)

Used to determine where :null values appear in an :order-by clause.

sql-op :as (form name &rest fields)

Assigns a name to a column or table in a :select form. When fields are given, they are added after the name, in parentheses. For example, (:as 'table1 't1 'foo 'bar) becomes table1 AS t1(foo, bar). When you need to specify types for the fields, you can do something like (:as 'table2 't2 ('foo integer)). Note that names are quoted, types are not (when using sql-compile or sql-template, you can leave out the quotes entirely).

sql-op :exists (query)

The EXISTS operator. Takes a query as an argument, and returns true or false depending on whether that query returns any rows.

sql-op :is-null (arg)

Test whether a value is null.

sql-op :not-null (arg)

Test whether a value is not null.

sql-op :in (value set)

Test whether a value is in a set of values.

sql-op :not-in (value set)

Inverse of the above.

sql-op :set (&rest elements)

Denote a set of values. This one has two interfaces. When the elements are known at compile-time, they can be given as multiple arguments to the operator. When they are not, a single argument that evaluates to a list should be used.

sql-op :[] (form start &optional end)

Dereference an array value. If end is provided, extract a slice of the array.

sql-op :extract (unit form)

Extract a field from a date/time value. For example, (:extract :month (:now)).

sql-op :case (&rest clauses)

A conditional expression. Clauses should take the form (test value). If test is :else, an ELSE clause will be generated.

sql-op :between (n start end)

Test whether a value lies between two other values.

sql-op :between-symmetric (n start end)

Works like :between, except that the start value is not required to be less than the end value.

sql-op :dot (&rest names)

Can be used to combine multiple names into a name of the form A.B to refer to a column in a table, or a table in a schema. Note that you can also just use a symbol with a dot in it.

sql-op :type (form type)

Add a type declaration to a value, as in in "4.3::real". The second argument is not evaluated normally, but put through sql-type-name to get a type identifier.

sql-op :raw (string)

Insert a string as-is into the query. This can be useful for doing things that the syntax does not support, or to re-use parts of a query across multiple queries:

(let* ((test (sql (:and (:= 'foo 22) (:not-null 'bar))))
       (rows (query (:select '* :from 'baz :where (:raw test)))))
  (query (:delete-from 'baz :where (:raw test)))
  (do-stuff rows))

sql-op :select (&rest args)

Creates a select query. The arguments are split on the keywords found among them. The group of arguments immediately after :select is interpreted as the expressions that should be selected. After this, an optional :distinct may follow, which will cause the query to only select distinct rows, or alternatively :distinct-on followed by a group of row names. Next comes the optional keyword :from, followed by at least one table name and then any number of join statements. Join statements start with one of :left-join, :right-join, :inner-join, :outer-join or :cross-join, then a table name or subquery, then the keyword :on or :using, if applicable, and then a form. A join can be preceded by :natural (leaving off the :on clause) to use a natural join. After the joins an optional :where followed by a single form may occur. And finally :group-by and :having can optionally be specified. The first takes any number of arguments, and the second only one. An example:

(:select (:+ 'field-1 100) 'field-5
   :from (:as 'my-table 'x)
   :left-join 'your-table :on (:= 'x.field-2 'your-table.field-1)
   :where (:not-null 'a.field-3))

sql-op :limit (query amount &optional offset)

In S-SQL limit is not part of the select operator, but an extra operator that is applied to a query (this works out better when limiting the union or intersection of multiple queries, same for sorting). It limits the number of results to the amount given as the second argument, and optionally offsets the result by the amount given as the third argument.

sql-op :order-by (query &rest exprs)

Order the results of a query by the given expressions. See :desc for when you want to invert an ordering.

sql-op :over (form &rest args)

Over, partition-by and window are so-called window functions. A window function performs a calculation across a set of table rows that are somehow related to the current row.

(query (:select 'salary (:over (:sum 'salary))
                :from 'empsalary))

sql-op :partition-by (&rest args)

Args is a list of one or more columns to partition by, optionally followed by an :order-by clause.

(query (:select 'depname 'subdepname 'empno 'salary
                (:over (:avg 'salary)
                       (:partition-by 'depname 'subdepname))
                :from 'empsalary))

Note the use of :order-by without parens:

(query (:select 'depname 'empno 'salary
                (:over (:rank)
                       (:partition-by 'depname :order-by (:desc 'salary)))
                :from 'empsalary))
    

sql-op :window (form)

(query (:select (:over (:sum 'salary) 'w)
              (:over (:avg 'salary) 'w)
              :from 'empsalary :window
              (:as 'w (:partition-by 'depname :order-by (:desc 'salary)))))

sql-op :with (&rest args)

With provides a way to write auxillary statements for use in a larger query, often referred to as Common Table Expressions or CTEs.

(query (:with (:as 'upd
                 (:parens
                  (:update 'employees :set 'sales-count (:+ 'sales-count 1)
                           :where (:= 'id
                                      (:select 'sales-person
                                               :from 'accounts
                                               :where (:= 'name "Acme Corporation")))
                           :returning '*)))
            (:insert-into 'employees-log
                          (:select '* 'current-timestamp :from
                 'upd))))

sql-op :with-recursive (&rest args)

Recursive modifier to a WITH statement, allowing the query to refer to its own output.

(query (:with-recursive
      (:as (:t1 'n)
           (:union-all (:values 1)
                       (:select (:+ 'n 1)
                                :from 't1
                                :where (:< 'n 100))))
      (:select (:sum 'n) :from 't1)))

(query (:with-recursive
      (:as (:included_parts 'sub-part 'part 'quantity)
           (:union-all
            (:select 'sub-part 'part 'quantity
                     :from 'parts
                     :where (:= 'part "our-product"))
            (:select 'p.sub-part 'p.part 'p.quantity
                     :from (:as 'included-parts 'pr)
                     (:as 'parts 'p)
                     :where (:= 'p.part 'pr.sub-part) )))
      (:select 'sub-part (:as (:sum 'quantity) 'total-quantity)
               :from 'included-parts
               :group-by 'sub-part)))

(query (:with-recursive
      (:as (:search-graph 'id 'link 'data 'depth)
           (:union-all (:select 'g.id 'g.link 'g.data 1
                                :from (:as 'graph 'g))
                       (:select 'g.id 'g.link 'g.data (:+ 'sg.depth 1)
                                :from (:as 'graph 'g) (:as 'search-graph 'sg)
                                :where (:= 'g.id 'sg.link))))
      (:select '* :from 'search-graph)))

(query (:with-recursive
      (:as (:search-graph 'id 'link 'data'depth 'path 'cycle)
           (:union-all
            (:select 'g.id 'g.link 'g.data 1
                     (:[] 'g.f1 'g.f2) nil
                     :from (:as 'graph 'g))
            (:select 'g.id 'g.link 'g.data (:+ 'sg.depth 1)
                     (:|| 'path (:row 'g.f1 'g.f2))
                     (:= (:row 'g.f1 'g.f2)
                         (:any* 'path))
                     :from (:as 'graph 'g)
                     (:as 'search-graph 'sg)
                     :where (:and (:= 'g.id 'sg.link)
                                  (:not 'cycle)))))
      (:select '* :from 'search-graph)))

sql-op :for-update (query &key of nowait)

Locks the selected rows against concurrent updates. This will prevent the rows from being modified or deleted by other transactions until the current transaction ends. The :of keyword should be followed by one or more table names. If provided, Postgres will lock these tables instead of the ones detected in the select statement. The :nowait keyword should be provided by itself (with no argument attached to it), after all the :of arguments . If :nowait is provided, Postgres will throw an error if a table cannot be locked immediately, instead of pausing until it's possible.

(:for-update (:select :* :from 'foo 'bar 'baz) :of 'bar 'baz :nowait)

sql-op :for-share (query &key of nowait)

Similar to :for-update, except it acquires a shared lock on the table, allowing other transactions to perform :for-share selects on the locked tables.

sql-op :function (name (&rest arg-types) return-type stability body)

Create a stored procedure. The argument and return types are interpreted as type names and not evaluated. Stability should be one of :immutable, :stable, or :volatile (see the Postgres manual). For example, a function that gets foobars by id:

(:function 'get-foobar (integer) foobar :stable (:select '* :from 'foobar :where (:= 'id '$1)))

sql-op :insert-into (table &rest rest)

Insert a row into a table. When the second argument is :set, the other arguments should be alternating field names and values, otherwise it should be a :select form that will produce the values to be inserted. Example:

(:insert-into 'my-table :set 'field-1 42 'field-2 "foobar")

It is possible to add :returning, followed by a list of field names or expressions, at the end of the :insert-into form. This will cause the query to return the values of these expressions as a single row.

sql-op :update (table &rest rest)

Update values in a table. After the table name there should follow the keyword :set and any number of alternating field names and values, like for :insert-into. Next comes the optional keyword :from, followed by at least one table name and then any number of join statements, like for :select. After the joins, an optional :where keyword followed by the condition, and :returning keyword followed by a list of field names or expressions indicating values to be returned as query result.

sql-op :delete-from (table &rest rest)

Delete rows from the named table. Can be given a :where argument followed by a condition, and a :returning argument, followed by one or more expressions that should be returned for every deleted row.

sql-op :create-table (name (&rest columns) &rest options)

Create a new table. After the table name a list of column definitions follows, which are lists that start with a name, followed by one or more of the following keyword arguments:

:type
This one is required. It specifies the type of the column. Use a type like (or db-null integer) to specify a column that may have NULL values.
:default
Provides a default value for the field.
:unique
If this argument is non-nil, the values of the column must be unique.
:primary-key
When non-nil, the column is a primary key of the table.
:check
Adds a constraint to this column. The value provided for this argument must be an S-SQL expression that returns a boolean value. It can refer to other columns in the table if needed.
:references
Adds a foreign key constraint to this table. The argument provided must be a list of the form (target &optional on-delete on-update). When target is a symbol, it names the table to whose primary key this constraint refers. When it is a list, its first element is the table, and its second element the column within that table that the key refers to. on-delete and on-update can be used to specify the actions that must be taken when the row that this key refers to is deleted or changed. Allowed values are :restrict, :set-null, :set-default, :cascade, and :no-action.

After the list of columns, zero or more extra options (table constraints) can be specified. These are lists starting with one of the following keywords:

:check
Adds a constraint to the table. Takes a single S-SQL expression that produces a boolean as its argument.
:primary-key
Specifies a primary key for the table. The arguments to this option are the names of the columns that this key consists of.
:unique
Adds a unique constraint to a group of columns. Again, the arguments are a list of symbols that indicate the relevant columns.
:foreign-key
Create a foreign key. The arguments should have the form (columns target &optional on-delete on-update), where columns is a list of columns that are used by this key, while the rest of the arguments have the same meaning as they have in the :references option for columns.

Every list can start with :constraint name to create a specifically named constraint.

Note that, unlike most other operators, :create-table expects most of its arguments to be unquoted symbols. The exception to this is the value of :check constraints: These must be normal S-SQL expressions, which means that any column names they contain should be quoted. When programmatically generating table definitions, sql-compile is usually more practical than the sql macro.

Here is an example of a :create-table form:

(:create-table enemy
  ((name :type string :primary-key t)
   (age :type integer)
   (address :type (or db-null string) :references (important-addresses :cascade :cascade))
   (fatal-weakness :type text :default "None")
   (identifying-color :type (string 20) :unique t))
  (:foreign-key (identifying-color) (colors name))
  (:constraint enemy-age-check :check (:> 'age 12))

sql-op:alter-table (name action &rest args)

Alters named table. Currently changing a column's data type is not supported. The meaning of args depends on action:

:add-column
Adds column to table. args should be a column in the same form as for :create-table.
:drop-column
Drops a column from the table.
:add-constraint
Adds a named constraint to the table.
:drop-constraint
Drops constraint. First of args should name a constraint to be dropped; second, optional argument specifies behaviour regarding objects dependent on the constraint and it may equal :cascade or :restrict.
:add
Adds an unnamed constraint to table. args should be a constraint in the same form as for :create-table. (This is for backwards-compatibility, you should use named constraints.)

Here is an example using the table defined above:

(:alter-table enemy :drop-constraint enemy-age-check)
(:alter-table enemy :add-constraint enemy-age-check :check (:> 'age 21))

sql-op :drop-table (name)

Drops the named table. You may optionally pass :if-exists before the name to suppress the error message.

sql-op :create-index (name &rest args)

Create an index on a table. After the name of the index the keyword :on should follow, with the table name after it. Then the keyword :fields, followed by one or more column names. Optionally, a :where clause with a condition can be added at the end to make a partial index.

sql-op :create-unique-index (name &rest args)

Works like :create-index, except that the index created is unique.

sql-op :drop-index (name)

Drop an index. Takes an :if-exists argument like :drop-table.

sql-op :create-sequence (name &key increment min-value max-value start cache cycle)

Create a sequence with the given name. The rest of the arguments control the way the sequence selects values.

sql-op :drop-sequence (name)

Drop a sequence. You may pass :if-exists as an extra first argument.

sql-op :create-view (name query)

Create a view from an S-SQL-style query.

sql-op :drop-view (name)

Drop a view. Takes optional :if-exists argument.

sql-op :set-constraints (state &rest constraints)

Configure whether deferrable constraints should be checked when a statement is executed, or when the transaction containing that statement is completed. The provided state must be either :immediate, indicating the former, or :deferred, indicating the latter. The constraints must be either the names of the constraints to be configured, or unspecified, indicating that all deferrable constraints should be thus configured.

sql-op :listen (channel)

Tell the server to listen for notification events on channel channel, a string, on the current connection.

sql-op :unlisten (channel)

Stop listening for events on channel.

sql-op :notify (channel &optional payload)

Signal a notification event on channel channel, a string. The optional payload string can be used to send additional event information to the listeners.

Symbol-index