Why do sql-mode and sql-interactive-mode not highlight strings the same way?

Question

Here is a screenshot showing the difference between sql-interactive-mode (top) and sql-mode (bottom).

Why does the string not get highlighted properly in sql-interactive-mode? (Note - this also happens with no color theme enabled.

Answer 1

There's code in sql-interactive-mode which says:

;; Note that making KEYWORDS-ONLY nil will cause havoc if you try
;; SELECT 'x' FROM DUAL with SQL*Plus, because the title of the column
;; will have just one quote.  Therefore syntactic highlighting is
;; disabled for interactive buffers.  No imenu support.
(sql-product-font-lock t nil)

and the t says "only highlight keywords".

So if you don't care about SQL*Plus - as should be the case for any reasonable person in control of his destiny and happiness - you can do the following:

(defun my-font-lock-everything-in-sql-interactive-mode ()
  (unless (eq 'oracle sql-product)
    (sql-product-font-lock nil nil)))
(add-hook 'sql-interactive-mode-hook 'my-font-lock-everything-in-sql-interactive-mode)

And indeed that seems to work nicely with PostgreSQL, at least in the simple case.

Where you might run into problems is if you SELECT data which contains, say, unmatched quote characters. The font locking will likely be applied to all of that data too, and the quotes might confuse it. So be prepared for quirks!