Nepheles <email@example.com> wrote:
| How does one represent the result of an SQL query as a Lisp object,
| while maintaining as much information as possible? The obvious method
| seems to be to have a list of rows, each containing a list of results,
| but that makes it awkward to refer to column names.
Call me simple-minded, perhaps, but I tend to just cons a row of column
names onto the front of the list of result row-lists, and then pass the
resulting single list around, e.g., using Eric Marsden's "pg.lisp":
> (defun simple-query (query)
"SIMPLE-QUERY -- Does PostgreSQL query using a shared,
Success ==> (values results nil) ; A list of rows+1 lists of strings.
Fail ==> (values nil error) ; A SQL-ERROR condition object."
(with-shared-pg-connection (conn *http-request*)
(let* ((result (pg-exec conn query)) ;Single query, no xaction.
;; Pull out just the column names from the attributes.
(cols (mapcar #'car (pg-result result :attributes))))
(values (cons cols (pg-result result :tuples)) nil)))
(values nil cc))))
> (simple-query "select * from toy limit 4")
(("season" "media" "title" "upd")
("fall" "tape" "My Favorite Thanksgiving" 16)
("xmas" "book" "My Favorite Christmas" 2)
("xmas" "video" "The Grinch who Stole Christmas" 4)
("summer" "book" "Unusual 4ths of July" 17))
If you just want to output an HTML table, it's already in a reasonably
convenient form. Or if you need to use the column labels more than once,
just peel them off and re-use them:
> (let* ((results (simple-query "select * from toy limit 4"))
(columns (car results))
(rows (cdr results)))
(loop for row in rows do
(loop for col in columns ; repeats each time
and item in row do
(format t "~&~a:~10t~a~%" col item))
title: My Favorite Thanksgiving
title: My Favorite Christmas
title: The Grinch who Stole Christmas
title: Unusual 4ths of July
Rob Warnock <firstname.lastname@example.org>
627 26th Avenue <URL:http://rpw3.org/>
San Mateo, CA 94403 (650)572-2607