sql-send-paragraph results in mis-aligned headers

Question

Anyone know how to fix this? I'm connecting to Postgres, and when I use a command like sql-send-paragraph to send a SQL query to the interactive *SQL* buffer, the headers are not aligned right. It's really hard to read.

For example a multiline query like this:

select a, b, c
from table1
where a = 1;

Will give something like this in the *SQL* buffer:

mydb=> mydb=> mydb-> mydb-> mydb->     a     |      b       |   c
----------+--------------+-------
        1 | 2015-01-05   | 59120
        7 | 2015-01-06   | 59121
        3 | 2015-01-07   | 59122
(3 rows)

It needs to put an extra newline in there so the column headers are on their own line.

Answer 1

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
(defvar sql-product)
(defvar sql-prompt-regexp)
(defvar sql-prompt-cont-regexp)

(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'.
  (add-hook 'comint-preoutput-filter-functions
            '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
`comint-preoutput-filter-functions'."
  ;; 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)
      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'
          (prefix-newline nil))
      (with-temp-buffer
        (insert output)
        (goto-char (point-min))
        (when (looking-at main-prompt)
          (setq prefix-newline t))
        (while (looking-at any-prompt)
          (replace-match ""))
        ;; Prepend a newline to the output, if necessary.
        (when prefix-newline
          (goto-char (point-min))
          (unless (looking-at "\n")
            (insert "\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))))
(ad-activate 'sql-send-string)

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.

Answer 2

You can use this snippet to add new line to beginning of output from emacswiki.

(defvar sql-last-prompt-pos 1
  "position of last prompt when added recording started")
(make-variable-buffer-local 'sql-last-prompt-pos)
(put 'sql-last-prompt-pos 'permanent-local t)

(defun sql-add-newline-first (output)
  "Add newline to beginning of OUTPUT for `comint-preoutput-filter-functions'
        This fixes up the display of queries sent to the inferior buffer
        programatically."
  (let ((begin-of-prompt
         (or (and comint-last-prompt-overlay
              ;; sometimes this overlay is not on prompt
              (save-excursion
                (goto-char (overlay-start comint-last-prompt-overlay))
                (looking-at-p comint-prompt-regexp)
                (point)))
             1)))
    (if (> begin-of-prompt sql-last-prompt-pos)
        (progn
          (setq sql-last-prompt-pos begin-of-prompt)
          (concat "\n" output))
      output)))

(defun sqli-add-hooks ()
  "Add hooks to `sql-interactive-mode-hook'."
  (add-hook 'comint-preoutput-filter-functions
            'sql-add-newline-first))

(add-hook 'sql-interactive-mode-hook 'sqli-add-hooks)

Here is another snippet that solves your problem

(defun sql-add-newline-first (output)
   "Add newline to beginning of OUTPUT for `comint-preoutput-filter-functions'"
  (concat "\n" output))

(defun sqli-add-hooks ()
  "Add hooks to `sql-interactive-mode-hook'."
  (add-hook 'comint-preoutput-filter-functions
            'sql-add-newline-first))

(add-hook 'sql-interactive-mode-hook 'sqli-add-hooks)

There are many other alternatives for this implementation, you can pick any that suits your needs.

Answer 3

Maybe try one of these things?

• Customize sql-send-terminator with a PAT that matches the terminator that gets sent and a TERM that specifies the terminator to really use, which would include a newline char.

• Customize sql-product-alist to add a :terminator for your platform that includes a newline char after the ;.

• Use (sql-set-product-feature sql-product :terminator MY-TERMINATOR), where MY-TERMINATOR is the terminator you really want to use, which presumably would end in a newline char.

Dunno - these are just guesses that occur to me by looking over the sql.el code superficially. The point is that there see to already be means provided for specifying the actual terminator to send to the SQL shell/interaction.