Forum OpenACS Q&A: psql and emacs

1: psql and emacs

Posted by good bye on 07/17/01 06:31 AM

i run SQL*Plus inside of an emacs window, because I like to cut
and paste between a scratch buffer and SQL*Plus session. I'd
like to be able to do the same thing with psql, but there is one
thing I find very annoying: when the results of a select extend
beyond one page, psql wants me to page down through the
output (instead of just dumping it all to the screen). The paging
down screws up my emacs shell.

Is there a way to turn this off? I.e. some flag in psql that tells it to
just keep dumping output to the screen, and not break it up into
pages?

2: Response to psql and emacs (response to 1)

Posted by Dan Wickstrom on 07/17/01 06:43 AM

You can toggle paging mode using the following:


openacs4=# pset pager
Using pager is off.                                                                                                    
openacs4=#

3: Response to psql and emacs (response to 1)

Posted by David Walker on 07/17/01 06:44 AM

Here's a hack that works. Apparently psql checks to see where STDIN is from. So...

echo | psql -c "sql string"

4: Response to psql and emacs (response to 1)

Posted by Vinod Kurup on 07/17/01 06:47 AM

Instead of using M-x shell and then running psql, I use M-x
sql-postgres which runs commands without a pager and does syntax coloring.

5: Response to psql and emacs (response to 1)

Posted by Roberto Mello on 07/17/01 07:54 AM

WOW! I was unaware or M-x sql-postgres. That's extremely nice Vinod. Thanks! Is there something similar for Oracle?

Also, do you know how to make it so readline can be used within this psql process running in Emacs (so that up arrow gives me the last command)?

6: Response to psql and emacs (response to 1)

Posted by Vinod Kurup on 07/17/01 08:18 AM

Thanks! Is there something similar for Oracle?

Yup! M-x sql-oracle

Also, do you know how to make it so readline can be used within this psql process running in Emacs (so that up arrow gives me the last command)?

Hmmm - that I don't know. I do most of my development on a Powerbook running Debian PowerPC and I can use CTRL-SHIFT-Up-arrow to scroll through the command buffer.

7: Response to psql and emacs (response to 1)

Posted by Dan Wickstrom on 07/17/01 01:50 PM

I've used something like the following to map the up/down arros keys in xemacs:


(add-hook 'shell-mode-hook 
	  (function 
	   (lambda ()
	     (define-key comint-mode-map 'up 'comint-previous-input)
	     (define-key comint-mode-map 'down 'comint-next-input))))

I haven't tested this with sql-postgresql and sql-oracle, but I'm assuming that they're both built on top of comint. You will probably just need to add this to your .emacs file and change the hook name.

8: Response to psql and emacs (response to 1)

Posted by Roberto Mello on 07/17/01 05:27 PM

I get C-up to give the last command, but I couldn't get your hook to work Dan :(

9: Response to psql and emacs (response to 1)

Posted by Dan Wickstrom on 07/17/01 05:50 PM

OK, I looked in sql.el. The correct hook name is sql-interactive-mode-hook. I've tested the following for the up/down arrow keys:
<p>
<pre>
(add-hook 'sql-interactive-mode-hook
      (function
      (lambda ()
      (define-key comint-mode-map 'up 'comint-previous-input)
      (define-key comint-mode-map 'down 'comint-next-input))))

</pre>
<p>

10: Response to psql and emacs (response to 1)

Posted by Louis Zirkel on 07/17/01 06:49 PM

I tried the M-x sql-postgres and the M-x sql-oracle and I got the first to work, but not the second. Any ideas? Why would one work and not the other?

11: Response to psql and emacs (response to 1)

Posted by Henry Minsky on 07/18/01 09:02 AM

In a shell buffer, Meta-p and Meta-n will sequence through all the
commands
in your command history.

12: Re: psql and emacs (response to 1)

Posted by Torben Brosten on 11/06/03 01:28 PM

I am unable to find a way to use gnu-emacs sql-postgres to connect directly to db.

The connection must specify port, host, dbname and username, but apparently sql-postgres only asks for host and dbname. Is there pathname short-hand I can use to get around this?

