Homoiconicity and SQL

Question

I'm currently using emacs sql-mode as my sql shell, a (simplified) query response is below:

my_db=# select * from visit limit 4;

num |        visit_key            |          created           |    expiry
----+-----------------------------+----------------------------+------------
 1  | 0f6fb8603f4dfe026d88998d81a | 2008-03-02 15:17:56.899817 | 2008-03-02
 2  | 7c389163ff611155f97af692426 | 2008-02-14 12:46:11.02434  | 2008-02-14
 3  | 3ecba0cfb4e4e0fdd6a8be87b35 | 2008-02-14 16:33:34.797517 | 2008-02-14
 4  | 89285112ef2d753bd6f5e51056f | 2008-02-21 14:37:47.368657 | 2008-02-21
(4 rows)

If I want to then formulate another query based on that data, e.g.

my_db=# select visit_key, created from visit where expiry = '2008-03-02' 
           and num > 10;

You'll see that I have to add the comma between visit_key and created, and surround the expiry value with quotes.

Is there a SQL DB shell that shows it's content more homoiconically, so that I could minimise this sort of editing? e.g.

num, visit_key, created, expiry           
(1, '0f6fb8603f4dfe026d88998d81a', '2008-03-02 15:17:56.899817', '2008-03-02')

or

(num=1, visit_key='0f6fb8603f4dfe026d88998d81a', 
    created='2008-03-02 15:17:56.899817', expiry='2008-03-02')

I'm using postgresql btw.

score 2 · Answer 1 · answered Nov 26 '10 at 20:05

Here's one idea, which is similar to what I do sometimes, though I'm not sure that it's exactly what you're asking for:

Run a Lisp compiler (like SBCL) in SLIME. Then load CLSQL. It has a "Functional Data Manipulation Language" (SELECT documentation) which might help you do something like you want, perhaps in conjunction with SLIME's autocompletion capabilities. If not, it's easy to define Lisp functions and macros (assuming you know Lisp, but you're already an Emacser!).

Out-of-the-box, it doesn't give the nicely formatted tables that most SQL interfaces have, but even that isn't too hard to add. And Lisp is certainly powerful enough to let one easily come up with ways to make your common operations easier.

score 0 · Answer 2 · answered Oct 03 '13 at 10:33

I've found the following changes in psql go some way to giving me homoiconicity:

=# select remote_ip, referer, http_method, time from hit limit 1;
    remote_ip    | referer | http_method |           time            
-----------------+---------+-------------+---------------------------
 213.233.132.148 |         | GET         | 2013-08-27 08:01:42.38808
(1 row)
=# \a
Output format is unaligned.
=# \f ''', '''
Field separator is "', '".
=# \t
Showing only tuples.
=# select remote_ip, referer, http_method, time from hit limit 1;
213.233.132.148', '', 'GET', '2013-08-27 08:01:42.38808

caveats: everything is a string, and it's missing start and end quotes.

Homoiconicity and SQL

2 Answers2