How to send a string to the SQLi (sql-mode/sql-interactive-mode) buffer after processing?

Question

How can I send a selected region in the sql-mode buffer to an external shell command and let the result be displayed in the sql-interactive-mode buffer (REPL)?

For example, normally, if I select a region in the sql-mode buffer (e.g. my_table) and hit C-c C-r, the text in the region is sent to the sql-interactive-mode buffer (REPL area) and evaluated as a SQL statement (which will generate an error since my_table isn't a valid statement).

Can I alter this behavior so that, the text is first sent to a unix shell command as input, and then the output of shell command is collected and displayed in the interactive-mode buffer?

The shell command I have in mind is this:

{ read message; psql -c "\\d+ $message"; }

The closest thing I can find is this emacs.SE example,

(defun wc-region (beg end)
  "Count words in the active region."
  (interactive "*r")
  (if (region-active-p)
      (shell-command-on-region beg end "wc -w")
    (message "No active region")))

which sends the select text to the Unix shell that does something (a word count) on it, and shows the result in a buffer called "Shell Command Output".

But I'd like to have it work in a sql-mode buffer, and have the processed result displayed in the sql-interactive-mode buffer (REPL area) instead of a new buffer. Just as if I had sent a SQL statement to the REPL.

I adapted the example above to:

(defun wc-region (beg end)
  "Count words in the active region."
  (interactive "*r")
  (if (region-active-p)
      (shell-command-on-region beg end " { read message; psql -c \"\\d+ $message\"; }  ")
    (message "No active region")))

which almost works except it's not sending output of the unix command to sql-interactive-mode buffer. Instead, it creates a new buffer for output.

Ultimately, I want to select a table name in SQL-mode, and display its definition in the sql-interactive-mode buffer by hitting a shortcut key. But I don't know elisp well enough.

-- Update --

@phils

Thanks a lot for your answer. I have two more very basic questions:

1. can the shell command be fixed so that it doesn't have to be keyed in? The following command seems to work (with the second version):

    { read s; echo "\\d+ $s"; }
   

Is there a way to fix command to the above string in a wrapper function or using setq somehow?

2. After executing the command, the focus is in the sql-mode, how can I automatically switch the focus to the sql-interactive-mode buffer after the command is issued?

Answer 1

If you look at any of the definitions of the sql-send-* commands, you'll see they all hand off to sql-send-string. E.g.:

(defun sql-send-region (start end)
  "Send a region to the SQL process."
  (interactive "r")
  (sql-send-string (buffer-substring-no-properties start end)))

shell-command-on-region is a good place to look for how to obtain a string to pass to sql-send-string. That uses call-process-region behind the scenes, so a simple (will not handle errors) combination of the two could look like this:

(defun my-sql-send-region-via-shell-command (command start end)
  "Send a region to the SQL process via shell COMMAND."
  (interactive
   (list (read-shell-command "Shell command on region: ")
         (region-beginning)
         (region-end)))
  (let* ((buf (current-buffer))
         (sql (with-temp-buffer
                (let ((tmpbuf (current-buffer)))
                  (with-current-buffer buf
                    (call-process-region
                     start end shell-file-name nil tmpbuf
                     nil shell-command-switch command))
                  (buffer-string)))))
    (sql-send-string sql)))

or perhaps this:

(defun my-sql-send-region-via-shell-command (command start end)
  "Send a region to the SQL process via shell COMMAND."
  (interactive
   (list (read-shell-command "Shell command on region: ")
         (region-beginning)
         (region-end)))
  (let ((temp-buffer-show-function
         (lambda (buf)
           (sql-send-string (with-current-buffer buf (buffer-string)))
           (kill-buffer buf))))
    (with-output-to-temp-buffer (generate-new-buffer "sql")
      (call-process-region
       start end shell-file-name nil standard-output
       nil shell-command-switch command))))

Entering cat as the shell command ought to give the same outcome as sql-send-region.

---

Is there a way to fix command in a wrapper function?

Sure, you just need to read the start and end and then pass them along with a hard-coded command.

(defun my-sql-send-region-via-FOO (start end)
  "Send a region to the SQL process via shell command FOO."
  (interactive "r")
  (my-sql-send-region-via-shell-command "FOO" start end))

You could also make start and end optional, and have the main function figure them out when they're not passed. I'll leave that as an exercise for the reader...

how can I automatically switch the focus to the sql-interactive-mode buffer after the command is issued?

It looks like sql-send-string supports that directly; try adding (sql-pop-to-buffer-after-send-region t) to the outermost let bindings.