Provide support for PostgreSQL arrays

[gnusupport]

Table:

admin=# \d sal_emp
                   Table "public.sal_emp"
     Column     |   Type    | Collation | Nullable | Default 
----------------+-----------+-----------+----------+---------
 name           | text      |           |          | 
 pay_by_quarter | integer[] |           |          | 
 schedule       | text[]    |           |          | 

Result:

(rcd-sql "SELECT schedule from sal_emp;" *admin*)
("{{meeting,lunch},{training,presentation}}" "{{meeting,lunch},{training,presentation},{Assigned,Jean}}")

In my opinion that is poor for Emacs Lisp to receive just textual representation of PostgreSQL arrays. I was expecting to receive some better structure.

Is it feasible to support PostgreSQL arrays so that Emacs Lisp receives better structure out of it?

[gnusupport]

Then I must use something dirty like this:

(defun sql-array-to-list (array)
  "This is temporary function as emacs-libpq does not have
support for PostgreSQL arrays as of 2019-09-11. This function is
replacing all curly brackets with parenthesis, without regard if
parenthesis is contained within the field or values within
arrays. Comas are replaced with spaces. Thus user must be
careful."
  (let* ((array (replace-regexp-in-string "},{" "\"@@@\"" array))
     (array (replace-regexp-in-string "{{" "((\"" array))
     (array (replace-regexp-in-string "}}" "\"))" array))
     (array (replace-regexp-in-string "," "\" \"" array))
     (array (replace-regexp-in-string "}" ")" array))
     (array (replace-regexp-in-string "}" ")" array))
     (array (replace-regexp-in-string "@@@" ") (" array))w
     (array (concat "'" array)))
    (condition-case nil
    (eval (car (read-from-string array)))
      (error ""))))

If you implement it in the module, then maybe proper arrays can be received instead.

[anse1]

Supporting arrays is not a very low-hanging fruit: Currently, the pq module is ignorant about any non-basic type and just yields a text representation of them. Since they can be nested and contain other composite types, doing it properly requires investigating the involved types further in the system catalog. I guess this can double the amount of code currently in the pq module.

I didn't suffer from the lack of support for composite types in the pq module yet, since I avoid composite types in the public parts of my schema designs. After all, they violate 1NF and thus cause other trouble as well.

Maybe you can work around your problem with postgres' UNNEST operator? For example, you could let postgres unpack the arrays in a more robust way than your function sql-array-to-list:

(mapcar
 (lambda (pgarr)
   (pq:query *pq* "select * from UNNEST ($1::text[]);" pgarr))
 (pq:query *pq* "select c from a"))
(("d" "e" "f") ("a" "b" "c"))

regards, Andreas