1

I want an emacs SQL indentation function with the following properties:

  1. Upcase { select, from, as, where, group by, order by, asc, desc, and, or }. -

EDIT: there are sqlup-mode.el and sql-upcase.el which both address this part of the problem.

  1. Maintain an invariant that { select, from, where, group by, order by } are followed by a newline.
  2. All select, where, group by and order by clauses are on their own line.
  3. 2 space indentation for non-top-level stuff
  4. AND and OR elements of WHERE clause is indented.

Example:

before:

select foo, bar, count(1) as foo_bar_count
from mytable
where foo = 1 and bar = 2
group by foo, bar
order by foo desc

after:

SELECT 
  foo, 
  bar, 
  count(1) AS foo_bar_count
FROM 
  mytable
WHERE 
  foo = 1 
  AND bar = 2
GROUP BY 
  foo, 
  bar
ORDER BY 
  foo DESC

I haven't found a reference to anything like this.

Questions:

  1. For point 1, the following seems to be sufficient:

    (defun kb/sql-upcase-keywords()
      (interactive)
      (goto-char (point-min))
      (while (re-search-forward "^[ \t]*\\(select\\|from\\)" nil t)
        (replace-match (upcase (match-string 1)))))
    
  2. For point 3 - is there an example of elisp which inserts newlines efficiently? I have hacked the following

    (defun kb/sql-add-newlines()
      (interactive)
      ;; (save-excursion)
      (goto-char (point-min))
      (while (re-search-forward
             "^[    ]*\\\\(select\\\\|from\\\\|where\\\\|group by\\\\|order by\\\\)[    ]*\\\\([^   ]*\\\\)"
              nil t)
        (message "Match found at %s" (point))
        (replace-match "\\\\1\n\\\\2")
        (previous-line)))
    

    Is there a better way of doing this? (The previous-line call seems to be needed in order to match select foo\nselect foo)

  3. Is there a simpler way forward that I am missing?

migrated from stackoverflow.com Dec 16 '17 at 16:05

This question came from our site for professional and enthusiast programmers.

1

It doesn't satisfy all your requirements (by far), but there's an automated SQL indentation package for Emacs at https://github.com/alex-hhh/emacs-sql-indent

You can install it now from GNU ELPA.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.