elisp for managing functions

Toad it's not. But this might be useful to some other people
here. Please bear in mind that I've only just written it so it'll be
buggy and incomplete.

It uses Eric Marsden's great postgresql elisp library.


Apologies if this has been done before.



Nic Ferrier




;; Some postgresql management tools.
;; (c) Tapsell-Ferrier Limited 2002
;;
;; This program is free software; you can redistribute it and/or
;; modify it under the terms of the GNU General Public License as
;; published by the Free Software Foundation; either version 2 of
;; the License, or (at your option) any later version.
;;
;; This program is distributed in the hope that it will be useful,
;; but WITHOUT ANY WARRANTY; without even the implied warranty of
;; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
;; GNU General Public License for more details.
;;
;; You should have received a copy of the GNU General Public
;; License along with this program; if not, write to the Free
;; Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
;; MA 02111-1307, USA.

(require 'pg)

;; The query used for finding info about functions.
(defmacro pgmgr-func-query (func-name)
  `(concat "SELECT pg_catalog.format_type(p.prorettype, NULL) as \"Result data type\",
  n.nspname as \"Schema\",
  p.proname as \"Name\",
  pg_catalog.oidvectortypes(p.proargtypes) as \"Argument data types\",
  u.usename as \"Owner\",
  l.lanname as \"Language\",
  p.prosrc as \"Source code\",
  pg_catalog.obj_description(p.oid, 'pg_proc') as \"Description\"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
     LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = p.proowner
WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
      AND p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype
      AND NOT p.proisagg
      AND pg_catalog.pg_function_is_visible(p.oid)
      AND p.proname ~ '^" ,func-name "$'
ORDER BY 2, 3, 1, 4;"))

;; This hash stores info about functions.
(defvar pgmgr-proc-hash (make-hash-table :test 'equal))

;; The connection to the pgsql backend.
(defvar pgmgr-backend-conn nil)

;; Get the connection to the server
(defmacro pgmgr-connection ()
  '(let ()
     (if (not pgmgr-backend-conn)
     (setq pgmgr-backend-conn
           (pg:connect
        (read-from-minibuffer "database: ")
        (read-from-minibuffer "username: ")
        (read-from-minibuffer "password: ")
        (read-from-minibuffer "host: "))))
     pgmgr-backend-conn))


;; Get a representation of the proc
(defun pgmgr-proc (pname)
  "get a proc.
If it doesn't exist add it to the cache."
  (let ((proc (gethash pname pgmgr-proc-hash)))
    (if (not proc)
    (let* ((resultset (pg:exec (pgmgr-connection) (pgmgr-func-query pname))))
      (while resultset
        (let* ((proc-desc (car (pg:result resultset :tuples)))
           (proc-name (nth 2 proc-desc)))
          (setq resultset (cdr-safe resultset))
          (puthash proc-name proc-desc pgmgr-proc-hash)))
      (setq proc (gethash pname pgmgr-proc-hash))))
    proc))

;; Get the name of the proc.
(defmacro pgmgr-proc-get-name (proc)
  `(let ((p ,proc))
     (if (listp p)
     (nth 2 p))))

;; Get the return value associated with the proc.
(defmacro pgmgr-proc-get-retval (proc)
  `(let ((p ,proc))
     (if (listp p)
     (nth 0 p))))

;; Get the arg list associated with the proc.
(defmacro pgmgr-proc-get-args (proc)
  `(let ((p ,proc))
     (if (listp p)
     (nth 3 p))))

;; Get the source of the proc.
(defmacro pgmgr-proc-get-source (proc)
  `(let ((p ,proc))
     (if (listp p)
     (nth 6 p))))

;; Return the part of the list representing the source.
(defmacro pgmgr-proc-set-source (proc new-source)
  `(let* ((p (nthcdr 6 ,proc)))
     (setcar p ,new-source)
     p))


;; Make a string that looks like the decleration line.
(defmacro pgmgr-proc-decl (proc)
  `(let ((p ,proc))
     (concat (pgmgr-proc-get-name p)
         " ( " (pgmgr-proc-get-args p) " ) "
         " returns " (pgmgr-proc-get-retval p))))


;; Edit the specified proc in a modified SQL buffer.
(defun pgmgr-edit-proc (proc-name)
  (interactive "MEnter proc name:")
  (setq proc (pgmgr-proc proc-name))
  (if proc
      (let (proc-buf-kmap
        (proc-buf    (get-buffer-create (pgmgr-proc-get-name proc))))
    (switch-to-buffer proc-buf)
    (sql-mode)
    (make-local-variable 'proc)
    (setq proc-source-start 0)
    (make-local-variable 'proc-source-start)
    (setq proc-buf-kmap (copy-keymap (current-local-map)))
    (define-key proc-buf-kmap "\C-xw"
      (lambda ()
        (interactive)
        (let* ((proc-source (buffer-substring-no-properties proc-source-start (point-max)))
           (create-replace (concat "create or replace function "
                       (pgmgr-proc-decl proc)
                       " as '" proc-source "' language 'plpgsql';")))
          (pg:exec (pgmgr-connection) create-replace)
          (pgmgr-proc-set-source proc proc-source)
          (message (concat (pgmgr-proc-get-name proc) " written")))))
    (use-local-map proc-buf-kmap)
    (insert (concat "-- " (pgmgr-proc-decl proc) "\n"))
    (setq proc-source-start (point))
    (put-text-property 1 (- proc-source-start 1) 'front-sticky 't)
    (put-text-property 1 (- proc-source-start 1) 'read-only 't)
    (insert (pgmgr-proc-get-source proc)))))


(provide 'pgmgr)