search

marijnh / Postmodern

A Common Lisp PostgreSQL programming interface
http://marijnhaverbeke.nl/postmodern
Other
397 stars 89 forks source link

Expand ability to use lists in parameterized queries and s-sql #314

Closed sabracrolleton closed 1 year ago

sabracrolleton commented 1 year ago

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.

    (query "select name from employee where id = any($1)"
         #(1 3 4))
    (query "select name from employee where id = any($1)"
         '(1 3 4))

    (let ((emp-ids #(1 2)))
       (query "select name from employee where id = any($1)"
               emp-ids))

    (let ((emp-ids '(1 2)))
       (query "select name from employee where id = any($1)"
               emp-ids))

     (query (:select 'name :from 'employee :where (:= 'id (:any* '$1)))
             #(1 3) :column)
      '("Jason" "Celia")

     (query (:select 'name :from 'employee :where (:= 'id (:any* '$1)))
             '(1 3) :column)
      '("Jason" "Celia")

Plain proper lists can also now be used in s-sql queries using :in. Previously you needed to use :set

    (query (:select 'name :from 'employee :where (:in 'id (:set 1 3 4))))
    '(("Jason") ("Celia") ("Linda"))

Now you can also provide a list.

    (let ((emp-ids '(1 2)))
        (query (:select 'name :from 'employee :where (:in 'id emp-ids))))
    (("Jason") ("Robert"))