This is the postmodern Reference Manual, version 1.33.8, generated automatically by Declt version 4.0 beta 2 "William Riker" on Sun Sep 15 06:23:42 2024 GMT+0.
postmodern/postmodern.asdcl-postgres/cl-postgres.asds-sql/s-sql.asdpostmodern/postmodern/package.lisppostmodern/postmodern/config.lisppostmodern/postmodern/connect.lisppostmodern/postmodern/json-encoder.lisppostmodern/postmodern/query.lisppostmodern/postmodern/prepare.lisppostmodern/postmodern/roles.lisppostmodern/postmodern/util.lisppostmodern/postmodern/transaction.lisppostmodern/postmodern/namespace.lisppostmodern/postmodern/execute-file.lisppostmodern/postmodern/table.lisppostmodern/postmodern/deftable.lispcl-postgres/cl-postgres/package.lispcl-postgres/cl-postgres/features.lispcl-postgres/cl-postgres/config.lispcl-postgres/cl-postgres/oid.lispcl-postgres/cl-postgres/errors.lispcl-postgres/cl-postgres/data-types.lispcl-postgres/cl-postgres/sql-string.lispcl-postgres/cl-postgres/trivial-utf-8.lispcl-postgres/cl-postgres/strings-utf-8.lispcl-postgres/cl-postgres/communicate.lispcl-postgres/cl-postgres/messages.lispcl-postgres/cl-postgres/ieee-floats.lispcl-postgres/cl-postgres/interpret.lispcl-postgres/cl-postgres/saslprep.lispcl-postgres/cl-postgres/scram.lispcl-postgres/cl-postgres/protocol.lispcl-postgres/cl-postgres/public.lispcl-postgres/cl-postgres/bulk-copy.lisps-sql/s-sql/package.lisps-sql/s-sql/config.lisps-sql/s-sql/s-sql.lispThe main system appears first, followed by any subsystem dependency.
postmodernPostgreSQL programming API
Sabra Crolleton <sabra.crolleton@gmail.com>
Marijn Haverbeke <marijnh@gmail.com>
zlib
1.33.8
alexandria (system).
cl-postgres (system).
s-sql (system).
global-vars (system).
split-sequence (system).
uiop (system).
closer-mop (system)., for feature :postmodern-use-mop
bordeaux-threads (system)., for feature :postmodern-thread-safe
postmodern (module).
cl-postgresLow-level client library for PostgreSQL
Sabra Crolleton <sabra.crolleton@gmail.com>
Marijn Haverbeke <marijnh@gmail.com>
zlib
1.33.8
md5 (system).
split-sequence (system).
ironclad (system).
cl-base64 (system).
uax-15 (system).
usocket (system)., for feature (:or :allegro :ccl :clisp :genera :armedbear :cmucl :lispworks :ecl)
sb-bsd-sockets (system)., required, for feature :sbcl
cl-postgres (module).
s-sqlLispy DSL for SQL
Sabra Crolleton <sabra.crolleton@gmail.com>
Marijn Haverbeke <marijnh@gmail.com>
zlib
1.33.8
cl-postgres (system).
alexandria (system).
s-sql (module).
Modules are listed depth-first from the system components tree.
postmodern/postmodernpostmodern (system).
package.lisp (file).
config.lisp (file).
connect.lisp (file).
json-encoder.lisp (file).
query.lisp (file).
prepare.lisp (file).
roles.lisp (file).
util.lisp (file).
transaction.lisp (file).
namespace.lisp (file).
execute-file.lisp (file).
table.lisp (file).
deftable.lisp (file).
cl-postgres/cl-postgrescl-postgres (system).
package.lisp (file).
features.lisp (file).
config.lisp (file).
oid.lisp (file).
errors.lisp (file).
data-types.lisp (file).
sql-string.lisp (file).
trivial-utf-8.lisp (file).
strings-utf-8.lisp (file).
communicate.lisp (file).
messages.lisp (file).
ieee-floats.lisp (file).
interpret.lisp (file).
saslprep.lisp (file).
scram.lisp (file).
protocol.lisp (file).
public.lisp (file).
bulk-copy.lisp (file).
s-sql/s-sqls-sql (system).
package.lisp (file).
config.lisp (file).
s-sql.lisp (file).
Files are sorted by type and then listed depth-first from the systems components trees.
postmodern/postmodern.asdcl-postgres/cl-postgres.asds-sql/s-sql.asdpostmodern/postmodern/package.lisppostmodern/postmodern/config.lisppostmodern/postmodern/connect.lisppostmodern/postmodern/json-encoder.lisppostmodern/postmodern/query.lisppostmodern/postmodern/prepare.lisppostmodern/postmodern/roles.lisppostmodern/postmodern/util.lisppostmodern/postmodern/transaction.lisppostmodern/postmodern/namespace.lisppostmodern/postmodern/execute-file.lisppostmodern/postmodern/table.lisppostmodern/postmodern/deftable.lispcl-postgres/cl-postgres/package.lispcl-postgres/cl-postgres/features.lispcl-postgres/cl-postgres/config.lispcl-postgres/cl-postgres/oid.lispcl-postgres/cl-postgres/errors.lispcl-postgres/cl-postgres/data-types.lispcl-postgres/cl-postgres/sql-string.lispcl-postgres/cl-postgres/trivial-utf-8.lispcl-postgres/cl-postgres/strings-utf-8.lispcl-postgres/cl-postgres/communicate.lispcl-postgres/cl-postgres/messages.lispcl-postgres/cl-postgres/ieee-floats.lispcl-postgres/cl-postgres/interpret.lispcl-postgres/cl-postgres/saslprep.lispcl-postgres/cl-postgres/scram.lispcl-postgres/cl-postgres/protocol.lispcl-postgres/cl-postgres/public.lispcl-postgres/cl-postgres/bulk-copy.lisps-sql/s-sql/package.lisps-sql/s-sql/config.lisps-sql/s-sql/s-sql.lispcl-postgres/cl-postgres.asdcl-postgres (system).
*string-file* (special variable).
*unicode* (special variable).
postmodern/postmodern/config.lisppostmodern (module).
*allow-overwriting-prepared-statements* (special variable).
*default-use-ssl* (special variable).
*enforce-parameter-types* (special variable).
*ignore-unknown-columns* (special variable).
*isolation-level* (special variable).
*max-pool-size* (special variable).
*alter-all-default-editor-privileges* (special variable).
*alter-all-default-select-privileges* (special variable).
*character-sets* (special variable).
*collations* (special variable).
*custom-column-writers* (special variable).
*disallowed-role-names* (special variable).
*execute-privileges-list* (special variable).
*identifier-name-to-key* (special variable).
*json-aggregate-context* (special variable).
*json-aggregate-first* (special variable).
*json-identifier-name-to-lisp* (special variable).
*json-list-encoder-fn* (special variable).
*json-output* (special variable).
*json-symbols-package* (special variable).
*lisp-identifier-name-to-json* (special variable).
*result-styles* (special variable).
*schema-path* (special variable).
*transaction-level* (special variable).
+json-lisp-symbol-tokens+ (special variable).
postmodern/postmodern/connect.lisppackage.lisp (file).
config.lisp (file).
postmodern (module).
*database* (special variable).
call-with-connection (function).
clear-connection-pool (function).
connect (function).
connect-toplevel (function).
connected-p (function).
disconnect (generic function).
disconnect-toplevel (function).
reconnect (generic function).
with-connection (macro).
*connection-pools* (special variable).
*pool-lock* (special variable).
connection-pool-type (reader method).
(setf connection-pool-type) (writer method).
get-from-pool (function).
pooled-database-connection (class).
with-pool-lock (macro).
postmodern/postmodern/json-encoder.lisppackage.lisp (file).
config.lisp (file).
postmodern (module).
encode-json-to-string (function).
as-array-member (macro).
as-object-member (macro).
camel-case-split (function).
camel-case-to-lisp (function).
camel-case-transform (function).
camel-case-transform-all-caps (function).
encode-array-member (function).
encode-json (generic function).
encode-json-alist (function).
encode-json-alist-to-string (function).
encode-json-list-explicit-encoder (function).
encode-json-list-guessing-encoder (function).
encode-json-plist (function).
encode-json-plist-to-string (function).
encode-object-member (function).
json-bool (function).
json-intern (function).
json-or-null (function).
lisp-to-camel-case (function).
local-time-timestamp-string (function).
map-slots (function).
next-aggregate-member (function).
safe-json-intern (function).
simple-date-date-string (function).
simple-date-interval-string (function).
simple-date-time-of-day-string (function).
simple-date-timestamp-string (function).
simplified-camel-case-to-lisp (function).
stream-array-member-encoder (function).
stream-object-member-encoder (function).
unencodable-value-error (function).
unencodable-value-error (condition).
unencodable-value-error-context (reader method).
(setf unencodable-value-error-context) (writer method).
unknown-symbol-error (function).
unknown-symbol-error (condition).
unknown-symbol-error-datum (reader method).
(setf unknown-symbol-error-datum) (writer method).
use-explicit-encoder (function).
use-guessing-encoder (function).
with-aggregate (macro).
with-array (macro).
with-explicit-encoder (macro).
with-guessing-encoder (macro).
with-local-encoder (macro).
with-object (macro).
with-substitute-printed-representation-restart (macro).
write-json-chars (function).
write-json-number (function).
write-json-string (function).
postmodern/postmodern/query.lispconnect.lisp (file).
json-encoder.lisp (file).
config.lisp (file).
postmodern (module).
*class-finalize-lock* (special variable).
all-rows (macro).
array-hash-row-reader (function).
column-row-reader (function).
dao-spec-for-format (function).
json-row-array-reader (function).
json-row-reader (function).
reader-for-format (function).
real-query (function).
single-row (macro).
single-row! (macro).
symbol-alist-row-reader (function).
symbol-plist-row-reader (function).
postmodern/postmodern/prepare.lispquery.lisp (file).
config.lisp (file).
postmodern (module).
cancel-backend (function).
defprepared (macro).
defprepared-with-names (macro).
drop-prepared-statement (function).
find-postgresql-prepared-statement (function).
find-postmodern-prepared-statement (function).
get-pid (function).
get-pid-from-postmodern (function).
list-postmodern-prepared-statements (function).
list-prepared-statements (function).
prepare (macro).
prepared-statement-exists-p (function).
reset-prepared-statement (function).
terminate-backend (function).
ensure-prepared (function).
find-postgresql-prepared-statement-by-query (function).
generate-prepared (function).
mismatched-parameter-types (condition).
next-statement-id (function).
parameter-types (reader method).
prepared-statement-types (reader method).
postmodern/postmodern/roles.lispquery.lisp (file).
config.lisp (file).
postmodern (module).
alter-role-search-path (function).
change-password (function).
create-role (function).
drop-role (function).
grant-admin-permissions (function).
grant-editor-permissions (function).
grant-readonly-permissions (function).
grant-role-permissions (function).
list-database-users (function).
list-role-permissions (function).
list-roles (function).
revoke-all-on-table (function).
role-exists-p (function).
create-role-helper (function).
database-parameters-to-list (function).
grant-role-permissions-helper (function).
remove-whitespace (function).
schema-parameters-to-list (function).
whitespace-in-string (function).
postmodern/postmodern/util.lispquery.lisp (file).
roles.lisp (file).
config.lisp (file).
postmodern (module).
add-comment (function).
change-toplevel-database (function).
coalesce (function).
column-exists-p (function).
create-database (function).
create-index (function).
create-sequence (function).
current-database (function).
database-exists-p (function).
database-size (function).
database-version (function).
describe-constraint (function).
describe-foreign-key-constraints (function).
describe-triggers (function).
describe-views (function).
drop-database (function).
drop-index (function).
drop-sequence (function).
drop-table (function).
find-comments (function).
find-primary-key-info (function).
get-all-table-comments (function).
get-column-comment (function).
get-column-comments (function).
get-database-comment (function).
get-schema-comment (function).
get-table-comment (function).
get-table-oid (function).
index-exists-p (function).
list-all-constraints (function).
list-all-tables (function).
list-available-collations (function).
list-available-extensions (function).
list-available-types (function).
list-check-constraints (function).
list-columns (function).
list-columns-with-types (function).
list-connections (function).
list-database-access-rights (function).
list-database-functions (function).
list-databases (function).
list-detailed-triggers (function).
list-foreign-keys (function).
list-index-definitions (function).
list-indexed-column-and-attributes (function).
list-indices (function).
list-installed-extensions (function).
list-role-accessible-databases (function).
list-sequences (function).
list-table-indices (function).
list-table-sizes (function).
list-tables (function).
list-tables-in-schema (function).
list-tablespaces (function).
list-templates (function).
list-triggers (function).
list-unique-or-primary-constraints (function).
list-views (function).
load-uuid-extension (function).
num-records-in-database (function).
postgres-array-string-to-array (function).
postgres-array-string-to-list (function).
postgresql-version (function).
rename-column (function).
rename-table (function).
sequence-exists-p (function).
sequence-next (function).
split-fully-qualified-tablename (function).
table-description (function).
table-description-menu (function).
table-description-plus (function).
table-exists-p (function).
table-size (function).
text (reader method).
text (reader method).
valid-sql-identifier-p (function).
view-exists-p (function).
character-set-exists-p (function).
code-char-0-p (function).
collation-exists-p (function).
copy-from-csv (function).
generate-uuid (function).
inconsistent-schema-name (condition).
invalid-database-name (condition).
make-exists-query (macro).
make-keyword (function).
make-list-query (macro).
replace-non-alphanumeric-chars (function).
table-parameter-helper (function).
table-schema-names (function).
to-identifier (function).
valid-sql-character-p (function).
postmodern/postmodern/transaction.lispquery.lisp (file).
config.lisp (file).
postmodern (module).
*current-logical-transaction* (special variable).
abort-hooks (generic reader).
(setf abort-hooks) (writer method).
abort-logical-transaction (generic function).
abort-transaction (function).
commit-hooks (generic reader).
(setf commit-hooks) (writer method).
commit-logical-transaction (generic function).
commit-transaction (function).
ensure-transaction (macro).
ensure-transaction-with-isolation-level (macro).
release-savepoint (function).
retry-transaction (function).
rollback-savepoint (function).
rollback-transaction (function).
with-logical-transaction (macro).
with-savepoint (macro).
with-transaction (macro).
begin-transaction (function).
call-with-ensured-transaction (function).
call-with-logical-transaction (function).
call-with-savepoint (function).
call-with-transaction (function).
isolation-level-p (function).
savepoint-connection (reader method).
savepoint-handle (class).
savepoint-name (reader method).
savepoint-open-p (reader method).
(setf savepoint-open-p) (writer method).
transaction-connection (reader method).
transaction-handle (class).
transaction-open-p (reader method).
(setf transaction-open-p) (writer method).
postmodern/postmodern/namespace.lispquery.lisp (file).
config.lisp (file).
postmodern (module).
create-schema (function).
drop-schema (function).
get-search-path (function).
list-schemas (function).
schema-exists-p (function).
set-search-path (function).
with-schema (macro).
do-with-schema (function).
postmodern/postmodern/execute-file.lispquery.lisp (file).
config.lisp (file).
postmodern (module).
execute-file (function).
parse-queries (function).
print-object (method).
read-queries (function).
base-filename (reader method).
comment-parser (structure).
comment-parser-buffer (reader).
(setf comment-parser-buffer) (writer).
comment-parser-p (function).
comment-parser-state (reader).
(setf comment-parser-state) (writer).
comment-parser-stream (reader).
(setf comment-parser-stream) (writer).
copy-comment-parser (function).
copy-parser (function).
disallowed-tag-char-p (function).
extend-current-tag (method).
filename (reader method).
find-included-filename (function).
format-current-tag (method).
line-has-includes (function).
make-comment-parser (function).
make-parser (function).
maybe-close-tags (method).
meta-cmd (reader method).
missing-i-file (condition).
parse-comments (function).
parse-query (function).
parser (structure).
parser-filename (reader).
(setf parser-filename) (writer).
parser-p (function).
parser-state (reader).
(setf parser-state) (writer).
parser-stream (reader).
(setf parser-stream) (writer).
parser-tags (reader).
(setf parser-tags) (writer).
pop-current-tag (method).
push-new-tag (method).
read-sql-file (function).
remove-sql-comments (function).
reset-state (method).
single-line-comment-scanner (special variable).
postmodern/postmodern/table.lisp:postmodern-use-mop
util.lisp (file).
transaction.lisp (file).
query.lisp (file).
config.lisp (file).
postmodern (module).
col-type-text-p (function).
compute-effective-slot-definition (method).
dao-class (class).
dao-exists-p (generic function).
dao-keys (generic function).
dao-table-definition (function).
dao-table-name (function).
define-dao-finalization (macro).
delete-dao (generic function).
direct-slot-definition-class (method).
do-query-dao (macro).
do-select-dao (macro).
effective-slot-definition-class (method).
fetch-defaults (generic function).
finalize-inheritance (method).
find-col-type (function).
find-dao-column-slot (function).
find-primary-key-column (generic function).
get-dao (generic function).
insert-dao (generic function).
make-dao (generic function).
query-dao (macro).
save-dao (function).
save-dao/transaction (function).
select-dao (macro).
shared-initialize (method).
shared-initialize (method).
update-dao (generic function).
upsert-dao (generic function).
validate-superclass (method).
with-column-writers (macro).
%eval (function).
*direct-column-slot* (special variable).
build-dao-methods (function).
collect-export-functions (function).
collect-import-functions (function).
column-check (reader method).
column-collate (reader method).
column-default (reader method).
column-export (reader method).
column-identity (reader method).
column-import (reader method).
column-interval (reader method).
column-primary-key (reader method).
column-references (reader method).
column-type (reader method).
column-unique (reader method).
dao-column-fields (function).
dao-column-map (reader method).
dao-column-slots (function).
dao-from-fields (function).
dao-row-reader (function).
dao-row-reader-with-body (macro).
dao-superclasses (function).
direct-column-slot (class).
direct-keys (reader method).
effective-column-slot (class).
field-name-to-slot-name (function).
find-export-function (function).
find-import-function (function).
generate-dao-query (function).
ghost (reader method).
list-to-column (function).
query-dao% (function).
set-to-class (function).
slot-column (reader method).
slot-definition-name-as-string (function).
slot-sql-name (reader method).
postmodern/postmodern/deftable.lispquery.lisp (file).
table.lisp (file)., for feature :postmodern-use-mop
postmodern (module).
!dao-def (function).
!foreign (function).
!index (function).
!unique (function).
!unique-index (function).
*table-name* (special variable).
*table-symbol* (special variable).
create-all-tables (function).
create-package-tables (function).
create-table (function).
deftable (macro).
*tables* (special variable).
add-table-definition (function).
flat-table-name (function).
cl-postgres/cl-postgres/package.lispcl-postgres (module).
*optimize* (special variable).
cl-postgres/cl-postgres/config.lispcl-postgres (module).
*on-evidence-of-man-in-the-middle-attack* (special variable).
*query-callback* (special variable).
*query-log* (special variable).
*retry-connect-delay* (special variable).
*retry-connect-times* (special variable).
*silently-truncate-ratios* (special variable).
cl-postgres/cl-postgres/oid.lisppackage.lisp (file).
config.lisp (file).
cl-postgres (module).
+abstime+ (constant).
+aclitem+ (constant).
+any+ (constant).
+any-array+ (constant).
+anyelement+ (constant).
+anyenum+ (constant).
+anynon-array+ (constant).
+anyrange+ (constant).
+bit+ (constant).
+bit-array+ (constant).
+bool+ (constant).
+bool-array+ (constant).
+box+ (constant).
+box-array+ (constant).
+bpchar+ (constant).
+bpchar-array+ (constant).
+bytea+ (constant).
+bytea-array+ (constant).
+cash+ (constant).
+char+ (constant).
+char-array+ (constant).
+cid+ (constant).
+cidr+ (constant).
+circle+ (constant).
+cstring+ (constant).
+cstring-array+ (constant).
+date+ (constant).
+date-array+ (constant).
+evttrigger+ (constant).
+fdw-handler+ (constant).
+float4+ (constant).
+float4-array+ (constant).
+float8+ (constant).
+float8-array+ (constant).
+gtsvector+ (constant).
+index-am-handler+ (constant).
+inet+ (constant).
+int2+ (constant).
+int2-array+ (constant).
+int2vector+ (constant).
+int4+ (constant).
+int4-array+ (constant).
+int4range+ (constant).
+int8+ (constant).
+int8-array+ (constant).
+internal+ (constant).
+interval+ (constant).
+interval-array+ (constant).
+json+ (constant).
+jsonb+ (constant).
+language-handler+ (constant).
+line+ (constant).
+lseg+ (constant).
+lseg-array+ (constant).
+lsn+ (constant).
+macaddr+ (constant).
+name+ (constant).
+name-array+ (constant).
+numeric+ (constant).
+numeric-array+ (constant).
+oid+ (constant).
+oid-array+ (constant).
+oid-vector+ (constant).
+opaque+ (constant).
+path+ (constant).
+pgddlcommand+ (constant).
+pgnodetree+ (constant).
+point+ (constant).
+point-array+ (constant).
+polygon+ (constant).
+record+ (constant).
+record-array+ (constant).
+refcursor+ (constant).
+regclass+ (constant).
+regconfig+ (constant).
+regdictionary+ (constant).
+regnamespace+ (constant).
+regoper+ (constant).
+regoperator+ (constant).
+regproc+ (constant).
+regprocedure+ (constant).
+regrole+ (constant).
+regtype+ (constant).
+regtype-array+ (constant).
+reltime+ (constant).
+text+ (constant).
+text-array+ (constant).
+tid+ (constant).
+time+ (constant).
+time-array+ (constant).
+timestamp+ (constant).
+timestamp-array+ (constant).
+timestamptz+ (constant).
+timestamptz-array+ (constant).
+timetz+ (constant).
+tinterval+ (constant).
+trigger+ (constant).
+tsm-handler+ (constant).
+tsquery+ (constant).
+tsvector+ (constant).
+unknown+ (constant).
+uuid+ (constant).
+v-oid+ (constant).
+varbit+ (constant).
+varbit-array+ (constant).
+varchar+ (constant).
+varchar-array+ (constant).
+xid+ (constant).
+xml+ (constant).
cl-postgres/cl-postgres/errors.lisppackage.lisp (file).
cl-postgres (module).
admin-shutdown (condition).
cannot-connect-now (condition).
check-violation (condition).
columns-error (condition).
connection-does-not-exist (condition).
connection-exception (condition).
connection-failure (condition).
crash-shutdown (condition).
data-exception (condition).
database-connection-error (condition).
database-connection-lost (condition).
database-error (condition).
database-error-cause (reader method).
database-error-code (reader method).
database-error-constraint-name (function).
database-error-detail (reader method).
database-error-extract-name (function).
database-error-message (reader method).
(setf database-error-message) (writer method).
database-error-query (reader method).
database-socket-error (condition).
db-division-by-zero (condition).
deadlock-detected (condition).
duplicate-alias (condition).
duplicate-column (condition).
duplicate-cursor (condition).
duplicate-database (condition).
duplicate-function (condition).
duplicate-object (condition).
duplicate-prepared-statement (condition).
duplicate-schema (condition).
duplicate-table (condition).
feature-not-supported (condition).
floating-point-exception (condition).
foreign-key-violation (condition).
insufficient-privilege (condition).
insufficient-resources (condition).
integrity-violation (condition).
internal-error (condition).
invalid-datetime-format (condition).
invalid-sql-statement-name (condition).
lock-not-available (condition).
log-query (function).
not-null-violation (condition).
numeric-value-out-of-range (condition).
object-in-use (condition).
object-state-error (condition).
operator-intervention (condition).
program-limit-exceeded (condition).
protocol-violation (condition).
query-canceled (condition).
restrict-violation (condition).
serialization-failure (condition).
server-shutdown (condition).
sqlclient-unable-to-establish-sqlconnection (condition).
sqlserver-rejected-establishment-of-sqlconnection (condition).
statement-completion-unknown (condition).
syntax-error-or-access-violation (condition).
system-error (condition).
transaction-integrity-constraint-violation (condition).
transaction-resolution-unknown (condition).
transaction-rollback (condition).
undefined-column (condition).
undefined-table (condition).
unique-violation (condition).
*current-query* (special variable).
*error-table* (special variable).
database-error-context (reader method).
database-error-hint (reader method).
database-error-position (reader method).
deferror (macro).
get-error-type (function).
invalid-byte-sequence (condition).
unrecognized-configuration-parameter (condition).
with-query (macro).
wrap-socket-error (function).
cl-postgres/cl-postgres/data-types.lisppackage.lisp (file).
config.lisp (file).
cl-postgres (module).
int4 (type).
int8 (type).
oid-types-match-p (function).
param-to-oid (function).
parameter-list-types (function).
parameter-lists-match-oid-types-p (function).
types-match-p (function).
uuid-string (type).
uuip-p (function).
get-int-size (function).
int-to-vector (function).
int16-to-vector (function).
int2 (type).
int2-array (type).
int2-array-p (function).
int2p (function).
int32-to-vector (function).
int4-array (type).
int4-array-p (function).
int4p (function).
int64-to-vector (function).
int8-array (type).
int8-array-p (function).
int8-to-vector (function).
int8p (function).
text-array (type).
text-array-p (function).
uuid-to-byte-array (function).
cl-postgres/cl-postgres/sql-string.lisppackage.lisp (file).
config.lisp (file).
data-types.lisp (file).
cl-postgres (module).
serialize-for-postgres (generic function).
to-sql-string (generic function).
escape-bytes (function).
write-quoted (function).
write-ratio-as-floating-point (function).
cl-postgres/cl-postgres/trivial-utf-8.lisppackage.lisp (file).
config.lisp (file).
cl-postgres (module).
read-utf-8-string (function).
string-to-utf-8-bytes (function).
utf-8-byte-length (function).
utf-8-bytes-to-string (function).
utf-8-decoding-error (condition).
utf-8-group-size (function).
write-utf-8-bytes (function).
*optimize* (special variable).
as-utf-8-bytes (macro).
get-utf-8-character (function).
utf-8-string-length (function).
cl-postgres/cl-postgres/strings-utf-8.lisppackage.lisp (file).
trivial-utf-8.lisp (file).
config.lisp (file).
cl-postgres (module).
*client-encoding* (special variable).
enc-byte-length (function).
enc-read-string (function).
enc-string-bytes (function).
enc-write-string (function).
cl-postgres/cl-postgres/communicate.lispstrings-utf-8.lisp (file).
sql-string.lisp (file).
config.lisp (file).
cl-postgres (module).
ensure-socket-is-closed (function).
integer-reader (macro).
integer-reader-name (function).
integer-writer (macro).
integer-writer-name (function).
read-bytes (function).
read-int1 (function).
read-int2 (function).
read-int4 (function).
read-int8 (function).
read-simple-str (function).
read-str (function).
read-uint1 (function).
read-uint2 (function).
read-uint4 (function).
read-uint8 (function).
skip-bytes (function).
skip-str (function).
write-bytes (function).
write-int1 (function).
write-int2 (function).
write-int4 (function).
write-int8 (function).
write-str (function).
write-uint1 (function).
write-uint2 (function).
write-uint4 (function).
write-uint8 (function).
cl-postgres/cl-postgres/messages.lispcommunicate.lisp (file).
config.lisp (file).
cl-postgres (module).
bind-message (function).
bytes-to-hex-string (function).
close-prepared-message (function).
copy-data-message (function).
copy-done-message (function).
copy-fail-message (function).
define-message (macro).
describe-prepared-message (function).
flush-message (function).
formats-to-bytes (function).
go-binary-list-p (function).
go-binary-p (function).
gss-auth-buffer-message (function).
md5-password (function).
md5-password-message (function).
parse-message (function).
parse-message-binary-parameters (function).
plain-password-message (function).
query-message (function).
scram-cont-message (function).
scram-type-message (function).
simple-bind-message (function).
simple-describe-message (function).
simple-execute-message (function).
simple-parse-message (function).
ssl-request-message (function).
startup-message (function).
sync-message (function).
terminate-message (function).
cl-postgres/cl-postgres/ieee-floats.lispconfig.lisp (file).
cl-postgres (module).
decode-float32 (function).
decode-float64 (function).
encode-float32 (function).
encode-float64 (function).
make-float-converters (macro).
cl-postgres/cl-postgres/interpret.lispoid.lisp (file).
communicate.lisp (file).
ieee-floats.lisp (file).
config.lisp (file).
cl-postgres (module).
*read-row-values-as-binary* (special variable).
*sql-readtable* (special variable).
copy-sql-readtable (function).
default-sql-readtable (function).
set-sql-datetime-readers (function).
set-sql-reader (function).
with-binary-row-values (macro).
with-text-row-values (macro).
*default-sql-readtable* (special variable).
*timestamp-format* (special variable).
+seconds-in-day+ (constant).
+start-of-2000+ (constant).
binary-reader (macro).
default-date-reader (function).
default-interval-reader (function).
default-time-reader (function).
default-timestamp-reader (function).
define-interpreter (macro).
get-type-interpreter (function).
interpret-as-text (function).
interpret-usec-bits (function).
interpreter-binary-p (function).
interpreter-reader (function).
read-array-value (function).
read-binary-array-value (function).
read-binary-bits (function).
read-row-value (function).
set-date-reader (function).
set-interval-reader (function).
set-usec-reader (function).
type-interpreter (class).
type-interpreter-binary-reader (reader method).
(setf type-interpreter-binary-reader) (writer method).
type-interpreter-oid (reader method).
(setf type-interpreter-oid) (writer method).
type-interpreter-text-reader (reader method).
(setf type-interpreter-text-reader) (writer method).
type-interpreter-use-binary (reader method).
(setf type-interpreter-use-binary) (writer method).
cl-postgres/cl-postgres/saslprep.lisppackage.lisp (file).
config.lisp (file).
cl-postgres (module).
print-object (method).
saslprep-normalize (function).
string-mapped-to-nothing (function).
string-mapped-to-space (function).
string-printable-ascii-p (function).
*printable-ascii-chars* (special variable).
bad-char-error (function).
bad-char-error (condition).
bad-char-error-message (reader method).
(setf bad-char-error-message) (writer method).
bad-char-error-normalization-form (reader method).
(setf bad-char-error-normalization-form) (writer method).
bad-char-error-value (reader method).
(setf bad-char-error-value) (writer method).
char-mapped-to-nothing-p (function).
char-mapped-to-space-p (function).
char-printable-ascii-p (function).
code-point-printable-ascii-p (function).
cl-postgres/cl-postgres/scram.lisppackage.lisp (file).
messages.lisp (file).
errors.lisp (file).
saslprep.lisp (file).
trivial-utf-8.lisp (file).
config.lisp (file).
cl-postgres (module).
aggregated-gen-final-client-message (function).
gen-auth-message (function).
gen-client-initial-response (function).
gen-client-key (function).
gen-client-nonce (function).
gen-client-proof (function).
gen-client-signature (function).
gen-final-message (function).
gen-final-message-part-1 (function).
gen-salted-password (function).
gen-stored-key (function).
get-server-key (function).
get-server-signature (function).
index (type).
make-octet-vector (function).
octet (type).
octet-vector (type).
pad-octet-vector (function).
parse-scram-server-first-response (function).
split-server-response (function).
validate-server-nonce (function).
cl-postgres/cl-postgres/protocol.lisppackage.lisp (file).
interpret.lisp (file).
messages.lisp (file).
errors.lisp (file).
scram.lisp (file).
saslprep.lisp (file).
trivial-utf-8.lisp (file).
config.lisp (file).
cl-postgres (module).
*ssl-certificate-file* (special variable).
*ssl-key-file* (special variable).
*ssl-root-ca-file* (special variable).
def-row-reader (macro).
field-name (generic reader).
(setf field-name) (writer method).
field-type (generic reader).
(setf field-type) (writer method).
postgresql-notification (condition).
postgresql-notification-channel (reader method).
(setf postgresql-notification-channel) (writer method).
postgresql-notification-payload (reader method).
(setf postgresql-notification-payload) (writer method).
postgresql-notification-pid (reader method).
(setf postgresql-notification-pid) (writer method).
postgresql-warning (condition).
row-reader (macro).
*connection-params* (special variable).
*effected-rows* (special variable).
authenticate (function).
build-row-reader (function).
field-binary-p (reader method).
field-description (class).
field-interpreter (reader method).
(setf field-interpreter) (writer method).
get-error (function).
get-notification (function).
get-warning (function).
initiate-ssl (function).
look-for-row (function).
message-case (macro).
protocol-error (condition).
read-byte-delimited (function).
read-field-descriptions (function).
returning-effected-rows (macro).
send-close (function).
send-execute (function).
send-parse (function).
send-query (function).
terminate-connection (function).
try-to-sync (function).
update-backend-key-data (function).
update-parameter (function).
with-syncing (macro).
cl-postgres/cl-postgres/public.lisppackage.lisp (file).
protocol.lisp (file).
features.lisp (file).
config.lisp (file).
cl-postgres (module).
*unix-socket-dir* (special variable).
alist-row-reader (function).
close-database (function).
connection-meta (function).
connection-parameters (generic reader).
(setf connection-parameters) (writer method).
connection-use-binary (reader method).
(setf connection-use-binary) (writer method).
database-connection (class).
database-open-p (function).
exec-prepared (function).
exec-query (function).
get-postgresql-version (function).
ignore-row-reader (function).
list-row-reader (function).
open-database (function).
postgresql-version-at-least (function).
prepare-query (function).
reopen-database (function).
unprepare-query (function).
use-binary-parameters (function).
vector-row-reader (function).
wait-for-notification (function).
connection-application-name (reader method).
(setf connection-application-name) (writer method).
connection-available (reader method).
(setf connection-available) (writer method).
connection-db (generic function).
connection-host (reader method).
connection-password (reader method).
connection-pid (function).
connection-port (generic function).
connection-service (reader method).
(setf connection-service) (writer method).
connection-socket (reader method).
(setf connection-socket) (writer method).
connection-timestamp-format (reader method).
(setf connection-timestamp-format) (writer method).
connection-use-ssl (reader method).
connection-user (reader method).
ensure-connection (function).
find-postgresql-prepared-query (function).
get-host-address (function).
inet-socket-connect (function).
initiate-connection (function).
unix-socket-connect (function).
unix-socket-path (function).
using-connection (macro).
with-reconnect-restart (macro).
cl-postgres/cl-postgres/bulk-copy.lisppackage.lisp (file).
public.lisp (file).
trivial-utf-8.lisp (file).
cl-postgres (module).
close-db-writer (function).
db-write-row (function).
open-db-writer (function).
print-object (method).
bulk-copier (class).
bulk-copier-own-connection (reader method).
copier-columns (reader method).
copier-count (reader method).
(setf copier-count) (writer method).
copier-database (reader method).
copier-table (reader method).
copier-write-sequence (function).
copier-write-value (function).
copy-query (function).
initialize-copier (function).
prepare-row (method).
send-copy-done (function).
send-copy-start (function).
s-sql/s-sql/config.lisppackage.lisp (file).
s-sql (module).
*downcase-symbols* (special variable).
*escape-sql-names-p* (special variable).
*standard-sql-strings* (special variable).
*postgres-reserved-words* (special variable).
s-sql/s-sql/s-sql.lisppackage.lisp (file).
config.lisp (file).
s-sql (module).
bigint (type).
bytea (type).
db-null (type).
double-precision (type).
enable-s-sql-syntax (function).
from-sql-name (function).
numeric (type).
register-sql-operators (macro).
smallint (type).
sql (macro).
sql-compile (function).
sql-error (function).
sql-error (condition).
sql-escape (generic function).
sql-escape-string (function).
sql-template (function).
sql-type-name (generic function).
text (reader method).
text (type).
to-sql-name (function).
varchar (type).
%build-foreign-reference (function).
*expand-runtime* (special variable).
alter-table-column (function).
cons-to-sql-name-strings (function).
def-drop-op (macro).
def-sql-op (macro).
dequote (function).
dissect-type (function).
escape-sql-expression (function).
expand-composite-table-name (function).
expand-create-index (function).
expand-extended-table-constraint (function).
expand-foreign-on* (function).
expand-identity (function).
expand-interval (function).
expand-joins (function).
expand-rows (function).
expand-sql-op (generic function).
expand-table-column (function).
expand-table-constraint (function).
expand-table-constraint-sok (function).
expand-table-name (function).
for-update/share (function).
implode (function).
make-expander (function).
malformed-composite-type-error (condition).
quoted-name-p (function).
reduce-strings (function).
s-sql-reader (function).
serial (type).
serial8 (type).
split-on-keywords (macro).
split-on-keywords% (function).
sql-expand (function).
sql-expand-list (function).
sql-expand-names (function).
strcat (function).
to-s-sql-string (generic function).
to-type-name (function).
Packages are listed by definition order.
cl-postgres-errorcl-postgres-ieee-floatscl-postgres.featurescl-postgres-oidcl-postgrescl-postgres-systempostmodern-systemcl-postgres-trivial-utf-8postmoderns-sqls-sql-systemcl-postgres-errorcl-postgres.
common-lisp.
admin-shutdown (condition).
cannot-connect-now (condition).
check-violation (condition).
columns-error (condition).
connection-does-not-exist (condition).
connection-exception (condition).
connection-failure (condition).
crash-shutdown (condition).
data-exception (condition).
db-division-by-zero (condition).
deadlock-detected (condition).
duplicate-alias (condition).
duplicate-column (condition).
duplicate-cursor (condition).
duplicate-database (condition).
duplicate-function (condition).
duplicate-object (condition).
duplicate-prepared-statement (condition).
duplicate-schema (condition).
duplicate-table (condition).
feature-not-supported (condition).
floating-point-exception (condition).
foreign-key-violation (condition).
insufficient-privilege (condition).
insufficient-resources (condition).
integrity-violation (condition).
internal-error (condition).
invalid-datetime-format (condition).
invalid-sql-statement-name (condition).
lock-not-available (condition).
not-null-violation (condition).
numeric-value-out-of-range (condition).
object-in-use (condition).
object-state-error (condition).
operator-intervention (condition).
program-limit-exceeded (condition).
protocol-violation (condition).
query-canceled (condition).
restrict-violation (condition).
serialization-failure (condition).
server-shutdown (condition).
sqlclient-unable-to-establish-sqlconnection (condition).
sqlserver-rejected-establishment-of-sqlconnection (condition).
statement-completion-unknown (condition).
syntax-error-or-access-violation (condition).
system-error (condition).
transaction-integrity-constraint-violation (condition).
transaction-resolution-unknown (condition).
transaction-rollback (condition).
undefined-column (condition).
undefined-table (condition).
unique-violation (condition).
*error-table* (special variable).
deferror (macro).
get-error-type (function).
invalid-byte-sequence (condition).
unrecognized-configuration-parameter (condition).
cl-postgres-ieee-floatsclp-ieee-floats
common-lisp.
decode-float32 (function).
decode-float64 (function).
encode-float32 (function).
encode-float64 (function).
make-float-converters (macro).
cl-postgres-oidoid
common-lisp.
+abstime+ (constant).
+aclitem+ (constant).
+any+ (constant).
+any-array+ (constant).
+anyelement+ (constant).
+anyenum+ (constant).
+anynon-array+ (constant).
+anyrange+ (constant).
+bit+ (constant).
+bit-array+ (constant).
+bool+ (constant).
+bool-array+ (constant).
+box+ (constant).
+box-array+ (constant).
+bpchar+ (constant).
+bpchar-array+ (constant).
+bytea+ (constant).
+bytea-array+ (constant).
+cash+ (constant).
+char+ (constant).
+char-array+ (constant).
+cid+ (constant).
+cidr+ (constant).
+circle+ (constant).
+cstring+ (constant).
+cstring-array+ (constant).
+date+ (constant).
+date-array+ (constant).
+evttrigger+ (constant).
+fdw-handler+ (constant).
+float4+ (constant).
+float4-array+ (constant).
+float8+ (constant).
+float8-array+ (constant).
+gtsvector+ (constant).
+index-am-handler+ (constant).
+inet+ (constant).
+int2+ (constant).
+int2-array+ (constant).
+int2vector+ (constant).
+int4+ (constant).
+int4-array+ (constant).
+int4range+ (constant).
+int8+ (constant).
+int8-array+ (constant).
+internal+ (constant).
+interval+ (constant).
+interval-array+ (constant).
+json+ (constant).
+jsonb+ (constant).
+language-handler+ (constant).
+line+ (constant).
+lseg+ (constant).
+lseg-array+ (constant).
+lsn+ (constant).
+macaddr+ (constant).
+name+ (constant).
+name-array+ (constant).
+numeric+ (constant).
+numeric-array+ (constant).
+oid+ (constant).
+oid-array+ (constant).
+oid-vector+ (constant).
+opaque+ (constant).
+path+ (constant).
+pgddlcommand+ (constant).
+pgnodetree+ (constant).
+point+ (constant).
+point-array+ (constant).
+polygon+ (constant).
+record+ (constant).
+record-array+ (constant).
+refcursor+ (constant).
+regclass+ (constant).
+regconfig+ (constant).
+regdictionary+ (constant).
+regnamespace+ (constant).
+regoper+ (constant).
+regoperator+ (constant).
+regproc+ (constant).
+regprocedure+ (constant).
+regrole+ (constant).
+regtype+ (constant).
+regtype-array+ (constant).
+reltime+ (constant).
+text+ (constant).
+text-array+ (constant).
+tid+ (constant).
+time+ (constant).
+time-array+ (constant).
+timestamp+ (constant).
+timestamp-array+ (constant).
+timestamptz+ (constant).
+timestamptz-array+ (constant).
+timetz+ (constant).
+tinterval+ (constant).
+trigger+ (constant).
+tsm-handler+ (constant).
+tsquery+ (constant).
+tsvector+ (constant).
+unknown+ (constant).
+uuid+ (constant).
+v-oid+ (constant).
+varbit+ (constant).
+varbit-array+ (constant).
+varchar+ (constant).
+varchar-array+ (constant).
+xid+ (constant).
+xml+ (constant).
cl-postgrescommon-lisp.
*on-evidence-of-man-in-the-middle-attack* (special variable).
*query-callback* (special variable).
*query-log* (special variable).
*read-row-values-as-binary* (special variable).
*retry-connect-delay* (special variable).
*retry-connect-times* (special variable).
*silently-truncate-ratios* (special variable).
*sql-readtable* (special variable).
*ssl-certificate-file* (special variable).
*ssl-key-file* (special variable).
*ssl-root-ca-file* (special variable).
*unix-socket-dir* (special variable).
alist-row-reader (function).
close-database (function).
close-db-writer (function).
connection-meta (function).
connection-parameters (generic reader).
(setf connection-parameters) (generic writer).
connection-use-binary (generic reader).
(setf connection-use-binary) (generic writer).
copy-sql-readtable (function).
database-connection (class).
database-connection-error (condition).
database-connection-lost (condition).
database-error (condition).
database-error-cause (generic reader).
database-error-code (generic reader).
database-error-constraint-name (function).
database-error-detail (generic reader).
database-error-extract-name (function).
database-error-message (generic reader).
(setf database-error-message) (generic writer).
database-error-query (generic reader).
database-open-p (function).
database-socket-error (condition).
db-write-row (function).
def-row-reader (macro).
default-sql-readtable (function).
exec-prepared (function).
exec-query (function).
field-name (generic reader).
(setf field-name) (generic writer).
field-type (generic reader).
(setf field-type) (generic writer).
get-postgresql-version (function).
ignore-row-reader (function).
int4 (type).
int8 (type).
list-row-reader (function).
log-query (function).
oid-types-match-p (function).
open-database (function).
open-db-writer (function).
param-to-oid (function).
parameter-list-types (function).
parameter-lists-match-oid-types-p (function).
postgresql-notification (condition).
postgresql-notification-channel (generic reader).
(setf postgresql-notification-channel) (generic writer).
postgresql-notification-payload (generic reader).
(setf postgresql-notification-payload) (generic writer).
postgresql-notification-pid (generic reader).
(setf postgresql-notification-pid) (generic writer).
postgresql-version-at-least (function).
postgresql-warning (condition).
prepare-query (function).
reopen-database (function).
row-reader (macro).
saslprep-normalize (function).
serialize-for-postgres (generic function).
set-sql-datetime-readers (function).
set-sql-reader (function).
string-mapped-to-nothing (function).
string-mapped-to-space (function).
string-printable-ascii-p (function).
to-sql-string (generic function).
types-match-p (function).
unprepare-query (function).
use-binary-parameters (function).
uuid-string (type).
uuip-p (function).
vector-row-reader (function).
wait-for-notification (function).
with-binary-row-values (macro).
with-text-row-values (macro).
*client-encoding* (special variable).
*connection-params* (special variable).
*current-query* (special variable).
*default-sql-readtable* (special variable).
*effected-rows* (special variable).
*optimize* (special variable).
*printable-ascii-chars* (special variable).
*timestamp-format* (special variable).
+seconds-in-day+ (constant).
+start-of-2000+ (constant).
aggregated-gen-final-client-message (function).
authenticate (function).
bad-char-error (function).
bad-char-error (condition).
bad-char-error-message (generic reader).
(setf bad-char-error-message) (generic writer).
bad-char-error-normalization-form (generic reader).
(setf bad-char-error-normalization-form) (generic writer).
bad-char-error-value (generic reader).
(setf bad-char-error-value) (generic writer).
binary-reader (macro).
bind-message (function).
build-row-reader (function).
bulk-copier (class).
bulk-copier-own-connection (generic reader).
bytes-to-hex-string (function).
char-mapped-to-nothing-p (function).
char-mapped-to-space-p (function).
char-printable-ascii-p (function).
close-prepared-message (function).
code-point-printable-ascii-p (function).
connection-application-name (generic reader).
(setf connection-application-name) (generic writer).
connection-available (generic reader).
(setf connection-available) (generic writer).
connection-db (generic function).
connection-host (generic reader).
connection-password (generic reader).
connection-pid (function).
connection-port (generic function).
connection-service (generic reader).
(setf connection-service) (generic writer).
connection-socket (generic reader).
(setf connection-socket) (generic writer).
connection-timestamp-format (generic reader).
(setf connection-timestamp-format) (generic writer).
connection-use-ssl (generic reader).
connection-user (generic reader).
copier-columns (generic reader).
copier-count (generic reader).
(setf copier-count) (generic writer).
copier-database (generic reader).
copier-table (generic reader).
copier-write-sequence (function).
copier-write-value (function).
copy-data-message (function).
copy-done-message (function).
copy-fail-message (function).
copy-query (function).
database-error-context (generic reader).
database-error-hint (generic reader).
database-error-position (generic reader).
default-date-reader (function).
default-interval-reader (function).
default-time-reader (function).
default-timestamp-reader (function).
define-interpreter (macro).
define-message (macro).
describe-prepared-message (function).
enc-byte-length (function).
enc-read-string (function).
enc-string-bytes (function).
enc-write-string (function).
ensure-connection (function).
ensure-socket-is-closed (function).
escape-bytes (function).
field-binary-p (generic reader).
field-description (class).
field-interpreter (generic reader).
(setf field-interpreter) (generic writer).
find-postgresql-prepared-query (function).
flush-message (function).
formats-to-bytes (function).
gen-auth-message (function).
gen-client-initial-response (function).
gen-client-key (function).
gen-client-nonce (function).
gen-client-proof (function).
gen-client-signature (function).
gen-final-message (function).
gen-final-message-part-1 (function).
gen-salted-password (function).
gen-stored-key (function).
get-error (function).
get-host-address (function).
get-int-size (function).
get-notification (function).
get-server-key (function).
get-server-signature (function).
get-type-interpreter (function).
get-warning (function).
go-binary-list-p (function).
go-binary-p (function).
gss-auth-buffer-message (function).
index (type).
inet-socket-connect (function).
initialize-copier (function).
initiate-connection (function).
initiate-ssl (function).
int-to-vector (function).
int16-to-vector (function).
int2 (type).
int2-array (type).
int2-array-p (function).
int2p (function).
int32-to-vector (function).
int4-array (type).
int4-array-p (function).
int4p (function).
int64-to-vector (function).
int8-array (type).
int8-array-p (function).
int8-to-vector (function).
int8p (function).
integer-reader (macro).
integer-reader-name (function).
integer-writer (macro).
integer-writer-name (function).
interpret-as-text (function).
interpret-usec-bits (function).
interpreter-binary-p (function).
interpreter-reader (function).
look-for-row (function).
make-octet-vector (function).
md5-password (function).
md5-password-message (function).
message-case (macro).
octet (type).
octet-vector (type).
pad-octet-vector (function).
parse-message (function).
parse-message-binary-parameters (function).
parse-scram-server-first-response (function).
plain-password-message (function).
prepare-row (generic function).
protocol-error (condition).
query-message (function).
read-array-value (function).
read-binary-array-value (function).
read-binary-bits (function).
read-byte-delimited (function).
read-bytes (function).
read-field-descriptions (function).
read-int1 (function).
read-int2 (function).
read-int4 (function).
read-int8 (function).
read-row-value (function).
read-simple-str (function).
read-str (function).
read-uint1 (function).
read-uint2 (function).
read-uint4 (function).
read-uint8 (function).
returning-effected-rows (macro).
scram-cont-message (function).
scram-type-message (function).
send-close (function).
send-copy-done (function).
send-copy-start (function).
send-execute (function).
send-parse (function).
send-query (function).
set-date-reader (function).
set-interval-reader (function).
set-usec-reader (function).
simple-bind-message (function).
simple-describe-message (function).
simple-execute-message (function).
simple-parse-message (function).
skip-bytes (function).
skip-str (function).
split-server-response (function).
ssl-request-message (function).
startup-message (function).
sync-message (function).
terminate-connection (function).
terminate-message (function).
text-array (type).
text-array-p (function).
try-to-sync (function).
type-interpreter (class).
type-interpreter-binary-reader (generic reader).
(setf type-interpreter-binary-reader) (generic writer).
type-interpreter-oid (generic reader).
(setf type-interpreter-oid) (generic writer).
type-interpreter-text-reader (generic reader).
(setf type-interpreter-text-reader) (generic writer).
type-interpreter-use-binary (generic reader).
(setf type-interpreter-use-binary) (generic writer).
unix-socket-connect (function).
unix-socket-path (function).
update-backend-key-data (function).
update-parameter (function).
using-connection (macro).
uuid-to-byte-array (function).
validate-server-nonce (function).
with-query (macro).
with-reconnect-restart (macro).
with-syncing (macro).
wrap-socket-error (function).
write-bytes (function).
write-int1 (function).
write-int2 (function).
write-int4 (function).
write-int8 (function).
write-quoted (function).
write-ratio-as-floating-point (function).
write-str (function).
write-uint1 (function).
write-uint2 (function).
write-uint4 (function).
write-uint8 (function).
cl-postgres-systemasdf/interface.
common-lisp.
*string-file* (special variable).
*unicode* (special variable).
cl-postgres-trivial-utf-8clp-utf8
common-lisp.
read-utf-8-string (function).
string-to-utf-8-bytes (function).
utf-8-byte-length (function).
utf-8-bytes-to-string (function).
utf-8-decoding-error (condition).
utf-8-group-size (function).
write-utf-8-bytes (function).
*optimize* (special variable).
as-utf-8-bytes (macro).
get-utf-8-character (function).
utf-8-string-length (function).
postmodernpomo
cl-postgres.
closer-common-lisp.
s-sql.
!dao-def (function).
!foreign (function).
!index (function).
!unique (function).
!unique-index (function).
*allow-overwriting-prepared-statements* (special variable).
*current-logical-transaction* (special variable).
*database* (special variable).
*default-use-ssl* (special variable).
*enforce-parameter-types* (special variable).
*ignore-unknown-columns* (special variable).
*isolation-level* (special variable).
*max-pool-size* (special variable).
*table-name* (special variable).
*table-symbol* (special variable).
abort-hooks (generic reader).
(setf abort-hooks) (generic writer).
abort-logical-transaction (generic function).
abort-transaction (function).
add-comment (function).
alter-role-search-path (function).
call-with-connection (function).
cancel-backend (function).
change-password (function).
change-toplevel-database (function).
clear-connection-pool (function).
coalesce (function).
col-type-text-p (function).
column-exists-p (function).
commit-hooks (generic reader).
(setf commit-hooks) (generic writer).
commit-logical-transaction (generic function).
commit-transaction (function).
connect (function).
connect-toplevel (function).
connected-p (function).
create-all-tables (function).
create-database (function).
create-index (function).
create-package-tables (function).
create-role (function).
create-schema (function).
create-sequence (function).
create-table (function).
current-database (function).
dao-class (class).
dao-exists-p (generic function).
dao-keys (generic function).
dao-table-definition (function).
dao-table-name (function).
database-exists-p (function).
database-size (function).
database-version (function).
define-dao-finalization (macro).
defprepared (macro).
defprepared-with-names (macro).
deftable (macro).
delete-dao (generic function).
describe-constraint (function).
describe-foreign-key-constraints (function).
describe-triggers (function).
describe-views (function).
disconnect (generic function).
disconnect-toplevel (function).
do-query-dao (macro).
do-select-dao (macro).
doquery (macro).
drop-database (function).
drop-index (function).
drop-prepared-statement (function).
drop-role (function).
drop-schema (function).
drop-sequence (function).
drop-table (function).
encode-json-to-string (function).
ensure-transaction (macro).
ensure-transaction-with-isolation-level (macro).
execute (macro).
execute-file (function).
fetch-defaults (generic function).
find-col-type (function).
find-comments (function).
find-dao-column-slot (function).
find-postgresql-prepared-statement (function).
find-postmodern-prepared-statement (function).
find-primary-key-column (generic function).
find-primary-key-info (function).
get-all-table-comments (function).
get-column-comment (function).
get-column-comments (function).
get-dao (generic function).
get-database-comment (function).
get-pid (function).
get-pid-from-postmodern (function).
get-schema-comment (function).
get-search-path (function).
get-table-comment (function).
get-table-oid (function).
grant-admin-permissions (function).
grant-editor-permissions (function).
grant-readonly-permissions (function).
grant-role-permissions (function).
index-exists-p (function).
insert-dao (generic function).
list-all-constraints (function).
list-all-tables (function).
list-available-collations (function).
list-available-extensions (function).
list-available-types (function).
list-check-constraints (function).
list-columns (function).
list-columns-with-types (function).
list-connections (function).
list-database-access-rights (function).
list-database-functions (function).
list-database-users (function).
list-databases (function).
list-detailed-triggers (function).
list-foreign-keys (function).
list-index-definitions (function).
list-indexed-column-and-attributes (function).
list-indices (function).
list-installed-extensions (function).
list-postmodern-prepared-statements (function).
list-prepared-statements (function).
list-role-accessible-databases (function).
list-role-permissions (function).
list-roles (function).
list-schemas (function).
list-sequences (function).
list-table-indices (function).
list-table-sizes (function).
list-tables (function).
list-tables-in-schema (function).
list-tablespaces (function).
list-templates (function).
list-triggers (function).
list-unique-or-primary-constraints (function).
list-views (function).
load-uuid-extension (function).
make-dao (generic function).
num-records-in-database (function).
parse-queries (function).
postgres-array-string-to-array (function).
postgres-array-string-to-list (function).
postgresql-version (function).
prepare (macro).
prepared-statement-exists-p (function).
query (macro).
query-dao (macro).
read-queries (function).
reconnect (generic function).
release-savepoint (function).
rename-column (function).
rename-table (function).
reset-prepared-statement (function).
retry-transaction (function).
revoke-all-on-table (function).
role-exists-p (function).
rollback-savepoint (function).
rollback-transaction (function).
save-dao (function).
save-dao/transaction (function).
schema-exists-p (function).
select-dao (macro).
sequence-exists-p (function).
sequence-next (function).
set-search-path (function).
split-fully-qualified-tablename (function).
table-description (function).
table-description-menu (function).
table-description-plus (function).
table-exists-p (function).
table-size (function).
terminate-backend (function).
update-dao (generic function).
upsert-dao (generic function).
valid-sql-identifier-p (function).
view-exists-p (function).
with-column-writers (macro).
with-connection (macro).
with-logical-transaction (macro).
with-savepoint (macro).
with-schema (macro).
with-transaction (macro).
%eval (function).
*alter-all-default-editor-privileges* (special variable).
*alter-all-default-select-privileges* (special variable).
*character-sets* (special variable).
*class-finalize-lock* (special variable).
*collations* (special variable).
*connection-pools* (special variable).
*custom-column-writers* (special variable).
*direct-column-slot* (special variable).
*disallowed-role-names* (special variable).
*execute-privileges-list* (special variable).
*identifier-name-to-key* (special variable).
*json-aggregate-context* (special variable).
*json-aggregate-first* (special variable).
*json-identifier-name-to-lisp* (special variable).
*json-list-encoder-fn* (special variable).
*json-output* (special variable).
*json-symbols-package* (special variable).
*lisp-identifier-name-to-json* (special variable).
*pool-lock* (special variable).
*result-styles* (special variable).
*schema-path* (special variable).
*tables* (special variable).
*transaction-level* (special variable).
+json-lisp-symbol-tokens+ (special variable).
add-table-definition (function).
all-rows (macro).
array-hash-row-reader (function).
as-array-member (macro).
as-object-member (macro).
base-filename (generic reader).
begin-transaction (function).
build-dao-methods (function).
call-with-ensured-transaction (function).
call-with-logical-transaction (function).
call-with-savepoint (function).
call-with-transaction (function).
camel-case-split (function).
camel-case-to-lisp (function).
camel-case-transform (function).
camel-case-transform-all-caps (function).
character-set-exists-p (function).
code-char-0-p (function).
collation-exists-p (function).
collect-export-functions (function).
collect-import-functions (function).
column-check (generic reader).
column-collate (generic reader).
column-default (generic reader).
column-export (generic reader).
column-identity (generic reader).
column-import (generic reader).
column-interval (generic reader).
column-primary-key (generic reader).
column-references (generic reader).
column-row-reader (function).
column-type (generic reader).
column-unique (generic reader).
comment-parser (structure).
comment-parser-buffer (reader).
(setf comment-parser-buffer) (writer).
comment-parser-p (function).
comment-parser-state (reader).
(setf comment-parser-state) (writer).
comment-parser-stream (reader).
(setf comment-parser-stream) (writer).
connection-pool-type (generic reader).
(setf connection-pool-type) (generic writer).
copy-comment-parser (function).
copy-from-csv (function).
copy-parser (function).
create-role-helper (function).
dao-column-fields (function).
dao-column-map (generic reader).
dao-column-slots (function).
dao-from-fields (function).
dao-row-reader (function).
dao-row-reader-with-body (macro).
dao-spec-for-format (function).
dao-superclasses (function).
database-parameters-to-list (function).
direct-column-slot (class).
direct-keys (generic reader).
disallowed-tag-char-p (function).
do-with-schema (function).
effective-column-slot (class).
encode-array-member (function).
encode-json (generic function).
encode-json-alist (function).
encode-json-alist-to-string (function).
encode-json-list-explicit-encoder (function).
encode-json-list-guessing-encoder (function).
encode-json-plist (function).
encode-json-plist-to-string (function).
encode-object-member (function).
ensure-prepared (function).
extend-current-tag (generic function).
field-name-to-slot-name (function).
filename (generic reader).
find-export-function (function).
find-import-function (function).
find-included-filename (function).
find-postgresql-prepared-statement-by-query (function).
flat-table-name (function).
format-current-tag (generic function).
generate-dao-query (function).
generate-prepared (function).
generate-uuid (function).
get-from-pool (function).
ghost (generic reader).
grant-role-permissions-helper (function).
inconsistent-schema-name (condition).
invalid-database-name (condition).
isolation-level-p (function).
json-bool (function).
json-intern (function).
json-or-null (function).
json-row-array-reader (function).
json-row-reader (function).
line-has-includes (function).
lisp-to-camel-case (function).
list-to-column (function).
local-time-timestamp-string (function).
make-comment-parser (function).
make-exists-query (macro).
make-keyword (function).
make-list-query (macro).
make-parser (function).
map-slots (function).
maybe-close-tags (generic function).
meta-cmd (generic reader).
mismatched-parameter-types (condition).
missing-i-file (condition).
next-aggregate-member (function).
next-statement-id (function).
parameter-types (generic reader).
parse-comments (function).
parse-query (function).
parser (structure).
parser-filename (reader).
(setf parser-filename) (writer).
parser-p (function).
parser-state (reader).
(setf parser-state) (writer).
parser-stream (reader).
(setf parser-stream) (writer).
parser-tags (reader).
(setf parser-tags) (writer).
pooled-database-connection (class).
pop-current-tag (generic function).
prepared-statement-types (generic reader).
push-new-tag (generic function).
query-dao% (function).
read-sql-file (function).
reader-for-format (function).
real-query (function).
remove-sql-comments (function).
remove-whitespace (function).
replace-non-alphanumeric-chars (function).
reset-state (generic function).
safe-json-intern (function).
savepoint-connection (generic reader).
savepoint-handle (class).
savepoint-name (generic reader).
savepoint-open-p (generic reader).
(setf savepoint-open-p) (generic writer).
schema-parameters-to-list (function).
set-to-class (function).
simple-date-date-string (function).
simple-date-interval-string (function).
simple-date-time-of-day-string (function).
simple-date-timestamp-string (function).
simplified-camel-case-to-lisp (function).
single-line-comment-scanner (special variable).
single-row (macro).
single-row! (macro).
slot-column (generic reader).
slot-definition-name-as-string (function).
slot-sql-name (generic reader).
stream-array-member-encoder (function).
stream-object-member-encoder (function).
symbol-alist-row-reader (function).
symbol-plist-row-reader (function).
table-parameter-helper (function).
table-schema-names (function).
to-identifier (function).
transaction-connection (generic reader).
transaction-handle (class).
transaction-open-p (generic reader).
(setf transaction-open-p) (generic writer).
unencodable-value-error (function).
unencodable-value-error (condition).
unencodable-value-error-context (generic reader).
(setf unencodable-value-error-context) (generic writer).
unknown-symbol-error (function).
unknown-symbol-error (condition).
unknown-symbol-error-datum (generic reader).
(setf unknown-symbol-error-datum) (generic writer).
use-explicit-encoder (function).
use-guessing-encoder (function).
valid-sql-character-p (function).
whitespace-in-string (function).
with-aggregate (macro).
with-array (macro).
with-explicit-encoder (macro).
with-guessing-encoder (macro).
with-local-encoder (macro).
with-object (macro).
with-pool-lock (macro).
with-substitute-printed-representation-restart (macro).
write-json-chars (function).
write-json-number (function).
write-json-string (function).
s-sqlcommon-lisp.
*downcase-symbols* (special variable).
*escape-sql-names-p* (special variable).
*standard-sql-strings* (special variable).
bigint (type).
bytea (type).
db-null (type).
double-precision (type).
enable-s-sql-syntax (function).
from-sql-name (function).
numeric (type).
register-sql-operators (macro).
smallint (type).
sql (macro).
sql-compile (function).
sql-error (function).
sql-error (condition).
sql-escape (generic function).
sql-escape-string (function).
sql-template (function).
sql-type-name (generic function).
text (slot).
text (slot).
text (generic reader).
text (type).
to-sql-name (function).
varchar (type).
%build-foreign-reference (function).
*expand-runtime* (special variable).
*postgres-reserved-words* (special variable).
alter-table-column (function).
cons-to-sql-name-strings (function).
def-drop-op (macro).
def-sql-op (macro).
dequote (function).
dissect-type (function).
escape-sql-expression (function).
expand-composite-table-name (function).
expand-create-index (function).
expand-extended-table-constraint (function).
expand-foreign-on* (function).
expand-identity (function).
expand-interval (function).
expand-joins (function).
expand-rows (function).
expand-sql-op (generic function).
expand-table-column (function).
expand-table-constraint (function).
expand-table-constraint-sok (function).
expand-table-name (function).
for-update/share (function).
implode (function).
make-expander (function).
malformed-composite-type-error (condition).
quoted-name-p (function).
reduce-strings (function).
s-sql-reader (function).
serial (type).
serial8 (type).
split-on-keywords (macro).
split-on-keywords% (function).
sql-expand (function).
sql-expand-list (function).
sql-expand-names (function).
strcat (function).
to-s-sql-string (generic function).
to-type-name (function).
Definitions are sorted by export status, category, package, and then by lexicographic order.
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.
This is bound to the current transaction-handle or savepoint-handle instance representing the innermost open logical transaction.
Special holding the current database. Most functions and macros operating on a database assume this contains a connected database.
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.
When converting symbols to strings, whether to downcase the symbols is set here. The default is to downcase symbols.
When set to t, the parameters of the first invocation of a prepared statement
will set the mandatory types that subsequent invocations of that prepared
statement must meet. If the parameters are used in paramparameters must meet. parameters must match type ensured-prepared will overwrite prepared statements having the
same name if the query statement itself in the postmodern meta connection is
different than the query statement provided to ensure-prepared.
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.
Normally, when get-dao, select-dao,
save-dao or query-dao finds a column in the database that’s not in the DAO class,
it should raise an error. Setting this variable to a non-NIL will cause it to
simply ignore the unknown column and allows you to define daos which are subsets
of a table.
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)
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.
If Postmodern sees evidence of an attempted man-in-the-middle attack, what should Postmodern do? Acceptable values are :error, :warn or :ignore
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
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.
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.
How many seconds to wait before trying to connect again. Borrowed from pgloader
How many times do we try to connect again. Borrowed from pgloader
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.
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.
When set to a filename, this file will be used as client certificate for SSL connections.
When set to a filename, this file will be used as client key for SSL connections.
Should be a path to a root certificate file, typically a pem file. It is used for SSL connections when you want to verify the host using a root ca.
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.
Used inside deftable to find the name of the table being defined.
Used inside deftable to find the symbol naming the table being defined.
Directory where the Unix domain socket for PostgreSQL be found.
The defun-like variant of row-reader: creates a row reader and gives it a top-level function name.
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.
This is another macro variant of prepare. It is like prepare, but gives the function a name which now becomes a top-level function for the prepared statement. The name should not be a string but may be quoted.
Like defprepared, but allows to specify names of the function arguments in a lambda list as well as arguments supplied to the query.
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.
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))
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))
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))
Ensures that body is executed within a transaction, but does not begin a new transaction if one is already in progress.
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
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.)
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.
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.
Execute the given query, which can be either a string or an S-SQL form (list
starting with a keyword). If the query contains placeholders ($1, $2, etc)
their values can be given as extra arguments. If one of these arguments is a
keyword occurring in the table below, it will not be used as a query
argument, but will determine the format in which the results are returned
instead. Any of the following formats can be used, with the default being :rows:
| :none | Ignore the result values. | | :lists, :rows | Return a list of lists, each list containing the values | | | for a row. | | :list, :row | Return a single row as a list. | | :alists | Return a list of alists which map column names to values, | | | with the names represented as keywords. | | :alist | Return a single row as an alist. | | :array-hash | Return an array of hashtables which map column names to | | | hash table keys. NOTE: It will return an empty array | | | if there is no result. | | :vectors | Returns a vector of vectors where each internal vector is | | | a returned row from the query. The field names are not | | | included. NOTE: It will return an empty vector instead of | | | NIL if there is no result. | | :str-alists | Like :alists, but use the original column names. | | :str-alist | Return a single row as an alist, with strings for names. | | :plists | Return a list of plists which map column names to values, | | | with the names represented as keywords. | | :plist | Return a single row as a plist. | | :column | Return a single column as a list. | | :single | Return a single value. | | :single! | Like :single, but raise an error when the number of | | | selected rows is not equal to 1. | | (:dao type) | Return a list of DAOs of the given type. The names of the | | | fields returned by the query must match slots in the DAO | | | class the same way as with query-dao. | | (:dao type :single)| Return a single DAO of the given type. | | :json-strs | Return a list of strings where each row is a json object | | | expressed as a string | | :json-str | Return a single string where the row returned is a json | | | object expressed as a string. | | :json-array-str | Return a string containing a json array, each element in | | | the array is a selected row expressed as a json object. | | | NOTE: If there is no result, it will return a string with | | | an empty json array. |
If the database returns information about the amount rows that were affected, such as with updating or deleting queries, this is returned as a second value.
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.
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.
Creates a row-reader, using the given name for the variable. Inside the body
this variable refers to a vector of field descriptions. On top of that, two
local functions are bound, next-row and next-field. The first will start
reading the next row in the result, and returns a boolean indicating whether
there is another row. The second will read and return one field, and should
be passed the corresponding field description from the fields argument as a
parameter.
A row reader should take care to iterate over all the rows in a result, and within each row iterate over all the fields. This means it should contain an outer loop that calls next-row, and every time next-row returns T it should iterate over the fields vector and call next-field for every field.
The definition of list-row-reader should give you an idea what a row reader
looks like:
(row-reader (fields)
(loop :while (next-row)
:collect (loop :for field :across fields
:collect (next-field field))))
Obviously, row readers should not do things with the database connection
like, say, close it or start a new query, since it still reading out the
results from the current query. A row reader
is a function that is used to do something with the results of a query. It has
two local functions: next-row and next-field, the first should be called
once per row and will return a boolean indicating whether there are
any more rows, the second should be called once for every element in
the fields vector, with that field as argument, to read a single value
in a row. See list-row-reader in public.lisp for an example.
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)
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.
Helper macro to locally set *read-row-values-as-binary* to t while executing body so that row values will be returned as binary.
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.
Evaluates the body with *database* bound to a connection as specified by spec, which should be list that connect can be applied to.
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))
...)
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))))
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))
Helper macro to locally set *read-row-values-as-binary* to nil while executing body so that row values will be returned as t.
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.
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.
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.
Used inside a deftable form. Define an index on the table being defined. The columns can be given as symbols or strings.
Constrains one or more columns to only contain unique (combinations of) values, with deferrable and initially-deferred defined as in !foreign
Used inside a deftable form. Define a unique index on the defined table.
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.
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)
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.
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.
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.
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.
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.
Disconnect and remove all connections in the connection pools.
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.
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.
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.
Returns t if a column-slot has text as a type. Could be text or (or text db-null)
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.
Immediately commit an open transaction.
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*.
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.
Returns a boolean indicating whether the given connection is still connected to the server.
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.
Copies a given readtable.
Create all defined tables.
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.
Create an index. Slightly less sophisticated than the query version because it does not have a where clause capability.
Create all tables whose identifying symbol is interned in the given package.
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.
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.
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.
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.
Returns the string name of the current database.
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.
Get the name of the table associated with the given DAO class (or symbol naming such a class).
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).
For various errors, returns the name provided by the error message (or nil if no such name was found).
Returns database name string if the database parameter is actually an available database
Returns a boolean indicating whether the given connection is currently connected.
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.
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.
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.
Returns the default readtable, containing only the readers defined by CL-postgres itself.
Return a list of alists of the descriptions a particular constraint given the table-name and the constraint name using the information_schema table.
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)
List detailed information on the triggers from the information_schema table.
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.
Disconnect *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.
Drop an index. Available keys are :concurrently, :if-exists, and :cascade.
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.
The role-name and optional new-owner name should be strings. If they are
symbols, they will be converted to string and hyphens will be converted to
underscores.
Before dropping the role, you must drop all the objects it owns (or reassign
their ownership) and revoke any privileges the role has been granted on other
objects. If database is :all, drop-role will loop through all databases in
the cluster ensuring that the role has no privileges or owned objects in
every database. Otherwise drop-role will drop objects owned by a role in the
current database.
We will reassign ownership of the objects to the postgres role
unless otherwise specified in the optional second parameter. If neither the
postgresql role nor a provided second parameter actually exist as a role on
the server, object ownership will be assigned to the role calling (drop-role).
Returns t if successful. Will not drop the postgres role.
As a minor matter of note, Postgresql allows a role to own objects in databases even if it does not have connection rights. This can be useful in setting group roles.
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.
Drop a sequence. Name should be quoted. Available key parameters are :if-exists and :cascade
Drop a table. Available additional key parameters are :if-exists and :cascade.
Enable a syntactic shortcut #Q(...) for (sql (...)). Optionally takes a character to use instead of #\Q.
Return the JSON representation of OBJECT as a string.
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.
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.
This function will execute sql queries stored in a file. Each sql statement
in the file will be run independently, but if one statement fails, subsequent
query statements will not be run, but any statement prior to the failing
statement will have been commited.
Execute-file allows the sql file to include other sql files, with the meta-commands i or include which look for a file location relative to your default pathname (current working directory) or ir or include_relative which look for a file location relative to the initial sql file. If the file is not found in the expected location, execute-file will look to see if the requested file is in the other possible location. If that does not work, it will trigger an error with a restart which allows you to provide a new name for the file.
If you want the standard transction treatment such that all statements succeed
or no statement succeeds, then ensure that the file starts with a begin
transaction statement and finishes with an end transaction statement. See the
test file test-execute-file-broken-transaction.sql as an example.
For debugging purposes, if the optional print parameter is set to t, format
will print the count of the query and the query to the REPL.
The default setting is to remove sql comments from the file before executing
the sql code. If that causes problems, the remove-comments parameter can be
set to nil.
IMPORTANT NOTE: This utility function assumes that the file containing the sql queries can be trusted and bypasses the normal postmodern parameterization of queries.
Returns the col-type for a class and a column-name. The column name must be a symbol
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.
Given a class and a symbol returns the dao-column-slot class for the column named by that symbol (not the sql_column_name). Column name can be a symbol or a string.
Returns the specified named prepared statement (if any) that postgresql has for this session.
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.
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.
Convert a string to a symbol, upcasing and replacing underscores with hyphens.
Returns a list of lists, each list showing the schema, table and comment of all tables with comments.
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.
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)
Returns the comment, if any, attached to a database
Get the process id used by postgresql for this connection.
Get the process id used by postgresql for this connection, but get it from the postmodern connection parameters.
Retrieves the version number of the connected postgresql database as a
string. Some installations of Postgresql add additional information after the base
version number, so hopefully this gets rid of the unwanted info.
If the schema has been commented, returns that string, else nil. Must be a schema in the currently connected database.
Returns the default schema search path for the current session.
Retrieves the comment, if any attached to the table.
Retrieves the oid identifier for a particular table from postgresql. Works for tables in all schemas.
Grants all privileges to a role for the named schema. If the optional table-name parameter is provided, the privileges are only granted with respect to that table (or tables if table-name is a list of table names).
Grants select, insert, update and delete privileges to a role for the named schema. If the optional table-name parameter is provided, the privileges are only granted with respect to that table (or tables if table-name is a list of table names).
Note that we are giving some function execute
permissions if table-name is nil, but if the table-name is specified, those are
not provided. Your mileage may vary on how many privileges you want to provide
to a editor role with access to only a limited number of tables.
Grants select privileges to a role for the named schema. If the optional
table-name parameter is provided, the privileges are only granted with respect
to that table (or tables if table-name is a list of table names). Note that we are
giving some function execute permissions if table-name is nil, but if the table-name
is specified, those are not provided. Your mileage may vary on how many privileges
you want to provide to a read-only role with access to only a limited number of tables.
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.
Tests whether an index with the given name exists. The name can be either a string or a symbol.
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.
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
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.
List the postgresql extensions which are available in the system to the currently connected database. The extensions may or may not be installed.
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")
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
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.
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.
List the current postgresql connections to the currently connected database. It does this by returningo info from pg_stat_activity on open connections.
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).
Returns a list of the functions in the database from the information_schema.
List database users (Roles who can login).
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.
DEPRECATED FOR DESCRIBE-TRIGGERS.List detailed information on the triggers from the information_schema table.
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))
Returns a list of the definitions used to create the current indexes for the table.
List the indexed columns and their attributes in a table. Includes primary key.
Return a list of the indexs in a database. Turn them into keywords if strings-p is not true.
List the postgresql extensions which are installed in the currently connected database.
List the prepared statements that postmodern has put in the meta slot in
the connection.
If the names-only parameter is set to t, it will only return a list of the names of the prepared statements.
This is syntactic sugar. A query that lists the prepared statements in the
session in which the function is run. It will return a list of alists of form:
((:NAME . "SNY24")
(:STATEMENT . "(SELECT name, salary FROM employee WHERE (city = $1))")
(:PREPARE-TIME . #<TIMESTAMP 25-11-2018T15:36:43,385>)
(:PARAMETER-TYPES . "{text}") (:FROM-SQL).
If the optional names-only parameter is
set to t, it will only return a list of the names of the prepared statements.
Returns a list of the databases to which the specified role can connect.
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.
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.
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.
Return a list of the sequences in a database. Turn them into keywords if strings-p is not true.
List the index names and the related columns in a single table. Each index will be in a separate sublist.
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.
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.
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.
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.
Returns a list of existing database template names.
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)
List constraints on a table. Table-name can be either a string or quoted. Turns constraints into keywords if strings-p is not true.
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.
Loads the Postgresql uuid-ossp contrib module. Once loaded, you can call uuid
generation functions such as uuid_generate_v4 within a query. E.g.
(query "select uuid_generate_v4()")
See Postgresql documentation at https://www.postgresql.org/docs/current/uuid-ossp.html
This function is default value of *QUERY-CALLBACK* and logs queries to *QUERY-LOG* if it is not NIL.
Returns a list of lists with schema, table name and approximate number of records in the currently connected database.
Returns t if the two parameters have matching types
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.
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.
Returns the postgresql oid for parameters which are going to be passed
from postmodern to postgresql in binary. Currently that only includes integers,
single-floats, double-floats and boolean. Everything else will be passed as
text for postgresql to interpret. We do not do arrays because passing them in Postgresql’s
binary format is actually more overhead than sending the string literal version. See
https://www.codesynthesis.com/pipermail/odb-users/2012-August/000688.html.
If you are wondering why text is not included in this function, many Postgresql
data types have no common lisp equivalent and therefore must be
passed as string literals. Specifying that something was text
when it is not will result in Postgresql throwing type mismatch errors.
Takes a list of parameters and returns the matching postgresql oid types
Takes two lists and validates that the lists have matching postgresql oid types.
Read SQL queries in given string and split them, returns a list
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")
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").
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.
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.
Parse and plan the given query, and store it with Postgresql under the given name.
Note that prepared statements are per-connection, so they can only be executed
through the same connection that prepared them. Also note that while the Postmodern package
will also stored the prepared query in the connection-meta slot of the connection, but
cl-postgres prepare-query does not. If the name is an empty string, Postgresql will not
store it as a reusable query. To make this useful in cl-postgres while
(connection-use-binary connection) is true, you need to pass a list of parameters with
the same type as you will be using when you call (exec-prepared).
For example:
(prepare-query connection "test6" "select $1, $2" ’(1 T))
(exec-prepared connection "test6" ’(42 nil) ’list-row-reader)
Returns t if the prepared statement exists in the current postgresql session, otherwise nil.
Read SQL queries in given file and split them, returns a list. Track included files so there is no accidental infinite loop. The default setting is to remove sql comments from the file before executing the sql code. If that causes problems, the remove-comments parameter can be set to nil.
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.
Immediately release a savepoint, commiting its results.
Rename a column in a table. Parameters can be strings or symbols. If the table
is not in the public schema, it needs to be fully qualified - e.g. schema.table.column
Returns t if successful.
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.
Re-establish a database connection for a previously closed connection object. (Calling this on a connection that is still open is harmless.)
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.
Invokes the retry-transaction restart, if found.
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.
Does the named role exist in this database cluster? Returns t or nil
Immediately roll back a savepoint, aborting the results.
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.
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.
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.
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.
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.
Tests whether a sequence with the given name exists. The name can be either a string or a symbol.
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.
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.
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))))
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.
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".
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.
Escape string data so it can be used in a query. Example:
(sql-escape-string "Puss in ’Boots’")
"E’Puss in ”Boots”’"
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.
Reads a string and removes any character that should be mapped to nothing per RFC 3454 and RFC 4013.
Reads a string and converts any character which should be mapped to a space pre RFC 3454 and RFC 4013 to a space.
Returns t if every character in the string is printable ascii.
Convert a string into an array of unsigned bytes containing its utf-8 representation.
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.
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.)
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.
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.
Return the size of a given postgresql table in k or m. Table-name can be either a string or quoted.
Less polite way of terminating at the database (as opposed to calling close-database). Faster than (cancel-backend pid) and more reliable.
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.
Close the prepared query given by name by closing the session connection.
Does not remove the query from the meta slot in connection. This is not the same as
keeping the connection open and sending Postgresql query to deallocate the named
prepared query.
Accepts a database connection and nil or t. The default for cl-postgres/Postmodern
is pass parameters to Postgresql as text (not in binary format). This is how it has
been since the beginning of Postmodern and the default is set this way in order to
avoid breaking existing user code. You can set Postmodern to pass integer, float
or boolean parameters to Postgresql in binary format on a connection basis when
the connection is created or you can use this function to change the existing connection
to use or not use binary parameter passing.
Calculate the amount of bytes needed to encode a string.
Convert a byte array containing utf-8 encoded characters into the string it encodes.
Determine the amount of bytes that are part of the character starting with a given byte.
Checking whether a string is a uuid. It does require the uuid string to be in hyphenated form. Like Postgresql, it will accept both upper and lower case, so looser than the specification which requires lower case only.
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.
Tests whether a view with the given name exists. Takes either a string or a symbol for the view name.
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.
Write a string to a byte-stream, encoding it as utf-8.
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).
transaction-handle)) ¶automatically generated reader method
transaction-handle)) ¶automatically generated writer method
Roll back the given logical transaction, regardless of whether it is an actual transaction or a savepoint.
transaction-handle)) ¶savepoint-handle)) ¶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.
transaction-handle)) ¶automatically generated reader method
transaction-handle)) ¶automatically generated writer method
Commit the given logical transaction, regardless of whether it is an actual transaction or a savepoint.
transaction-handle)) ¶savepoint-handle)) ¶This method returns a mapping (string to string) containing all the configuration parameters for the connection.
database-connection)) ¶automatically generated reader method
database-connection)) ¶automatically generated writer method
database-connection)) ¶automatically generated reader method
database-connection)) ¶automatically generated writer method
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.
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.
database-error)) ¶database-error)) ¶database-error)) ¶database-error)) ¶database-error)) ¶database-error)) ¶Delete the given dao from the database.
Disconnects a normal database connection, or moves a pooled connection into the pool.
pooled-database-connection)) ¶Add the connection to the corresponding pool, or drop it when the pool is full.
database-connection)) ¶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.
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.
field-description)) ¶automatically generated reader method
name.
field-description)) ¶automatically generated writer method
name.
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.
field-description)) ¶automatically generated reader method
field-description)) ¶automatically generated writer method
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.
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.
symbol) &rest args) ¶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.)
Combines make-instance with insert-dao. Make the instance of
the given class and insert it into the database, returning the created instance.
symbol) &rest args &key &allow-other-keys) ¶postgresql-notification)) ¶postgresql-notification)) ¶postgresql-notification)) ¶postgresql-notification)) ¶postgresql-notification)) ¶postgresql-notification)) ¶pid.
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.
database-connection)) ¶pooled-database-connection)) ¶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)).
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’]"
Transform a lisp type into a string containing something SQL understands. Default is to just use the type symbol’s name.
symbol) &rest args) ¶(eql string)) &rest args) ¶(eql s-sql:varchar)) &rest args) ¶(eql s-sql:numeric)) &rest args) ¶(eql float)) &rest args) ¶(eql double-float)) &rest args) ¶(eql s-sql:double-precision)) &rest args) ¶(eql s-sql::serial)) &rest args) ¶(eql s-sql::serial8)) &rest args) ¶(eql array)) &rest args) ¶(eql s-sql:db-null)) &rest args) ¶inconsistent-schema-name)) ¶invalid-database-name)) ¶malformed-composite-type-error)) ¶text.
Convert a Lisp value to its textual unescaped SQL
representation. Returns a second value indicating whether this value should be
escaped if it is to be put directly into a query. Generally any string is going
to be designated to be escaped.
You can define to-sql-string methods for your own datatypes if you want to be able to pass them to exec-prepared. When a non-NIL second value is returned, this may be T to indicate that the first value should simply be escaped as a string, or a second string providing a type prefix for the value. (This is different from s-sql::to-s-sql-string only in the handling of cons lists.
string)) ¶vector)) ¶cons)) ¶array)) ¶integer)) ¶float)) ¶double-float)) ¶ratio)) ¶(eql t))) ¶(eql nil))) ¶(eql :null))) ¶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.
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.
dao-class) name direct-slot-definitions) ¶sb-mop.
dao-class) &key column col-type &allow-other-keys) ¶Slots that have a :col-type option are column-slots.
sb-mop.
dao-class)) ¶Building a row reader and a set of methods can only be done after inheritance has been finalised.
sb-mop.
bulk-copier) stream) ¶bad-char-error) stream) ¶dao-class) (super-class standard-class)) ¶sb-mop.
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))))
Raised when a query is initiated on a disconnected connection object.
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.
error.
connection-does-not-exist.
connection-exception.
connection-failure.
data-exception.
database-connection-error.
feature-not-supported.
insufficient-resources.
integrity-violation.
internal-error.
invalid-sql-statement-name.
object-state-error.
operator-intervention.
program-limit-exceeded.
protocol-violation.
sqlclient-unable-to-establish-sqlconnection.
sqlserver-rejected-establishment-of-sqlconnection.
syntax-error-or-access-violation.
system-error.
transaction-resolution-unknown.
transaction-rollback.
Code: the Postgresql SQLSTATE code for the error
(see the Postgresql Manual Appendix A for their meaning). Not localizable.
Always present.
(quote nil)
:code
This slot is read-only.
Message: the primary human-readable error message.
This should be accurate but terse (typically one line). Always present.
:message
Detail: an optional secondary error message carrying
more detail about the problem. Might run to multiple lines or NIL if none is
available.
(quote nil)
:detail
This slot is read-only.
Hint: an optional suggestion what to do about the problem.
(quote nil)
:hint
This slot is read-only.
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.
(quote nil)
:context
This slot is read-only.
Query that led to the error, or NIL if no query was involved.
(quote cl-postgres::*current-query*)
This slot is read-only.
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.
common-lisp.
(quote nil)
:position
This slot is read-only.
The condition that caused this error, or NIL when it was not caused by another condition.
(quote nil)
:cause
This slot is read-only.
Used to wrap stream-errors and socket-errors, giving them a database-connection-error superclass.
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.
simple-warning.
simple-error.
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)
Representation of a database connection. Contains
login information in order to be able to automatically re-establish a
connection when it is somehow closed.
(setf connection-application-name).
connection-application-name.
(setf connection-available).
connection-available.
connection-db.
connection-host.
(setf connection-parameters).
connection-parameters.
connection-password.
connection-port.
(setf connection-service).
connection-service.
(setf connection-socket).
connection-socket.
(setf connection-timestamp-format).
connection-timestamp-format.
(setf connection-use-binary).
connection-use-binary.
connection-use-ssl.
connection-user.
disconnect.
reconnect.
| Initarg | Value |
|---|---|
:application-name | postmodern-default |
:host
This slot is read-only.
:port
This slot is read-only.
:db
This slot is read-only.
:user
This slot is read-only.
:password
This slot is read-only.
:ssl
This slot is read-only.
:binary
:service
:application-name
:socket
t
Also know as int8
Type for representing NULL values. Use like (or integer db-null) for declaring a type to be an integer that may be null.
Also known as int2
Bound to the current connection’s parameter table when executing a query.
Maps pool specifiers to lists of pooled connections.
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.
A copy of the default readtable that client code can fall back on.
This is used to communicate the fact that a slot is a column to effective-slot-definition-class.
A set of words that maybe should be disallowed from user names. Edit as you please.
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.
NIL outside of any aggregate environment, ’ARRAY or ’OBJECT within the respective environments.
T when the first member of a JSON Object or Array is encoded, afterwards NIL.
Designator for a function which maps string (a JSON Object key) to string (name of a Lisp symbol).
The default output stream for encoding operations.
The package where JSON Object keys etc. are interned. Default KEYWORD, NIL = use current *PACKAGE*.
Designator for a function which maps string (name of a Lisp symbol) to string (e. g. JSON Object key).
A lock to prevent multiple threads from messing with the connection pool at the same time.
A set of all PostgreSQL’s reserved words, for automatic escaping. Probably not a good idea to use these words as identifiers anyway.
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.
If the default path is reset, it will also reset this parameter which will get read by reconnect.
Unexported ordered list containing the known table definitions.
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.
Mapping between JSON literal names and Lisp boolean values.
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.
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.
Given a character, calls the writer function for every byte in the encoded form of that character.
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.
Def-drop-op accepts variables, strings or symbols as the identifier.
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.
Shorthand for defining binary readers.
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.
Create a function to read integers from a binary stream.
Create a function to write integers to a binary stream.
Helper macro for the functions that check whether an object exists. Only works for public schema
Helper macro for the functions that list tables, sequences, and views.
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.
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*).
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.
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.
Run BODY to encode a JSON aggregate type, delimited by BEGIN-CHAR and END-CHAR.
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.
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.
Aquire a lock for the pool when evaluating body (if thread support is present).
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.
Establish a SUBSTITUTE-PRINTED-REPRESENTATION restart for OBJECT and execute 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.
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.
Generates the sql string for the portion of altering a column.
Try to initiate a connection. Caller should close the socket if this raises a condition.
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.
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.)
Helper for the following two macros.
Convert an array of 0-255 numbers into the corresponding string of (lowercase) hex codes.
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.
Take a camel-case string and convert it into a string with Lisp-style hyphenation.
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.
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.
Returns t if the character should be mapped to nothing per RFC 3454 Table B.1 and RFC 4013
If character is mapped to space per RFC 3454 Table C.1.2 and RFC 4013, then return t, else nil
Returns t if the char is printable ascii.
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.
Returns t if character has char-code 0 (generally #Nul)
Returns t if the int is a printable ascii code-point.
This function does require the parameter to be a string and properly upper and lower cased.
Collects the export functions for a class (if any) and returns a list of lists of form (sql_name_of_field . export-function)
Collects the import functions for a class (if any) and returns a list of lists of form (sql_name_of_field . export-function)
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.
Takes a list of two items and returns a single string separated by a space. The items will be converted to sql compatible namestrings.
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.
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.
Returns a list of symbols of the names of the slots in a class.
Enumerate the slots in a class that refer to table rows.
Defines a row-reader for objects of a given class. Note that query fields are the Postgresql column names, not the dao slot names.
Build a list of superclasses of a given class that are DAO classes.
Returns a list of databases where the parameter may be a list of databases, a single string name or :current, :all or "all".
Helper function for macros which look for ’something but that has been converted to (quote something).
Returns t if char is a character we are going to disallow in a tag
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.
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.
Write the JSON representation (Object) of ALIST to STREAM (or to *JSON-OUTPUT*). Return NIL.
Return the JSON representation (Object) of ALIST as a string.
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).
Write the JSON representation (Object) of PLIST to STREAM (or to *JSON-OUTPUT*). Return NIL.
Return the JSON representation (Object) of PLIST as a string.
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.
Used to make sure a connection object is connected before doing anything with it.
Make sure a statement has been prepared for this connection. If overwrite is set to t (not the default), it will overwrite the existing query of the same name. Reminder that the meta info is a list of query, params.
Escape an array of octets in PostgreSQL’s horribly inefficient textual format for binary data.
Try to escape an expression at compile-time, if not possible, delay to runtime. Used to create stored procedures.
Helper function for building a composite table name
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.
Process table constraints that follow the closing parentheses in the table definition.
Provide interval limit options
Helper for the select operator. Turns the part following :from into the proper SQL syntax for joining tables.
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.)
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.
Note: temporary tables are unlogged tables. Having both :temp and :unlogged would be redundant.
Takes a Postgresql column name and tries to match it to a dao slot name symbol. This is trying to deal with the hyphens and underscores problem where Postgresql table field names cannot use hyphens but that is normal CL practice. The slot name symbol will be the full package::slot-name. field-name must be a string.
Returns the export function, if any, for a class and a column-name. Column name must be a symbol
Returns the import function, if any, for a class and a column-name. Column name must be a symbol
Create full pathname if included using a ir metacommand or include_relative.
Returns a list of (name, query, parameters) for a named prepared query.
Note the somewhat similar Postmodern version (find-postgresql-prepared-statement name) only
returns the query, not the parameters or name.
Returns the name of prepared statement (if any) postgresql has for this session that matches the query .
Formats have to be passed as arrays of 2-byte integers, with 1 indicating binary and 0 indicating plain text.
Currently assumes all parameters are normal strings
Returns a byte array
Generate a random alphanumeric nonce with a default length of 32.
The eventual client-proof needs to be base64 encoded
Assuming client-proof is in a usb8 array, returns client-proof as part of the final message as a base64 string
Assumes the server-nonce is a utf8 string
Takes an password (must be an ascii string) and server salt (by default presumed byte-array but can be set for :string or :hex) and an integer iterations. Digest is presumed to be :sha256 but can be set to other valid ironclad digests. returns a byte-array
Helper function for the following two macros. Note that it will attempt to automatically reconnect if database-connection-error, or admin-shutdown. It will reset any prepared statements triggering an invalid-sql-statement-name error. The generated function will overwrite old prepared statements triggering a duplicate-prepared-statement error and will pre-emptively overwrite an existing prepared statement of the same name the first time generate-prepared is called for this function name. Subsequent calls to the generated function will not overwrite unless postgresql throws a duplicate-prepared-statement error.
This gets a uuid (version 4) generated by Postgresql. If the uuid-ossp module is not loaded, it
will load it first. This is probably not something you want to use because it is
potentially two calls to Postgresql to get a uuid when you could do it in a single
call within a query when you are getting other information.
Read an error message from the socket and raise the corresponding database-error condition.
Get a database connection from the specified pool, returns nil if no connection was available.
Returns valid IPv4 or IPv6 address for the host.
Takes an integer and returns the size of the integer for postgresql purposes (int2, int4, int8)
Read an asynchronous notification message from the socket and signal a condition for it.
Returns a type-interpreter containing interpretation rules for this type.
Given an array of bytes and the amount of bytes to use, extract the character starting at the given start position.
Read a warning from the socket and emit it.
Function used by bind-message to decide whether to send parameters in binary.
Potential use for a single parameter to decide whether to send it in binary.
Reduce a list of strings to a single string, inserting a separator between them.
Check whether a connection object is connected, try to connect it if it isn’t.
Initiate SSL handshake with the PostgreSQL server, and wrap the socket in an SSL stream. When require is true, an error will be raised when the server does not support SSL. When hostname is supplied, the server’s certificate will be matched against it.
Takes a signed integer and returns a vector of unsigned bytes.
Takes a 16 byte integer and returns a vector of unsigned bytes with a length of 2.
Checking whether every item in an array is an int4
Checking whether the item is an int2
Takes a 32 byte integer and returns a vector of unsigned bytes with a length of 4
Checking whether every item in an array is an int4
Checking whether the item is an int4
Takes a 64 byte integer and returns a vector of unsigned bytes with a length of 8
Checking whether every item in an array is an int4
Takes a 8 byte positive integer and returns a vector of unsigned bytes with a length of 1 byte.
Checking whether the item is an int8
This interpreter is used for types that we have no specific interpreter for – it just reads the value as a string. (Values of unknown types are passed in text form.)
Decode a 64 bit time-related value based on the timestamp format used. Correct for sign bit when using integer format.
If the interpreter’s use-binary field is a function, call it and return the value, otherwise, return T or nil as appropriate.
Determine if we went the text or binary reader for this type interpreter and return the appropriate reader.
Checks whether a variable is a valid isolation-level keyword.
Intended for the JSON-EXPLICT-ENCODER. Converts a non-nil value to a value (:true) that creates a json true value when used in the explict encoder. Or (:false).
Intern STRING in the current *JSON-SYMBOLS-PACKAGE*.
Intended for the JSON-EXPLICT-ENCODER. Returns a non-nil value as itself, or a nil value as a json null-value
Returns ’i if the first characters in a line are the postgresql include file commands: i or include. Returns ’ir if the first characters in a line are postgresql include commands ir or include_relative. Returns nil otherwise.
Take a string with Lisp-style hyphentation and convert it to camel case. This is an inverse of CAMEL-CASE-TO-LISP.
If a col-type is a list, alist or plist, will set the Postgresql column to text.
If the item is a simple-date:timestamp instance, return a string in the form of yyyy-mm-dd hh:mm:ss:ms
Read server messages until either a new row can be read, or there are no more results. Return a boolean indicating whether any more results are available, and, if available, stores the amount of effected rows in *effected-rows*. Also handle getting out of copy-in/copy-out states (which are not supported).
Generates an appropriate expander function for a given operator with a given arity.
Call FUNCTION on the name and value of every bound slot in OBJECT.
Apply the hashing that PostgreSQL expects to a password.
Between two members of an Object or Array, print a comma separator.
Provide unique statement names.
Takes an octet-vector and, if it is shorter than the SIZE parameter, pads it to the SIZE parameter by adding 0 entries at the beginning.
Like parse-message but specifically when binary parameters are parsed.
Read a SQL query from STREAM, starting at whatever the current position is.
Returns another SQL query each time it’s called, or NIL when EOF is
reached expectedly. Signal end-of-file condition when reaching EOF in the
middle of a query.
See the following docs for some of the parser complexity background:
http://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING
Parser states are:
- EAT reading the query
- TAG reading a tag that could be an embedded $x$ tag or a closing tag
- EOT End Of Tag
- EQT Eat Quoted Text
- ETT Eat Tag-Quoted Text
- EDQ Eat Double-Quoted Text (identifiers)
- EOQ done reading the query
- ESC read escaped text (with backslash)
Takes a server response and a client-nonce. 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
tags.
Helper function which may be useful for certain macros.
Takes what might be a string, a symbol or a quoted-name in the form
’(quote name) and returns the string version of the name.
Read the fields of a null-terminated list of byte + string values and put them in an alist.
Read a byte array of the given length from a stream.
Read the field descriptions for a query result and put them into an array of field-description objects.
Read a null-terminated string from a stream. Interprets it as ASCII.
Read a given file and (default) remove the comments. Read lines from the redacted result and return them in a stream. Recursively apply i include instructions.
Read a null-terminated string from a stream. Takes care of encoding when UTF-8 support is enabled.
Used for supporting both plain string queries and S-SQL constructs. Looks at the argument at compile-time and wraps it in (sql ...) if it looks like an S-SQL query.
Join adjacent strings in a list; leave other values intact.
Take a string input, replace all the multi-line comments and single line comments, returning the resulting string.
Removes whitespace from strings.
Takes a string and a replacement char and replaces any character which is not alphanumeric or an asterisk with a specified character - by default an underscore and returns the modified string.
The default json-intern is not safe. Interns of many
unique symbols could potentially use a lot of memory.
An attack could exploit this by submitting something that is passed
through that has many very large, unique symbols. This version
is safe in that respect because it only allows symbols that already
exist.
Returns a list of schemas in the current for which a role will be granted privileges.
Send a close command to the server, giving it a name.
Used by cl-postgres:exec-prepared to Execute a previously parsed query, and apply the given row-reader to the result.
Send a parse command to the server, giving it a name.
Send a query to the server, and apply the given row-reader to the results.
Take an imput that may be a symbol, actual class or dao object and returns the actual class.
If the item is a simple-date:date instance, return a string in the form of yyyy-mm-dd
If the item is a simple-date:timestamp instance, return a string in the form of P3Y6M4DT12H30M5,3S represents a duration of three years, six months, four days, twelve hours, thirty minutes, five seconds and 300 milliseconds following ISO 8601. Note that milliseconds are a decimal fraction added to seconds and the separator is a comma, not a full stop. If the interval does not have milliseconds or the milliseconds = 0, that decimal fraction will not be added.
If the item is a simple-date:timestamp instance, return a string in the form of yyyy-mm-dd hh:mm:ss:ms
If the item is a simple-date:timestamp instance, return a string in the form of yyyy-mm-dd hh:mm:ss:ms
Insert - between lowercase and uppercase chars. Ignore _ + * and several consecutive uppercase.
Skip a given number of bytes in a binary stream.
Skip a null-terminated string.
Given a dao slot, this returns a downcased string of the name of a slot without the package name for use in find functions.
Helper function for split-on-keywords. Extracts the values associated with the keywords from an argument list, and checks for errors.
Takes an array of bytes which are encoded in base64, It should return a list
of three alists of the form:
(("r" . "odaUyoz0GpB5GxXLfe2Y8SVjZEosREsxzxhtXY1jiNebxJlohG8IRD1v")
("s" . "HV25Sl/1VAUF7k+Ddv42dQ==") ("i" . "4096") where "r" is the
server nonce,
"s" is a base64 encoded salt and "i" is the number of iterations for the
hash digest.
We do not use split-sequence building the cons cell because the equal sign can appear in the nonce or salt itself.
Compile-time expansion of forms into lists of stuff that evaluate
to strings (which will form a SQL query when concatenated). :default will
return ’ DEFAULT’
Expand a list of elements, adding a separator between them.
Takes a list of elements (symbols or strings) and returns a separated list of strings. If the element is a cons, then
Concatenate a list of strings into a single one.
Return a function which takes an argument and encodes it to STREAM as a Member of an Array. The encoding function is taken from the value of ENCODER (default is #’ENCODE-JSON).
Return a function which takes two arguments and encodes them to STREAM as a Member of an Object (String : Value pair).
Helper function to allow for fully qualified table names and non-qualified tables names that just exist in public schema or in a separately stated schema in the second parameter. Will thrown an error if the table-name is fully qualified and has a schema name different than the specified schema name.
Close a connection, notifying the server.
Checking whether every item in an array is text
Used to allow both strings and symbols as identifier - converts symbols to string with the S-SQL rules.
Turn a Lisp type expression into a SQL typename.
Try to re-synchronize a connection by sending a sync message if it hasn’t already been sent, and then looking for a ReadyForQuery message.
Signal an UNENCODABLE-VALUE-ERROR.
Calculate the length of the string encoded by the given bytes.
Takes a uuid string and creates a vector of unsigned bytes
Returns t if chr is letter, underscore, digits or dollar sign
checks whether the server-nonce begins with the client-nonce. Both need to be normal strings.
Returns location of whitespace in string. You probably do not want to allow whitespace in either user names or passwords.
Write a byte-array to a stream.
Write JSON representations (chars or escape sequences) of characters in string S to STREAM.
Write the JSON representation of the number NR to STREAM.
Write a JSON representation (String) of S to STREAM.
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.
Write a null-terminated string to a stream (encoding it when UTF-8 support is enabled.).
bad-char-error)) ¶bad-char-error)) ¶bad-char-error)) ¶bad-char-error)) ¶bad-char-error)) ¶bad-char-error)) ¶missing-i-file)) ¶bulk-copier)) ¶automatically generated reader method
direct-column-slot)) ¶automatically generated reader method
direct-column-slot)) ¶automatically generated reader method
direct-column-slot)) ¶automatically generated reader method
direct-column-slot)) ¶automatically generated reader method
direct-column-slot)) ¶automatically generated reader method
direct-column-slot)) ¶automatically generated reader method
direct-column-slot)) ¶automatically generated reader method
direct-column-slot)) ¶automatically generated reader method
direct-column-slot)) ¶automatically generated reader method
direct-column-slot)) ¶automatically generated reader method
direct-column-slot)) ¶automatically generated reader method
database-connection)) ¶automatically generated reader method
database-connection)) ¶automatically generated writer method
database-connection)) ¶automatically generated reader method
database-connection)) ¶automatically generated writer method
database-connection)) ¶automatically generated reader method
database-connection)) ¶automatically generated reader method
host.
database-connection)) ¶automatically generated reader method
pooled-database-connection)) ¶automatically generated reader method
pooled-database-connection)) ¶automatically generated writer method
database-connection)) ¶automatically generated reader method
port.
database-connection)) ¶automatically generated reader method
database-connection)) ¶automatically generated writer method
database-connection)) ¶automatically generated reader method
database-connection)) ¶automatically generated writer method
database-connection)) ¶automatically generated reader method
database-connection)) ¶automatically generated writer method
database-connection)) ¶automatically generated reader method
database-connection)) ¶automatically generated reader method
user.
bulk-copier)) ¶automatically generated reader method
bulk-copier)) ¶automatically generated reader method
bulk-copier)) ¶automatically generated writer method
bulk-copier)) ¶automatically generated reader method
bulk-copier)) ¶automatically generated reader method
database-error)) ¶database-error)) ¶hint.
database-error)) ¶Write a JSON representation of OBJECT to STREAM and return NIL.
standard-object) &optional stream) ¶Check to see if the object is a local-time:timestamp, or a simple-date timestamp, date or interval. Is so, handle those. If not then Write the JSON representation (Object) of the CLOS object O to STREAM (or to *JSON-OUTPUT*).
hash-table) &optional stream) ¶Write the JSON representation (Object) of the hash table H to STREAM (or to *JSON-OUTPUT*).
sequence) &optional stream) ¶Write the JSON representation (Array) of the sequence S (not an alist) to STREAM (or to *JSON-OUTPUT*).
list) &optional stream) ¶Write the JSON representation of the list S to STREAM (or to
*JSON-OUTPUT*), using one of the two rules specified by
first calling USE-GUESSING-ENCODER or USE-EXPLICIT-ENCODER.
The guessing encoder: If S is a list encode S as a JSON Array, if
S is a dotted list encode it as an Object (per ENCODE-JSON-ALIST).
The explicit decoder: If S is a list, the first symbol defines
the encoding:
If (car S) is ’TRUE return a JSON true value.
If (car S) is ’FALSE return a JSON false value.
If (car S) is ’NULL return a JSON null value.
If (car S) is ’JSON princ the strings in (cdr s) to stream
If (car S) is ’LIST or ’ARRAY encode (cdr S) as a a JSON Array.
If (car S) is ’OBJECT encode (cdr S) as A JSON Object,
interpreting (cdr S) either as an A-LIST or a P-LIST.
symbol) &optional stream) ¶Write the JSON representation of the symbol S to STREAM (or to *JSON-OUTPUT*). If S is boolean, a boolean literal is written. Otherwise, the name of S is passed to *LISP-IDENTIFIER-NAME-TO-JSON* and the result is written as String.
character) &optional stream) ¶JSON does not define a character type, we encode characters as Strings.
string) &optional stream) ¶Write the JSON representation of the string S to STREAM (or to *JSON-OUTPUT*).
number) &optional stream) ¶Write the JSON representation of the number NR to STREAM (or to *JSON-OUTPUT*).
signal an error which the user can correct by choosing to encode the string which is the printed representation of the OBJECT.
Override expansion of operators. Default is to just
place operator name in front, arguments between parentheses and
nothing behind it.
(eql :copy)) args-name0) ¶Move data between Postgres tables and filesystem files.
(eql :drop-role)) args-name0) ¶(eql :create-role)) args-name0) ¶Add a new role. A role is an entity that can own database objects
and have database privileges; a role can be considered a “user”, a
“group”, or both depending on how it is used.
:options to create role do not require values, e.g. (:create-role
’foo :options ’SUPERUSER ’NOINHERIT).
connection-limit, valid-until, role, in-role, admin are keyword options that accept values.
(eql :drop-database)) args-name0) ¶(eql :create-database)) args-name0) ¶Create a database.
If the database exists an error is raised.
(eql :create-rule)) args-name0) ¶(eql :drop-domain)) args-name0) ¶(eql :create-domain)) args-name0) ¶(eql :create-enum)) args-name0) ¶(eql :create-view)) args-name0) ¶(eql :create-sequence)) args-name0) ¶(eql :currval)) args-name0) ¶(eql :nextval)) args-name0) ¶(eql :truncate)) args-name0) ¶This query sql-op takes one or more table names and will truncate
those tables (deleting all the rows. The following keyword parameters
are optionally allowed and must be in this order.
:only will truncate only this table and not descendent tables.
:restart-identity will restart any sequences owned by the table.
:continue-identity will continue sequences owned by the table.
:cascade will cascade the truncation through tables using foreign keys.
(eql :drop-rule)) args-name0) ¶(eql :drop-type)) args-name0) ¶(eql :drop-view)) args-name0) ¶(eql :drop-sequence)) args-name0) ¶(eql :drop-index)) args-name0) ¶(eql :drop-table)) args-name0) ¶(eql :cascade)) args-name0) ¶(eql :create-unique-index)) args-name0) ¶(eql :create-index)) args-name0) ¶(eql :alter-sequence)) args-name0) ¶(eql :alter-table)) args-name0) ¶(eql :create-extended-table)) args-name0) ¶Create a table with more complete syntax where table-constraints and extended-table-constraints are lists. Note that with extended tables you can have tables without columns that are inherited or partitioned.
(eql :create-table)) args-name0) ¶(eql :create-composite-type)) args-name0) ¶Creates a composite type with a type-name and two or more
columns. Sample call would be:
(sql (:create-composite-type ’fullname (first-name text) (last-name text)))
(eql :window)) args-name0) ¶(eql :with-recursive)) args-name0) ¶(eql :with)) args-name0) ¶(eql :parens)) args-name0) ¶(eql :partition-by)) args-name0) ¶Partition-by allows aggregate or window functions to apply separately to
segments of a result. Partition-by accepts the following keywords:
:order-by, :rows-between, :range-between, :preceding, :unbounded-preceding,
:current-row, :unbounded-following and :following. See https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS for Postgresql documentation on usage.
Example:
(query
(:select (:as ’population.country ’country-name)
(:as ’population ’country-population)
’region-name
(:as (:over (:sum ’population)
(:partition-by ’region-name :order-by ’region-name
:rows-between :unbounded-preceding :current-row))
’regional-population)
:from ’population
:inner-join ’regions
:on (:= ’population.iso3 ’regions.iso3)
:where (:and (:not-null ’population.iso2)
(:= ’year 1976))))
(eql :over)) args-name0) ¶Over allows functions to apply to a result set, creating an additional column.
A simple example of usage would be:
(query (:select ’salary (:over (:sum ’salary))
:from ’empsalary))
A more complicated version using the :range-between operator could look like this:
(query (:limit
(:select (:as ’country ’country-name)
(:as ’population ’country-population)
(:as (:over (:sum ’population)
(:range-between :order-by ’country :unbounded-preceding
:unbounded-following))
’global-population)
:from ’population
:where (:and (:not-null ’iso2)
(:= ’year 1976)))
5))
(eql :rows-between)) args-name0) ¶Rows-between allows window functions to apply to different segments of a result set.
It accepts the following keywords:
:order-by, :rows-between, :range-between, :preceding, :unbounded-preceding,
:current-row, :unbounded-following and :following. See https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS for Postgresql documentation on usage.
An example could look like this :
(query
(:select (:as ’country ’country-name)
(:as ’population ’country-population)
(:as (:over (:sum ’population)
(:rows-between :order-by ’country :preceding 2 :following 2))
’global-population)
:from ’population
:where (:and (:not-null ’iso2)
(:= ’year 1976))))
(eql :range-between)) args-name0) ¶Range-between allows window functions to apply to different segments of a result set.
It accepts the following keywords:
:order-by, :rows-between, :range-between, :unbounded-preceding,
:current-row and :unbounded-following. Use of :preceding or :following will generate errors.
See https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS for Postgresql documentation on usage.
An example which calculates a running total could look like this :
(query
(:select (:as ’country ’country-name)
(:as ’population ’country-population)
(:as (:over (:sum ’population)
(:range-between :order-by ’country :unbounded-preceding :current-row))
’global-population)
:from ’population
:where (:and (:not-null ’iso2)
(:= ’year 1976))))
(eql :delete-from)) args-name0) ¶(eql :update)) args-name0) ¶(eql :insert-rows-into)) args-name0) ¶(eql :notify)) args-name0) ¶(eql :unlisten)) args-name0) ¶(eql :listen)) args-name0) ¶(eql :insert-into)) args-name0) ¶(eql :function)) args-name0) ¶(eql :for-share)) args-name0) ¶(eql :for-update)) args-name0) ¶(eql :set-constraints)) args-name0) ¶(eql :order-by)) args-name0) ¶(eql :limit)) args-name0) ¶(eql :fetch)) args-name0) ¶Fetch can be a more efficient way to do pagination instead of using limit and
offset. Fetch allows you to retrieve a limited set of rows, optionally offset
by a specified number of rows. In order to ensure this works correctly, you
should use the order-by clause. If the amount is not provided, it assumes
you only want to return 1 row.
https://www.postgresql.org/docs/current/sql-select.html
Examples:
(query (:fetch (:order-by (:select ’id :from ’historical-events) ’id) 5))
((1) (2) (3) (4) (5))
(query (:fetch (:order-by (:select ’id :from ’historical-events) ’id) 5 10))
((11) (12) (13) (14) (15))
(eql :var-samp)) args-name0) ¶The var-samp function returns the sample variance of the input values
(square of the sample standard deviation).
Example:
(query (:select (:var-samp ’salary) :from ’people))
(eql :var-pop)) args-name0) ¶The var-pop function returns the population variance of the input values
(square of the population standard deviation).
Example:
(query (:select (:var-pop ’salary) :from ’people))
(eql :variance)) args-name0) ¶Variance is a historical alias for var_samp. The variance function returns
the sample variance of the input values (square of the sample standard deviation).
Example:
(query (:select (:variance ’salary) :from ’people))
(eql :stddev-samp)) args-name0) ¶The stddev-samp function returns the sample standard deviation of the input
values.
Example:
(query (:select (:stddev-samp ’salary) :from ’people))
(eql :stddev-pop)) args-name0) ¶The stddev-pop function returns the population standard deviation of the
input values.
Example:
(query (:select (:stddev-pop ’salary) :from ’people))
(eql :stddev)) args-name0) ¶The stddev function returns the the sample standard deviation of the input
values. It is a historical alias for stddev-samp.
Example:
(query (:select (:stddev ’salary) :from ’people))
(eql :regr-syy)) args-name0) ¶The regr-syy function returns the sum(Y^2) - sum(Y)^2/N
("sum of squares" of the dependent variable).
Example:
(query (:select (:regr-syy ’height ’weight) :from ’people))
(eql :regr-sxy)) args-name0) ¶The regr-sxy function returns the sum(X*Y) - sum(X) * sum(Y)/N ("sum of products" of independent times dependent variable).
Example:
(query (:select (:regr-sxy ’height ’weight) :from ’people))
(eql :regr-sxx)) args-name0) ¶The regr-sxx function returns the sum(X^2) - sum(X)^2/N (“sum of squares”
of the independent variable).
Example:
(query (:select (:regr-sxx ’height ’weight) :from ’people))
(eql :regr-slope)) args-name0) ¶The regr-slope function returns the slope of the least-squares-fit linear
equation determined by the (X, Y) pairs.
Example:
(query (:select (:regr-slope ’height ’weight) :from ’people))
(eql :regr-r2)) args-name0) ¶The regr-r2 function returns the square of the correlation coefficient.
Example:
(query (:select (:regr-r2 ’height ’weight) :from ’people))
(eql :regr-intercept)) args-name0) ¶The regr-intercept function returns the y-intercept of the least-squares-fit
linear equation determined by the (X, Y) pairs.
Example:
(query (:select (:regr-intercept ’height ’weight) :from ’people))
(eql :regr-count)) args-name0) ¶The regr-count function returns the number of input rows in which both
expressions are nonnull.
Example:
(query (:select (:regr-count ’height ’weight) :from ’people))
(eql :regr-avgy)) args-name0) ¶The regr-avgy function returns the average of the dependent
variable (sum(Y)/N).
Example:
(query (:select (:regr-avgy ’height ’weight) :from ’people))
(eql :regr-avgx)) args-name0) ¶The regr-avgx function returns the average of the independent
variable (sum(X)/N)
Example:
(query (:select (:regr-avgx ’height ’weight) :from ’people))
(eql :mode)) args-name0) ¶Mode is used to find the most frequent input value in a group. See e.g.
https://www.postgresql.org/docs/10/static/functions-aggregate.html
and article at
https://tapoueh.org/blog/2017/11/the-mode-ordered-set-aggregate-function/.
(eql :array-agg)) args-name0) ¶Array-agg returns a list of values concatenated into an array. Allowable optional keyword parameters are :distinct, :order-by and :filter.
Example:
(query (:select ’g.id
(:as (:array-agg ’g.users :filter (:= ’g.canonical "Y"))
’canonical-users)
(:as (:array-agg ’g.users :filter (:= ’g.canonical "N"))
’non-canonical-users)
:from (:as ’groups ’g)
:group-by ’g.id)
Note that order-by in array-agg requires postgresql 9.0 or later. Filter requires postgresql 9.4 or later. See tests.lisp for examples.
(eql :string-agg)) args-name0) ¶String-agg allows you to concatenate strings using different types of
delimiter symbols. Allowable optional keyword parameters are :distinct,
:order-by and :filter
Examples:
(query (:select (:as (:string-agg ’bp.step-type "," ) ’step-summary)
:from ’business-process))
(query (:select ’mid (:as (:string-agg ’y "," :distinct
:order-by (:desc ’y))
’words)
:from ’moves))
(query (:select (:string-agg ’name ","
:order-by (:desc ’name)
:filter (:< ’id 4))
:from ’employee))
Note that order-by in string-agg requires postgresql 9.0 or later. Filter requires postgresql 9.4 or later.See tests.lisp for examples.
(eql :grouping-sets)) args-name0) ¶Grouping-sets allows multiple group-by in a single query
https://www.postgresql.org/docs/current/static/queries-table-expressions.html
More complex grouping operations are possible using the concept of grouping
sets. The data selected by the FROM and WHERE clauses is grouped separately
by each specified grouping set, aggregates computed for each group just as
for simple GROUP BY clauses, and then the results returned.
This operator requires postgresql 9.5 or later. Examples:
(query (:select ’city (:as (:extract ’year ’start-date) ’joining-year)
(:as (:count 1) ’employee_count)
:from ’employee
:group-by (:grouping-sets (:set ’city (:extract ’year ’start-date)))))
(query (:select ’c1 ’c2 ’c3 (:sum ’c3)
:from ’table-name
:group-by (:grouping-sets (:set ’c1 ’c2) (:set ’c1) (:set ’c2)
(:set))))
(eql :select)) args-name0) ¶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, :cross-join (or those with -lateral,
e.g :left-join-lateral, :right-join-lateral, :inner-join-lateral, :outer-join-lateral).
S-sql will not accept :join, use :inner-join instead.
Then comes a table name or subquery,
then there is an optional :with-ordinality or :with-ordinality-as alisa
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.
A few examples:
(query (: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)))
(query (:select ’i.* ’p.*
:from (:as ’individual ’i)
:inner-join (:as ’publisher ’p)
:using (’individualid)
:left-join-lateral (:as ’anothertable ’a)
:on (:= ’a.identifier ’i.individualid)
:where (:= ’a.something "something")))
(query (:select ’t1.id ’a.elem ’a.nr
:from (:as ’t12 ’t1)
:left-join (:unnest (:string-to-array ’t1.elements ","))
:with-ordinality-as (:a ’elem ’nr)
:on ’t))
(eql :raw)) args-name0) ¶(eql :type)) args-name0) ¶Type will specify the datatype for a value. It uses the normal sql :: syntax.
The type can be quoted but does not need to be quoted. It does not accept
variables. As an example:
(query (:select (:type "2018-01-01" ’date)) :single)
(query (:select (:type "2018-01-01" date)) :single)
(eql :dot)) args-name0) ¶(eql :empty-set)) args-name0) ¶Returns a list containing a string of two parentheses as an empty set.
(eql :set)) args-name0) ¶(eql :case)) args-name0) ¶(eql :between-symmetric)) args-name0) ¶(eql :between)) args-name0) ¶(eql :covar-samp)) args-name0) ¶The covar-samp function returns the sample covariance between a set of
dependent and independent variables.
Example:
(query (:select (:covar-samp ’height ’weight) :from ’people))
(eql :covar-pop)) args-name0) ¶The covar-pop function returns the population covariance between a set of
dependent and independent variables.
Example:
(query (:select (:covar-pop ’height ’weight) :from ’people))
(eql :corr)) args-name0) ¶The corr function returns the correlation coefficient between a set of
dependent and independent variables.
Example:
(query (:select (:corr ’height ’weight) :from ’people))
(eql :percentile-dist)) args-name0) ¶Requires Postgresql 9.4 or higher. There are two required keyword parameters
:fraction and :order-by. Percentile-dist returns the first input value whose
position in the ordering equals or exceeds the specified fraction. If the
fraction parameter is an array eturns an array of results matching the shape
of the fractions parameter, with each non-null element replaced by the input
value corresponding to that percentile.
Examples:
(query (:select (:percentile-dist :fraction 0.5 :order-by ’number-of-staff)
:from ’schools))
(query (:select (:percentile-dist :fraction array[0.25 0.5 0.75 1]
:order-by ’number-of-staff)
:from ’schools))
(eql :percentile-cont)) args-name0) ¶Requires Postgresql 9.4 or higher. Percentile-cont returns a value
corresponding to the specified fraction in the ordering, interpolating between
adjacent input items if needed. There are two required keyword parameters
:fraction and :order-by. If the fraction value is an array, then it returns an
array of results matching the shape of the fractions parameter, with each
non-null element replaced by the value corresponding to that percentile.
Examples:
(query (:select (:percentile-cont :fraction 0.5 :order-by ’number-of-staff)
:from ’schools))
(query (:select (:percentile-cont :fraction array[0.25 0.5 0.75 1]
:order-by ’number-of-staff)
:from ’schools))
(eql :every)) args-name0) ¶Returns true if all input values are true, otherwise false. Allowed
keyword parameters are distinct and filter. Note that if the filter keyword is
used, the filter must be last in the every args. If distinct is used, it must
come before filter. Unlike standard sql, the word ’where’ is not used inside
the filter clause (s-sql will properly expand it). E.g.
(query (:select ’* (:every (:like ’studname "%h"))
:from ’tbl-students
:group-by ’studname ’studid ’studgrades))
See tests.lisp for examples.
(eql :min)) args-name0) ¶Returns the minimum value of a set of values. Allowed keyword parameters
are distinct and filter. Note that if the filter keyword is used, the filter
must be last in the min args. If distinct is used, it must come before filter.
Unlike standard sql, the word ’where’ is not used inside the filter
clause (s-sql will properly expand it). E.g.
(query (:select (:min ’*) (:min ’* :filter (:= 1 ’bid)) ’id
:from ’pbbench-history))
See tests.lisp for more examples.
(eql :max)) args-name0) ¶Max returns the maximum value of a set of values. Allowed keyword parameters
are distinct and filter. Note that if the filter keyword is used, the filter must
be last in the max args. If distinct is used, it must come before filter. Unlike
standard sql, the word ’where’ is not used inside the filter clause (s-sql will
properly expand it). E.g.
(query (:select (:max ’*) (:max ’* :filter (:= 1 ’bid)) ’id
:from ’pbbench-history))
See tests.lisp for more examples.
(eql :sum)) args-name0) ¶Sum calculates the total of a list of values. Allowed keyword parameters
are distinct and filter. Note that if the keyword filter is used, the filter
must be last in the sum args. If distinct is used, it must come before filter.
Unlike standard sql, the word ’where’ is not used inside the filter
clause (s-sql will properly expand it). E.g.
(query (:select (:sum ’*) (:sum ’* :filter (:= 1 ’bid)) ’id
:from ’pbbench-history))
See tests.lisp for examples.
(eql :avg)) args-name0) ¶Avg calculates the average value of a list of values. Allowed keyword
parameters are distinct and filter. Note that if the filter keyword is used,
the filter must be last in the avg args. If distinct is used, it must come
before filter. Unlike standard sql, the word ’where’ is not used inside the
filter clause (s-sql will properly expand it). E.g.
(query (:select (:avg ’*) (:avg ’* :filter (:= 1 ’bid)) ’id
:from ’pbbench-history))
See tests.lisp for examples.
(eql :count)) args-name0) ¶Count returns the number of rows. It can be the number of rows collected
by the select statement as in:
(query (:select (:count ’*) :from ’table1 :where (:= ’price 100)))
or it can be a smaller number of rows based on the allowed keyword parameters
:distinct and :filter as in:
(query (:select (:count ’memid :distinct) :from ’cd.bookings))
or
(query (:select (:as (:count ’* :distinct) ’unfiltered)
(:as (:count ’* :filter (:= 1 ’bid)) ’filtered)
:from ’testtable))
Note that if used, the filter must be last in the count args. If distinct is
used, it must come before filter. Unlike standard sql, the word ’where’ is not
used inside the filter clause. E.g.
(sql (:select (:count ’*) (:count ’* :filter (:= 1 ’bid)) ’id
:from ’pbbench-history))
See tests.lisp for examples.
(eql :values)) args-name0) ¶values statement
(eql :extract)) args-name0) ¶(eql :not-in)) args-name0) ¶(eql :in)) args-name0) ¶(eql :not-null)) args-name0) ¶(eql :is-null)) args-name0) ¶(eql :is-false)) args-name0) ¶(eql :is-true)) args-name0) ¶(eql :exists)) args-name0) ¶(eql :cast)) args-name0) ¶Cast is one of two functions that help convert one type of data to another.
The other function is type. An example use of cast is:
(query (:select (:as (:cast (:as (:* 50 (:random)) ’int)) ’x)
:from (:generate-series 1 3)))
One syntactic difference between cast and type is that the cast function requires that the datatype be quoted or a variable be passed as the type.
(let ((type ’text))
(query (:select (:cast (:as "20" type)))
:single))
(eql :integer)) args-name0) ¶(eql :date)) args-name0) ¶(eql :age)) args-name0) ¶(eql :make-timestamptz)) args-name0) ¶Takes lists of (time-unit value) and returns a timestamptz type. e.g. (make-interval ("days" 10)("hours" 4)).
(eql :make-timestamp)) args-name0) ¶Takes lists of (time-unit value) and returns a timestamp type. e.g. (make-interval ("days" 10)("hours" 4)).
(eql :make-interval)) args-name0) ¶Takes lists of (time-unit value) and returns an interval type. e.g. (make-interval ("days" 10)("hours" 4)).
(eql :timestamp)) args-name0) ¶(eql :local-time)) args-name0) ¶LOCALTIME and LOCALTIMESTAMP deliver values without time zone. They optionally take a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field. Without a precision parameter, the result is given to the full available precision.
(eql :local-timestamp)) args-name0) ¶LOCALTIME and LOCALTIMESTAMP deliver values without time zone. They optionally
take a precision parameter, which causes the result to be rounded to that many
fractional digits in the seconds field. Without a precision parameter, the
result is given to the full available precision. Precision
only applies to seconds.
(eql :current-time)) args-name0) ¶Current-time and Current-timestamp deliver values with time zones. They optionally take a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field. Without a precision parameter, the result is given to the full available precision. Precision only applies to seconds.
(eql :current-timestamp)) args-name0) ¶Current-time and Current-timestamp deliver values with time zones. They optionally take a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field. Without a precision parameter, the result is given to the full available precision. Precision only applies to seconds.
(eql :current-date)) args-name0) ¶Provides the current time. The default is universal time. If you want
a more human readable approach, you can use :to-char. As an example:
(query (:select (:current-date) (:to-char (:current-date) "YYYY-MM-DD")))
((3751488000 "2018-11-18"))
(eql :interval)) args-name0) ¶Interval takes a string.
See https://www.postgresql.org/docs/current/static/datatype-datetime.html
It optionally take a precision parameter, which causes the result to be rounded
to that many fractional digits in the seconds field. Without a precision
+parameter, the result is given to the full available precision. Precision
only applies to seconds.
(eql :[])) args-name0) ¶This slices arrays. Sample usage would be:
(query (:select (:[] ’provinces 1) :from ’array-provinces
:where (:= ’name "Germany"))
(eql :array[])) args-name0) ¶This handles statements that include functions in the query such as (:+ 1 2), (:pi) in the array whereas just passing an array as #(1.0 2.4) does not and you are not selecting into an array, so do not use :array.
(eql :array)) args-name0) ¶This is used when calling a select query into an array. See sample usage.
(eql :all)) args-name0) ¶(eql :any)) args-name0) ¶Any needs to be considered as a special case. Postgres has both a function-call-style any and an infix any, and S-SQL’s syntax doesn’t allow them to be distinguished. As a result, postmodern has a regular :any sql-op and a :any* sql-op, which expand slightly differently.
(eql :any*)) args-name0) ¶Any needs to be considered as a special case. Postgres has both a function-call-style any and an infix any, and S-SQL’s syntax doesn’t allow them to be distinguished. As a result, postmodern has a regular :any sql-op and a :any* sql-op, which expand slightly differently.
(eql :distinct)) args-name0) ¶(eql :@@)) args-name0) ¶(eql :as)) args-name0) ¶(eql :nulls-last)) args-name0) ¶(eql :nulls-first)) args-name0) ¶(eql :desc)) args-name0) ¶(eql :asc)) args-name0) ¶(eql :||)) args-name0) ¶The concatenation operator combines two or more columns into a single column
return. E.g:
(query (:select ’countries.id (:|| ’countries.name "-" ’regions.name)
:from ’countries ’regions
:where (:and (:= ’regions.id ’countries.region-id)
(:= ’countries.name "US"))))
((21 "US-North America"))
(eql :|%#|)) args) ¶(eql :%%)) args) ¶(eql :unary)) args) ¶(eql :|#=|)) args) ¶(eql :<@)) args) ¶(eql :|?\||)) args) ¶(eql :?&)) args) ¶(eql :?)) args) ¶(eql :=>)) args) ¶(eql :->)) args) ¶(eql :@<)) args) ¶(eql :@>)) args) ¶(eql :~=)) args) ¶(eql :|\|>>|)) args) ¶(eql :|\|&>|)) args) ¶(eql :@)) args) ¶(eql :>>)) args) ¶(eql :|<<\||)) args) ¶(eql :<<)) args) ¶(eql :&>)) args) ¶(eql :|&<\||)) args) ¶(eql :&<)) args) ¶(eql :&&)) args) ¶(eql :except-all)) args) ¶(eql :except)) args) ¶(eql :intersect-all)) args) ¶(eql :intersect)) args) ¶(eql :|#>>|)) args) ¶(eql :|#>|)) args) ¶(eql :->>)) args) ¶(eql :ilike)) args) ¶(eql :like)) args) ¶(eql :!~*)) args) ¶(eql :!~)) args) ¶(eql :~*)) args) ¶(eql :^)) args) ¶(eql :>=)) args) ¶(eql :<=)) args) ¶(eql :>)) args) ¶(eql :<)) args) ¶(eql :<>)) args) ¶(eql :!=)) args) ¶(eql :/)) args) ¶(eql :=)) args) ¶(eql :~)) args) ¶(eql :-)) args) ¶(eql :union-all)) args) ¶(eql :union)) args) ¶(eql :or)) args) ¶(eql :and)) args) ¶(eql :|\|\||)) args) ¶(eql :|\||)) args) ¶(eql :&)) args) ¶(eql :%)) args) ¶(eql :*)) args) ¶(eql :+)) args) ¶(eql :not)) args) ¶field-description)) ¶automatically generated reader method
field-description)) ¶automatically generated reader method
field-description)) ¶automatically generated writer method
missing-i-file)) ¶direct-column-slot)) ¶automatically generated reader method
missing-i-file)) ¶mismatched-parameter-types)) ¶mismatched-parameter-types)) ¶savepoint-handle)) ¶automatically generated reader method
savepoint-handle)) ¶automatically generated reader method
name.
savepoint-handle)) ¶automatically generated reader method
savepoint-handle)) ¶automatically generated writer method
effective-column-slot)) ¶automatically generated reader method
direct-column-slot)) ¶automatically generated reader method
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. This method is different from cl-postgres:to-sql-string
only with respect to its handling of cons lists. That method had been doing double
duty and now it is limited to passing parameters.
You can define to-s-sql-string methods for your own datatypes.
string)) ¶vector)) ¶cons)) ¶array)) ¶integer)) ¶float)) ¶double-float)) ¶ratio)) ¶(eql t))) ¶(eql nil))) ¶(eql :null))) ¶transaction-handle)) ¶automatically generated reader method
transaction-handle)) ¶automatically generated reader method
transaction-handle)) ¶automatically generated writer method
type-interpreter)) ¶automatically generated reader method
type-interpreter)) ¶automatically generated writer method
type-interpreter)) ¶automatically generated reader method
oid.
type-interpreter)) ¶automatically generated writer method
oid.
type-interpreter)) ¶automatically generated reader method
type-interpreter)) ¶automatically generated writer method
type-interpreter)) ¶automatically generated reader method
type-interpreter)) ¶automatically generated writer method
unencodable-value-error)) ¶unencodable-value-error)) ¶unknown-symbol-error)) ¶unknown-symbol-error)) ¶error.
Text message indicating what went wrong with the validation.
(quote nil)
:message
The value of the field for which the error is signalled.
(quote nil)
:value
The normalization form for the error was signalled.
(quote nil)
:normalization-form
Invalid-database-name indicates that this database does not
exist in this cluster or the user does not have the permissions necessary to
access this database.
error.
text.
error.
:base-filename
This slot is read-only.
This is raised if something really unexpected
happens in the communcation with the server. Should only happen in
case of a bug or a connection to something that is not a (supported)
PostgreSQL server at all.
error.
:message
Signalled when a datum is passed to ENCODE-JSON (or another encoder function) which actually cannot be encoded.
type-error.
| Initarg | Value |
|---|---|
:expected-type | t |
:context
Signalled by safe-json-intern when a symbol that is not already interned in *json-symbols-package* is found.
parse-error.
:datum
:own-connection
This slot is read-only.
:database
This slot is read-only.
:table
This slot is read-only.
:columns
This slot is read-only.
common-lisp.
0
Type of slots that refer to database columns.
standard-direct-slot-definition.
:col-type
This slot is read-only.
:col-default
This slot is read-only.
:col-identity
This slot is read-only.
:col-unique
This slot is read-only.
:col-collate
This slot is read-only.
:col-primary-key
This slot is read-only.
:col-interval
This slot is read-only.
:col-check
This slot is read-only.
:col-references
This slot is read-only.
:col-export
This slot is read-only.
:col-import
This slot is read-only.
This slot is read-only.
standard-effective-slot-definition.
postmodern::*direct-column-slot*
This slot is read-only.
Description of a field in a query result.
Type for database connections that are pooled.
Stores the arguments used to create it, so different pools can be
distinguished.
:pool-type
Simple box type for storing the state and the associated database connection of a savepoint.
(error "savepoint name is not provided.")
:name
This slot is read-only.
t
postmodern:*database*
This slot is read-only.
Simple box type for storing the status and the
associated database connection of a transaction. When open-p is nil,
the transaction has been aborted or committed. commit-hooks and
abort-hooks hold lists of functions (which should require no
arguments) to be executed at commit and abort time, respectively.
Information about type interpreter for types coming
back from the database. use-binary is either T for binary, nil for
text, or a function of no arguments to be called to determine if
binary or text should be used. The idea is that there will always be
a text reader, there may be a binary reader, and there may be times
when one wants to use the text reader.
Int4-array is an array of integers of size 2
Int4-array is an array of integers of size 4
Int8-array is an array of integers of size 8
Text-array is an array of strings
| Jump to: | !
%
(
A B C D E F G I J L M N O P Q R S T U V W |
|---|
| Jump to: | !
%
(
A B C D E F G I J L M N O P Q R S T U V W |
|---|
| Jump to: | %
*
+
A B C D E F G H I M N O P Q R S T U V |
|---|
| Jump to: | %
*
+
A B C D E F G H I M N O P Q R S T U V |
|---|
| Jump to: | A B C D E F I J L M N O P Q R S T U V |
|---|
| Jump to: | A B C D E F I J L M N O P Q R S T U V |
|---|