pgproc.el

(define-minor-mode pgproc-mode
  "Tools to write preformated PL/PgSQL"
  :init-value nil

  (defun pgproc-create-replace-function ()
    "Insert SQL to create or replace function"
    (interactive)
    (let (name args rettype volatility comment)
      (setq name (read-from-minibuffer "Function name: "))
      (setq args (read-from-minibuffer "Arguments: "))
      (setq rettype (read-from-minibuffer "(setof?) return type: "))
      (setq volatility (completing-read "Volatility: "
                                        '("VOLATILE" "STABLE" "IMMUTABLE")
                                        nil t nil nil "VOLATILE"))
      (setq comment (read-from-minibuffer "Comment: "))

      (insert "CREATE OR REPLACE FUNCTION " name "(" args ")\n"
              "RETURNS " rettype "\n"
              "LANGUAGE plpgsql\n"
              volatility "\n"
              "AS $$\n"
              "DECLARE\n"
              "\n"
              "BEGIN\n"
              "\n"
              "END;\n"
              "$$;\n"
              "COMMENT ON FUNCTION " name "(" args ") IS '" comment "';\n")))

  (defun pgproc-create-function-returning-type ()
    "Insert SQL to create a function returning a composite type"
    (interactive)
    (let (name args setof rettype volatility comment)
      (setq name (read-from-minibuffer "Function name: "))
      (setq args (read-from-minibuffer "Arguments: "))
      (setq setof (y-or-n-p "setof ?"))
      (setq rettype (read-from-minibuffer "return type: "))
      (setq volatility (completing-read "Volatility: "
                                        '("VOLATILE" "STABLE" "IMMUTABLE")
                                        nil t nil nil "VOLATILE"))
      (setq comment (read-from-minibuffer "Comment: "))

      (insert "DROP FUNCTION IF EXISTS " name "(" args ");\n"
              "DROP TYPE IF EXISTS " rettype ";\n"
              "CREATE TYPE " rettype " AS (\n"
              ");\n"
              "\n"

              "CREATE FUNCTION " name "(" args ")\n"
              "RETURNS " (if setof "SETOF " "") rettype "\n"
              "LANGUAGE plpgsql\n"
              volatility "\n"
              "AS $$\n"
              "DECLARE\n"
              "\n"
              "BEGIN\n"
              "\n"
              "END;\n"
              "$$;\n"
              "COMMENT ON FUNCTION " name "(" args ") IS '" comment "';\n")))

)

.emacs

(load-library "~/.emacs.d/pgproc.el")

(defun my-pgproc-hook ()
   (pgproc-mode 1))
(add-hook 'sql-mode-hook 'my-pgproc-hook)