Postmodern examples

Postmodern is a Common Lisp library for interacting with PostgreSQL databases.

Postmodern has very good documentation, but I always like to have examples. This is intended to be an introduction to the library for beginners using examples. This is not intended to be an sql tutorial, a common lisp tutorial, a postgresql tutorial or a replacement for the postmodern s-sql manual. I am mainly focusing on s-sql, which is a domain specific language (dsl) written in lisp, covering a substantial part of sql and directed solely at using the postgresql database. Note: This article is about to undergo a substantial re-write. If you have any suggestions, please email them to me.

Table of Contents

• Postmodern is a Common Lisp library for interacting with PostgreSQL databases.
  • Database Tables Used in Examples
• Connecting and unconnecting
• Data Types
• Intro to S-SQL
  • Profiling S-SQL
    • Simple Database Queries
    • Sql-escape
    • Sql-compile
    • Return Types
    • Keywords
  • As or Alias
  • Concatenating Columns
• SQL Operators, Key Words, Etc
  • [Special Characters][A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [Q] [R] [S] [T] [U] [V] [W] [X] [Y] [Z]
    • sql-op :+, :*, :%, :&, :|, :||, :=, :/, :!=, :<, :<>, :>, :<=, :>=, :^, *, /, , <, >, alias (see as), and, any, array, array_agg, as, avg, between, case, cast, coalesce, commit, constraints, count, create-index,current-date, current-timestamp, datatypes, date-part,date-trunc,desc, delete, distinct, distinct-on, enum, except, :except-all exists, extract, generate-series, group-by, having, in, insert, insert-into :intersect, :intersect-all, interval, is-null, join cross-join, join inner-join, join left-join, join outer-join, like, limit, max, min, not,now, null, nullif, on, on-conflict, or, order-by, over, partition-by, raw, returning,rollback, rollup, select, set, some, string_agg, sum, threads, transactions, truncate, union, union-all, unique, update, upsert, using, when, while, window, with, with-recursive
  • Postgresql Functions
    • Combining Postgresql Functions
  • More complex examples - functions examining the database itself
  • Time Functions (Updated 3 December 2016)
• Parametized Statements
• Prepared Statements
  • Raw SQL Statements (Sometimes you need conditionals or items not in s-sql)
  • Building Conditional Queries, see examples in Raw above
• Calling Postgresql Stored Functions and Procedures
• Many to Many Calls
• Doquery
  • DAO
    • Selecting Schemas
    • Nested Classes
    • Returning Data Objects (DAOs)
    • Selecting by DAO
    • Using a DAO, replacing linked ids with actual names
    • Getting keys out of DAO
  • Insert
    • First, without s-sql
    • With s-sql
    • With DAOs
    • Multiple Row Inserts
• Update
• Delete
• Table management
  • Create Table with Dao
  • Create a table with Deftable
  • Create a table without a dao class
  • Alter Table
• Arrays
  • Text Searching
    • Introductions to indexing and searching
    • Text Indexing
    • Searching
    • Ranking
    • Snippets with the search text in bold
• Sequences
• Exploring Databases