This seems to be working nicely both for input typed at the sqli prompt, as well as for input sent from another query buffer.
I've only tested it with postgres, so feedback regarding other products is welcomed.
It doesn't handle the case where multiple queries are sent together and you end up with prompts in the middle of the output (i.e. at the start of the output for each of the individual queries). That's less of a concern, though (and I'm not convinced that attempting to handle it would be safe in any case).
It took me a while to notice that
sql-interactive-remove-continuation-prompt exists and is called as a filter by default. A solution for this issue should probably happen in there, so a bug report may be in order. For now I have my custom filter appended so that it runs after the default filter.
I've included my tweaking of the postgres prompts in the code below, as (a) that's an issue for my use-case, and (b) neither of the output filters will work if the prompt patterns don't match, so it's important to be aware of these regexps.
;; Silence compiler warnings
(add-hook 'sql-interactive-mode-hook 'my-sql-interactive-mode-hook)
(defun my-sql-interactive-mode-hook ()
"Custom interactive SQL mode behaviours. See `sql-interactive-mode-hook'."
(when (eq sql-product 'postgres)
;; Allow symbol chars in database names in prompt.
;; Default postgres pattern was: "^\\w*=[#>] " (see `sql-product-alist').
(setq sql-prompt-regexp "^\\(?:\\sw\\|\\s_\\)*=[#>] ")
;; Ditto for continuation prompt: "^\\w*[-(][#>] "
(setq sql-prompt-cont-regexp "^\\(?:\\sw\\|\\s_\\)*[-(][#>] "))
;; Deal with inline prompts in query output.
;; Runs after `sql-interactive-remove-continuation-prompt'.
'my-sql-comint-preoutput-filter :append :local))
(defun my-sql-comint-preoutput-filter (output)
"Filter prompts out of SQL query output.
Runs after `sql-interactive-remove-continuation-prompt' in
;; If the entire output is simply the main prompt, return that.
;; (i.e. When simply typing RET at the sqli prompt.)
(if (string-match (concat "\\`\\(" sql-prompt-regexp "\\)\\'") output)
;; Otherwise filter all leading prompts from the output.
;; Store the buffer-local prompt patterns before changing buffers.
(let ((main-prompt sql-prompt-regexp)
(any-prompt comint-prompt-regexp) ;; see `sql-interactive-mode'
(when (looking-at main-prompt)
(setq prefix-newline t))
(while (looking-at any-prompt)
;; Prepend a newline to the output, if necessary.
(unless (looking-at "\n")
;; Return the filtered output.
(buffer-substring-no-properties (point-min) (point-max))))))
(defadvice sql-send-string (before my-prefix-newline-to-sql-string)
"Force all `sql-send-*' commands to include an initial newline.
This is a trivial solution to single-line queries tripping up my
custom output filter. (See `my-sql-comint-preoutput-filter'.)"
(ad-set-arg 0 (concat "\n" (ad-get-arg 0))))
The advice at the end works around the problem of differentiating direct input into the sqli buffer (in which case an initial newline is inserted directly by the user), and sending a single-line query from a sql-mode query buffer (which was the remaining case when we didn't get the output we wanted). The apparent output is identical in these cases, but forcing an initial newline for the
sql-send-* commands works around that problem.