3 - Added :analyze operator
4 - Added :using option for :delete-from
5 - Additional S-SQL Documentation
6 ## Updated version numbers in the asd files
9 - Added :call operator to s-sql (so that you can use the s-sql syntax to call a Postgresql procedure) and substantial more s-sql examples in the documentation.
11 - Fixed bug in create-composite-type that disallowed certain multiword parameters
12 - Fixed bug in to-type-name and sql-type-name that precluded custom types
13 - Fixed bug in :create-table ability to create generated columns.
14 - Fixed bug in to-tsvector calls
16 - Added cube operators
18 - Added more documentation examples and tests for s-sql
21 Fixed bug in thread handling when using binary parameters
25 Dollar Quoted tags are allowed in files. Prior to Postmodern version 1.33.8 only
26 alphabetic characters were allowed in tags. Postgresql documentation and industry
27 practice allow any character to be in a dollar quoted tag. Postmodern version 1.33.8
28 relaxes the alphabetic character requirement. The only limitation now is that digit
29 characters cannot be in the first position in a tag.
31 This version also fixes a bug in the postmodern function drop-role. Previously if a role
32 owned objects in multiple databases, the drop-role function tried to drop the role before
33 all owned objects were reassigned owners.
36 Changes in cl-postgres and s-sql to allow use of plain proper lists in parameterized queries. Previously only vectors could be used. The following show examples using both vectors and lists in queries using both raw sql and s-sql.
38 (query "select name from employee where id = any($1)"
40 (query "select name from employee where id = any($1)"
43 (let ((emp-ids #(1 2)))
44 (query "select name from employee where id = any($1)"
47 (let ((emp-ids '(1 2)))
48 (query "select name from employee where id = any($1)"
51 (query (:select 'name :from 'employee :where (:= 'id (:any* '$1)))
55 (query (:select 'name :from 'employee :where (:= 'id (:any* '$1)))
61 Plain proper lists can also now be used in s-sql queries using :in. Previously you needed to use :set
63 (query (:select 'name :from 'employee :where (:in 'id (:set 1 3 4))))
64 '(("Jason") ("Celia") ("Linda"))
66 Now you can also provide a list.
68 (let ((emp-ids '(1 2)))
69 (query (:select 'name :from 'employee :where (:in 'id emp-ids))))
70 (("Jason") ("Robert"))
74 Postmodern/cl-postgres now returns an empty array if Postgresql returns an empty array.
76 The previous behavior had been to return nil, but I have been convinced by a user to make this
80 Discontinue support for versions of sbcl < 1.2.5 which were compiled without support for bsd-sockets.
82 Drop unwarrented assumption that the role 'postgres' will always exist when dropping a role resulting in ownership changes of postgresql objects.
84 Add more documentation on the limitation in s-sql on using lists in a parameterized statement. If you are trying to use a list in a parametized statement, you can't. You have to convert the list to a vector and use "any" rather than "in."
86 Add additional support for ssl connections to allow use of a root certificate for validation. Set \*ssl-root-ca-file* to the pathname for the root certificate.
89 Fix bug in a warning in execute-file that referred to the current package rather than Postmodern.
91 Added retry-transaction restart in the call-with-transaction function
95 Fix bug in export functions when user tries to export nil into a database
97 Fix bug in import functions when functions are called but the current package has changed.
98 Note to self. Pay attention to needs for fully qualified symbols (including package names) and how to test them for equality.
101 Dao Export and Import Functions (Postmodern v. 1.33.1 and newer)
103 There may be times when the types of values in a dao slot do not have comparable types in Postgresql. For purposes of the following example, assume you have slots that you want to contain lists. Postgresql does not have a "list" data type. Postgresql arrays must be homogeneous but CL lists do not have that limitation. What to do?
105 One method would be to use text columns or jsonb columns in Postgresql and have functions that convert as necessary going back and forth. In the following example we will use text columns in Postgresql and write CL list data to string when we "export" the data to Postgresql and then convert from string when we "import" the data from Postgresql into a dao-class instance.
107 Consider the following dao-class definition. We have added additional column keyword parameters :col-export and :col-import. These parameters refer to functions which will convert the values from that slot to a valid Postgresql type (in our example, a string) on export to the database and from that Postgresql type to the type we want in this slot on import from the database.
111 ((id :col-type integer :col-identity t :accessor id)
112 (name :col-type text :col-unique t :col-check (:<> 'name "")
113 :initarg :name :accessor name)
114 (rlist :col-type (or text db-null) :initarg :rlist :accessor rlist
115 :col-export list-to-string :col-import string-to-list)
116 (alist :col-type (or text db-null) :initarg :alist :accessor alist
117 :col-export list-to-string :col-import string-to-alist)
118 (plist :col-type (or text db-null) :initarg :plist :accessor plist
119 :col-export list-to-string :col-import string-to-plist))
120 (:metaclass dao-class)
124 Now we need to define the import functions. When writing your import functions, pay attention to how you want to handle nil or :NULL values as well as how you might want to error check the conversion from a Postgresql datatype to a CL datatype.
127 (defun string-to-list (str)
128 "Take a string representation of a list and return a lisp list.
129 Note that you need to handle :NULLs."
130 (cond ((eq str :NULL)
133 (with-input-from-string (s str) (read s)))
137 And now we need to define the export function. In our example we are just going to be using format to write the CL value to a string. You are responsible for writing an export function that does what you need. This example just tells Postgresql to insert a string "unknown" if the slot value is not a list. You would need more error checking and condition handling.
140 (defun list-to-string (val)
141 "Simply uses (format ..) to write a list out as a string"
143 (format nil "~a" val)
147 # Changelog v. 1.33.0
148 This version of Postmodern now provides the ability to pass parameters to Postgresql in binary format IF that format is available for that datatype. Currently this means int2, int4, int8, float, double-float (except clisp) and boolean. Rational numbers continue to be passed as text.
150 The flag is set in the database connection object. (Thank you Cyrus Harmon for suggesting that). This means it can be set either in the initial connection to the database or using the use-binary-parameters function to set it after the initial connection has been established. If you are using multiple connections, some can be set to use binary parameters, some not.
152 If a query to Postgresql does not have a table column which would allow Postgresql to determine the correct datatype and you do not specify differently, Postgresql will treat the parameters passed with the query as text. The default text setting with results:
155 (query "select $1" 1 :single)
157 (query "select $1" 1.5 :single)
159 (query "select $1" T :single)
161 (query "select $1" nil :single)
163 (query "select $1" :NULL :single)
167 You can specify parameter type as so:
170 (query "select $1::integer" 1 :single)
174 Setting the use-binary slot in the database connection object to t has the following results:
177 (query "select $1" 1 :single)
179 (query "select $1" 1.5 :single)
181 (query "select $1" T :single)
183 (query "select $1" nil :single)
185 (query "select $1" :NULL :single)
189 The default for cl-postgres/Postmodern is to continue to pass parameters to Postgresql as text (not in binary format) in order to avoid breaking existing user code. If you want to pass parameters to Postgresql in binary format and want to set that up when you are making the database connection, the following examples may help. We continue the difference in the signatures (cl-postgres using optional parameters and postmodern using keyword parameters) because of the expected downstream breakage if we shifted cl-postgres:open-database to using keyword parameters.
191 The signature for opening a database in cl-postgres:
194 (defun open-database (database user password host
195 &optional (port 5432) (use-ssl :no)
196 (service "postgres") (application-name "")
201 or your favorite macro.
203 In postmodern you have the connect function or the with-connection macro:
206 (defun connect (database-name user-name password host
207 &key (port 5432) pooled-p
208 (use-ssl *default-use-ssl*)
211 (application-name ""))
214 (defmacro with-connection (spec &body body)
215 `(let ((*database* (apply #'connect ,spec)))
216 (unwind-protect (progn ,@body)
217 (disconnect *database*))))
220 In any case, you can set the flag after the connection is established with the use-binary-parameters function:
223 (pomo:use-binary-parameters *database* t)
225 (cl-postgres:use-binary-parameters some-database-connection t)
228 Using binary parameters does tighten type checking when using prepared queries. You will not be able to use prepared queries with varying formats. In other words, if you have a prepared query that you pass an integer as the first parameter and a string as the second parameter the first time it is used, any subsequent uses of that prepared query during that session will also have to pass an integer as the first parameter and a string as the second parameter.
230 Benchmarking does indicate a slight speed and consing benefit to passing parameters as binary, but your mileage will vary depending on your use case.
232 In addition, this version also adds the ability to have queries returned as vectors of vectors, using a vectors keyword.
235 (query "select id, some_int, some_text from tests_data :where id = 1" :vectors)
237 (query (:select 'id 'some-int 'some-text :from 'test-data)
239 #(#(1 2147483645 "text one")
244 Like :array-hash, if there is no result it will return an empty array, not nil.
245 # Changelog v. 1.32.9
246 Adds new utility functions
248 - table-description-menu which allows you to pick and choose
249 what table characteristics you want returned. See giant docstring for details.
251 - get-schema-comment which takes a schema name and returns the schema comment
254 - list-check-constraints which takes a fully qualified table name and returns
255 a list of lists of check constraints where each sublist has the form
256 of (check-constraint-name check).
258 Example: (list-check-constraints "s2.employees")
259 (("employees_birth_date_check" "CHECK (birth_date > '1900-01-01'::date)")
260 ("employees_check" "CHECK (start_date > birth_date)")
261 ("employees_salary_check" "CHECK (salary > 0::numeric)"))
264 get-column-comments (the parameter string has changed if you were using the internal version)
265 get-all-table-comments
269 Fixes a bug when trying to connect to a database using ssl. If the keyword :try was used,
270 the connection would not fall back to non-ssl connections.
272 # Changelog v. 1.32.8
276 without the :columns parameter, :update requires alternating column value like so:
279 (query (:update 'weather
280 :set 'temp-lo (:+ 'temp-lo 1)
281 'temp-hi (:+ 'temp-lo 15)
283 :where (:and (:= 'city "San Francisco")
284 (:= 'date "2003-07-03"))
285 :returning 'temp-lo 'temp-hi 'prcp))
288 :update now accepts a :columns parameter. This allows the use of either :set or :select (both of which need to be enclosed in a form) to provide the values, allowing update queries like:
291 (query (:update 'weather
292 :columns 'temp-lo 'temp-hi 'prcp
293 (:set (:+ 'temp-lo 1) (:+ 'temp-lo 15) :DEFAULT)
294 :where (:and (:= 'city "San Francisco")
295 (:= 'date "2003-07-03"))))
298 :columns 'database-name 'encoding
299 (:select 'x.datname 'x.encoding
300 :from (:as 'pg-database 'x)
301 :where (:= 'x.oid 't1.oid))))
305 Insert-into also now accepts a :columns parameter which allows more precise use of select to insert values into specific row(s). A sample query could look like:
308 (query (:insert-into 't11
309 :columns 'region 'subregion 'country
310 (:select (:as 'region-name 'region)
311 (:as 'sub-region-name 'subregion)
318 Joins are now expanded to include lateral joins. So addition join types are
320 - :join-lateral (best practice is still to be specific on what kind of join you want)
322 - :right-join-lateral
323 - :inner-join-lateral
324 - :outer-join-lateral
325 - :cross-join-lateral
328 Selects can now use :with-ordinality or :with-ordinality-as parameters. Postgresql will give the new ordinality column the name of ordinality. :with-ordinality-as allows you to set different names for the columns in the result set.
332 :from (:generate-series 4 1 -1)
336 (query (:select 't1.*
337 :from (:json-object-keys "{\"a1\":\"1\",\"a2\":\"2\",\"a3\":\"3\"}")
338 :with-ordinality-as (:t1 'keys 'n)
341 ## New Utility copy-from-csv
342 Just a convenience function. It runs the psql copy command from inside lisp using uiop:run-program
344 # Changelog v. 1.32.7
346 Additional capabilities for s-sql functions :insert-into and :insert-rows-into
348 Specifically, both can now use:
350 - overriding-system-value
351 - overriding-user-value
352 - on-conflict-do-nothing
354 - on-conflict-on-constraint
362 See updated s-sql docs for examples.
364 # Changelog v. 1.32.4
366 Added the ability to return results as json-encoded results as follows:
369 Return a list of strings where the row returned is a json object expressed as a string
371 (query (:select 'id 'int4 'text :from 'short-data-type-tests :where (:< 'id 3)) :json-strs)
372 ("{\"id\":1,\"int4\":2147483645,\"text\":\"text one\"}"
373 "{\"id\":2,\"int4\":0,\"text\":\"text two\"}")
376 Return a single string where the row returned is a json object expressed as a string
378 (query (:select 'id 'int4 'text :from 'short-data-type-tests :where (:= 'id 3)) :json-str)
379 "{\"id\":3,\"int4\":3,\"text\":\"text three\"}"
382 Return a string containing a json array, each element in the array is a selected row expressed as a json object
384 (query (:select 'id 'int4 'text :from 'short-data-type-tests :where (:< 'id 3)) :json-array-str)
385 "[{\"id\":1,\"int4\":2147483645,\"text\":\"text one\"}, {\"id\":2,\"int4\":0,\"text\":\"text two\"}]"
387 # Changelog v. 1.32.3
389 Added flag to avoid SSL certificate verification if required by user
391 ## Fix S-SQL issue 239 (:drop-table ...) expanded incorrectly
393 (:drop-table ...) can again use variable input
394 Allowable permutations are:
396 (let ((table-var1 "table-1")
397 (table-var2 'table-1))
398 (query (:drop-table :if-exists "table_1"))
400 (query (:drop-table :if-exists table-var1 :cascade))
402 (query (:drop-table :if-exists "table-1" :cascade))
404 (query (:drop-table :if-exists 'table-1 :cascade))
406 (query (:drop-table :if-exists table-var2 :cascade))
408 (query (:drop-table (:if-exists "table-1") :cascade))
410 (query (:drop-table :if-exists table-var1))
412 (query (:drop-table :if-exists "table-1"))
414 (query (:drop-table :if-exists 'table-1))
416 (query (:drop-table :if-exists table-var2))
418 (query (:drop-table (:if-exists "table-1")))
420 (query (:drop-table table-var1 :cascade))
422 (query (:drop-table "table-1" :cascade))
424 (query (:drop-table 'table-1 :cascade))
426 (query (:drop-table table-var2 :cascade))
428 (query (:drop-table "table-1" :cascade))
430 (query (:drop-table table-var1))
432 (query (:drop-table "table-1"))
434 (query (:drop-table 'table-1))
436 (query (:drop-table table-var2))
438 (query (:drop-table 'table-1))
440 (query (:drop-table "table-1")))
442 ## Fix S-SQL issue 236 (:create-table ...) error on multiple attributes
444 The s-sql version of (:create-table ...) will now accept:
446 Basic table name permutations with :temp, :if-not-exists and :unlogged
448 (query (:create-table 'distributors-in-hell)
450 (query (:create-table "distributors-in_hell")
452 (query (:create-table (:temp 'distributors-in_hell))
454 (query (:create-table (:temp "distributors-in_hell"))
456 (query (:create-table (:temp :if-not-exists "distributors-in_hell"))
458 (query (:create-table (:temp :if-not-exists 'distributors-in-hell))
460 (query (:create-table (:temporary 'distributors-in-hell))
462 (query (:create-table (:temporary "distributors-in_hell"))
464 (query (:create-table (:temporary :if-not-exists 'distributors-in_hell))
466 (query (:create-table (:temporary :if-not-exists "distributors-in_hell"))
468 (query (:create-table (:unlogged 'distributors-in_hell))
470 (query (:create-table (:unlogged "distributors-in_hell"))
472 (query (:create-table (:unlogged :if-not-exists 'distributors-in_hell))
474 (query (:create-table (:unlogged :if-not-exists "distributors-in_hell"))
476 (query (:create-table (:temp :unlogged 'distributors-in_hell))
478 (query (:create-table (:temp :unlogged "distributors-in_hell"))
480 (query (:create-table (:temp :unlogged :if-not-exists "distributors-in_hell"))
482 (query (:create-table (:temp :unlogged :if-not-exists 'distributors-in-hell))
485 Expanding table names with composite types
487 (query (:create-table (:of distributors-in-hell 'employee-type))
489 (query (:create-table (:of distributors-in-hell "employee-type"))
491 (query (:create-table (:temp :of distributors-in-hell 'employee-type))
493 (query (:create-table (:temp :of distributors-in-hell "employee-type"))
495 (query (:create-table (:temp :unlogged :of distributors-in-hell 'employee-type))
497 (query (:create-table (:temp :unlogged :of distributors-in-hell "employee_type"))
499 (query (:create-table (:temp :if-not-exists :of distributors-in-hell 'employee-type))
501 (query (:create-table (:temp :if-not-exists :of distributors-in-hell "employee-type"))
503 (query (:create-table (:temp :unlogged :if-not-exists :of distributors-in-hell 'employee-type))
505 (query (:create-table (:temp :unlogged :if-not-exists :of distributors-in-hell "employee_type"))
507 (query (:create-table (:unlogged :of distributors-in-hell 'employee-type))
509 (query (:create-table (:unlogged :of distributors-in-hell "employee_type"))
512 Expanding table names with wrapping the table name in a sublist with :if-not-exists
514 (query (:create-table (:temp (:if-not-exists "distributors-in_hell")))
516 (query (:create-table (:temp (:if-not-exists 'distributors-in-hell)))
518 (query (:create-table (:temporary (:if-not-exists 'distributors-in_hell)))
520 (query (:create-table (:temporary (:if-not-exists "distributors-in_hell")))
522 (query (:create-table (:unlogged (:if-not-exists 'distributors-in_hell)))
524 (query (:create-table (:unlogged (:if-not-exists "distributors-in_hell")))
526 (query (:create-table (:temp :unlogged (:if-not-exists "distributors-in_hell")))
528 (query (:create-table (:temp :unlogged (:if-not-exists 'distributors-in-hell)))
531 Note: (:create-table ...) does not accept variables as the table name
533 ### Some additional tests, and small formatting and documentation changes
540 - Daos now have the ability to specify columns as identity, unique, references,
541 primary key or check.
543 - New create-database and create-role functions make it easier to set readonly
544 or edit-only permissions on databases, schemas or tables.
546 - More documentation on connections (toplevel v. with-connection), dao-utilitization
547 and more examples for s-sql
549 ## New Functionality for DAOs
550 Daos now have the ability to specify columns as identity, unique, references,
551 primary key or check.
557 ((id :col-type integer :col-identity t :accessor id)
558 (name :col-type string :col-unique t :check (:<> 'name "")
559 :initarg :name :reader country-name)
560 (inhabitants :col-type integer :initarg :inhabitants
561 :accessor country-inhabitants)
562 (sovereign :col-type (or db-null string) :initarg :sovereign
563 :accessor country-sovereign)
564 (region-id :col-type integer :col-references ((regions id))
565 :initarg :region-id :accessor region-id))
566 (:metaclass dao-class)
567 (:table-name countries))
569 In this example we have an id column which is specified to be an identity column.
570 Postgresql will automatically generate a sequence of of integers and this will
573 We have a name column which is specified as unique and is not null and the
574 check will ensure that the database refuses to accept an empty string as the name.
576 We have a region-id column which references the id column in the regions table.
577 This is a foreign key constraint and Postgresql will not accept inserting a country
578 into the database unless there is an existing region with an id that matches this
579 number. Postgresql will also not allow deleting a region if there are countries
580 that reference that region's id. If we wanted Postgresql to delete countries when
581 regions are deleted, that column would be specified as:
583 (region-id :col-type integer :col-references ((regions id) :cascade)
584 :initarg :region-id :accessor region-id)
587 Now you can see why the double parens.
589 ## New S-SQL Functionality
591 Fetch is a more efficient way to do pagination instead of using limit and
592 offset. Fetch allows you to retrieve a limited set of rows, optionally offset
593 by a specified number of rows. In order to ensure this works correctly, you
594 should use the order-by clause. If the amount is not provided, it assumes
595 you only want to return 1 row. https://www.postgresql.org/docs/current/sql-select.html
598 (query (:fetch (:order-by (:select 'id :from 'historical-events) 'id) 5))
600 ((1) (2) (3) (4) (5))
602 (query (:fetch (:order-by (:select 'id :from 'historical-events) 'id) 5 10))
604 ((11) (12) (13) (14) (15))
607 ## New Postmodern Functions
609 - function get-database-comment (database-name)
611 Returns the comment, if any, attached to a database.
613 - function change-toplevel-database (new-database user password host)
615 Just changes the database assuming you are using a toplevel connection.
616 Recommended only for development work. Returns the name of the newly connected
617 database as a string.
619 ### database-management
621 - function create-database (database-name &key (encoding "UTF8") (connection-limit -1)
622 owner limit-public-access comment collation template)
624 Creates and initializes a database. Besides the obvious database-name parameter,
625 you can also use key parameters to set encoding (defaults to UTF8), owner,
626 connection-limit (defaults to no limit)). If limit-public-access is set to t,
627 then only superuser roles or roles with explicit access to this database will
628 be able to access it. If collation is set, the assumption is that template0
629 needs to be used as the base of the database rather than template1 which may
630 contain encoding specific or locale specific data.
632 - function list-templates ()
634 Returns a list of existing database template names.
636 - function list-available-collations ()
638 Get a list of the collations available from the current database cluster.
639 Collations are a mess as different operating systems provide different
640 collations. We might get some sanity if Postgresql can use ICU as the default.
641 See https://wiki.postgresql.org/wiki/Collations.
643 - function list-database-access-rights (&optional database-name)
645 If the database parameter is specifed, this returns an list of lists where
646 each sublist is a role name and whether they have access rights (t or nil) to that
647 particular database. If the database-name is not provided, the sublist is
648 a database name, a role name and whether they have access rights (t or nil). This
649 excludes the template databases.
653 - function list-role-permissions (&optional role)
655 This returns a list of sublists of the permissions granted within the
656 currently connected database. If an optional role is provided, the result is
657 limited to that role. The sublist returned will be in the form of role-name,
658 schema-name, table-name and then a string containing all the rights of that role
659 on that table in that schema.
661 - function create-role (name password &key (base-role :readonly) (schema :public)
662 (tables :all) (databases :current)
663 (allow-whitespace nil)
665 (allow-disallowed-names nil) (comment nil))
667 Keyword parameters: Base-role. Base-role should be one of :readonly, :editor,
668 :admin, :standard or :superuser. A readonly user can only select existing data in the
669 specified tables or databases. An editor has the ability to insert, update,
670 delete or select data. An admin has all privileges on a database, but cannot
671 create new databases, roles, or replicate the system. A standard user has no
672 particular privileges other than connecting to databases.
674 :schema defaults to :public but can be a list of schemas. User will not have
675 access to any schemas not in the list.
677 :tables defaults to :all but can be a list of tables. User will not have access
678 to any tables not in the list.
680 :databases defaults to :current but can be a list of databases. User will not
681 have access to any databases not in the list.
683 :allow-whitespace - Whitespace in either the name or password is not allowed by
686 :allow-utf8 defaults to nil. If t, the name and password will be normalized. If
687 nil, the name and password are limited to printable ascii characters. For fun
688 reading on utf8 user names see
689 https://labs.spotify.com/2013/06/18/creative-usernames. Also interesting reading
690 is https://github.com/flurdy/bad_usernames and
691 https://github.com/dsignr/disallowed-usernames/blob/master/disallowed%20usernames.csv,
692 and https://www.b-list.org/weblog/2018/feb/11/usernames/
694 :allow-disallowed-names defaults to nil. If nil, the user name will be checked
695 against \*disallowed-role-names*.
697 As an aside, if allowing utf8 in names, you might want to think about whether
698 you should create second copy of the username in the original casing and normalized
699 as NFC for display purposes as opposed to normalizing to NFKC. It might be viewed
700 as culturally insensitive to change the display of the name.
702 - function alter-role-search-path (role search-path)
704 Changes the priority of where a role looks for tables (which schema first,
705 second, etc. Role should be a string or symbol. Search-path could be a list of schema
706 names either as strings or symbols.
708 - function change-password (role password &optional expiration-date)
710 Alters a role's password. If the optional expiration-date parameter is provided,
711 the password will expire at the stated date. A sample expiration date would be
712 'December 31, 2020'. If the expiration date is 'infinity', it will never expire.
713 The password will be encrypted in the system catalogs. This is
714 automatic with postgresql versions 10 and above.
716 - function grant-role-permissions (role-type name &key (schema :public) (tables :all)
719 Grant-role-permissions assumes that a role has already been created, but
720 permissions need to be granted or revoked on a particular database.
722 A :superuser can create databases, roles, replication, etc. Returns nil.
723 A :standard user has no particular privileges or restrictions. Returns nil.
724 An :admin user can edit existing data, insert new data and create new tables
725 in the specified databases/schemas/tables.
726 An :editor user can update fields or insert new records but cannot create new
727 tables in the specified tables or databases.
728 A :readonly role can only read existing data in the specified schemas,
729 tables or databases. Schema, tables or databases can be :all or a list of
730 schemas, tables or databases to be granted permission.
732 Granting :all provides access to all future items of that type as well.
734 Note that the schema and table rights and revocations granted are limited to
735 the connected database at the time of execution of this function.
737 - function grant-readonly-permissions (schema-name role-name &optional (table-name nil))
739 Grants select privileges to a role for the named schema. If the optional
740 table-name parameter is provided, the privileges are only granted with respect
741 to that table. Note that we are giving some function execute permissions if
742 table-name is nil, but if the table-name is specified, those are not provided.
743 Your mileage may vary on how many privileges you want to provide to a
744 read-only role with access to only a limited number of tables.
746 - function grant-editor-permissions (schema-name role-name &optional (table-name nil))
748 Grants select, insert, update and delete privileges to a role for the named
749 schema. If the optional table-name parameter is provided, the privileges are only
750 granted with respect to that table. Note that we are giving some function execute
751 permissions if table-name is nil, but if the table-name is specified, those are
752 not provided. Your mileage may vary on how many privileges you want to provide
753 to a editor role with access to only a limited number of tables.
755 - function grant-admin-permissions (schema-name role-name &optional (table-name nil))
757 Grants all privileges to a role for the named schema. If the optional table-name
758 parameter is provided, the privileges are only granted with respect to that table.
760 - function revoke-all-on-table (table-name role-name)
762 Takes a table-name which could be a string, symbol or list of strings or symbols
763 of tables names, a role name and revokes all privileges that role-name may have
764 with that/those tables. This is limited to the currently connected database and
765 can only revoke the privileges granted by the caller of the function.
767 - function list-role-accessible-databases (role-name)
769 Returns a list of the databases to which the specified role can connect.
773 - function get-table-comment (table-name &optional schema-name)
775 Retrieves the comment, if any attached to the table
777 ### general utilities
779 - function add-comment (type name comment &optional (second-name ""))
781 Attempts to add a comment to a particular database object. The first parameter
782 is a keyword for the type of database object. The second parameter is the name of
783 the object. The third parameter is the comment itself. Some objects require an
784 additional identifier. The names can be strings or symbols.
786 Example usage would be:
788 (add-comment :database 'my-database-name "Does anyone actually use this database?".)
790 (add-comment :column 'country-locations.name "Is what it looks like - the name of a country".)
792 (add-comment :column "country_locations.name" "Is what it looks like - the name of a country".)
794 Example usage where two identifiers are required would be constraints:
796 (add-comment :constraint 'constraint1 "Some kind of constraint descriptions here".
800 - function postgres-array-string-to-list (str)
802 Takes a postgresql array in the form of a string like
803 "{sabra=CTc/sabra,a=c/sabra,b=c/sabra}"
805 and returns a lisp list like
807 ("sabra=CTc/sabra" \"a=c/sabra" "b=c/sabra")
809 - function postgres-array-string-to-array (str)
811 Same thing but returns an array