Besides M-x sql-help, I was not able to find docs for using sql-postgres (I tried gnu.org/emacs and google). Postgres.org mentions a non-enduser, socket-level pg.el.[1] where port and username defaults can be changed.

In the interim, I am using psql via M-x shell, but am experiencing the same symptoms as original post above: At the end of each screen, a prompt appears which partially destroys the data output of a view row. I tried the techniques mentioned above, but they don't seem to work. I get syntax errors. Maybe the command has changed in the newer versions, or the instructions are not explicit enough for me to understand?

1. http://www.chez.com/emarsden/downloads/pg.el

13: Re: psql and emacs (response to 1)

Posted by Torben Brosten on 11/06/03 01:36 PM

'pset pager' doesn't work in shell mode, nor at the term prompt.

'apropos pset' results in a bunch of references to ieee floating point commands (not relevant).

This is on FreeBSD 4.9-prerelease kernel

14: Re: psql and emacs (response to 1)

Posted by Tom Ayles on 11/06/03 02:14 PM

sql-postgres always tries to connect with the same username as the user running emacs. I worked around this by creating a superuser in the database I was working in with my username. Probably not best practice though - I'd love to know how to send a username from Emacs.

15: Re: psql and emacs (response to 1)

Posted by Randy Beggs on 11/06/03 03:08 PM

I made a note to myself about this, and it works fine for me:

Modify 'sql-postgres-options' and insert two seperate strings at the top of the list: "-U" & "db_username"

16: Re: psql and emacs (response to 1)

Posted by Torben Brosten on 11/06/03 11:24 PM

Thanks Randy, I'll keep that option in my bag of tricks.

Patrick Giagnocavo suggested this solution (it works):

from (bash/ksh shell)

export PGUSER=username
export PGHOST=host
export PGPORT=port

Then start emacs in this same shell.

much thanks,

Torben

17: Re: psql and emacs (response to 16)

Posted by Malte Sussdorff on 11/07/03 08:27 AM

Can we add this to the documentation, maybe in the form of generating a /etc/profile.d/postgres file, that has to be sourced by every user wanting to access postgres (at least nsadmin and most likely all your development users as well).

Besides setting the variables above, it should as well set the PATH (so we do *not* have to use nsd-postgres anymore).

18: Re: psql and emacs (response to 12)

Posted by Andrei Popov on 11/07/03 11:30 AM

From my .emacs:

(setq sql-user "apopov")
(setq sql-database "apopov")
(setq sql-server "vm")
(setq sql-postgres-options '("-Uapopov" "-P" "pager=off"))

But it won't help w/port.

19: Re: psql and emacs (response to 18)

Posted by Bart Teeuwisse on 11/07/03 04:57 PM

Another option is to apply the following patch to emacs/21.2/lisp/progmodes/sql.el so that Emacs will always ask for a username regardless of environment variables. It has served me well.

1674c1674
< (sql-get-login 'database 'server)
---
> (sql-get-login 'user 'database 'server)
1683a1684,1685
> (if (not (string= "" sql-user))
> (setq params (append (list "-U" sql-user) params)))

Don't forget to byte compile sql.el afterwards or Emacs will continue to use the old sql.elc without the patch.

/Bart

20: Re: psql and emacs (response to 19)

Posted by Mark Aufflick on 03/17/04 08:12 AM

To save people wasting the time i just wasted, if you're using emacs 21.3 you can do just about the same thing as Bart describes with the following points:

* I applied the patch by hand, but I think the lines in the 21.2 patch above is off by 1 - but get to line 1683 and it's around there somewhere
* it only worked for me if the sql-get-login used this specific order:

(sql-get-login 'database 'user 'server)

If I put the 'user anywhere else it ignored it :/

Also, sql-get-login will automatically ask for password when it asks for user - if you are not using passwords just leave it blank and hit enter.

PS: Bart you'll be pleased to know that I got to this thread from the first page of a google search - it's cool because I'm working at a non-oacs client, and I didn't even notice that I was on openacs.org until I saw /Bart :)

I'll be signing off /Mark soon, thus completing all our conversions into Lars-borgs :P