Thread: External psql editor

I do all my postgres work using the psql shell. Editing a command reguires
moving character-by-character and I'd like to use my small text editor (joe)
because it allows more control over line movement.

A web search found a stackexchange thread that suggested adding to
~/.bash_profile the line:
export PSQL_EDITOR=/usr/bin/joe
so I did this yesterday.

Today I've learned that the keyboard chords I use in joe in other
applications aren't working here. For example, C-w should delete the word to
the right of the point (cursor location). It doesn't. Instead, it deletes
from the cursor postion to the head of the line. C-x doesn't move the cursor
one word to the right, but cancels the command.

Is there a way for me to specify use of joe at the psql command line? (I'd
use emacs but that's gross overkill.)

Regards,

Rich

On 4/29/22 11:55, Rich Shepard wrote:
> I do all my postgres work using the psql shell. Editing a command reguires
> moving character-by-character and I'd like to use my small text editor (joe)
> because it allows more control over line movement.
> 
> A web search found a stackexchange thread that suggested adding to
> ~/.bash_profile the line:
> export PSQL_EDITOR=/usr/bin/joe
> so I did this yesterday.
> 
> Today I've learned that the keyboard chords I use in joe in other
> applications aren't working here. For example, C-w should delete the word to
> the right of the point (cursor location). It doesn't. Instead, it deletes
> from the cursor postion to the head of the line. C-x doesn't move the cursor
> one word to the right, but cancels the command.
> 
> Is there a way for me to specify use of joe at the psql command line? (I'd
> use emacs but that's gross overkill.)

What you are missing is that even though the PSQL_EDITOR env variable is 
set, psql itself doesn't emulate that editor's behavior natively. You 
need to actually launch the editor (possibly while having a partial 
query in the buffer) with the \e command.

While in psql, type \e and Enter. You will have the current query buffer 
in the editor. You can do this at the end of a partial (not yet 
semicolon terminated) query.


Best Regards, Jan

Hi Rich:

On Fri, 29 Apr 2022 at 17:55, Rich Shepard <rshepard@appl-ecosys.com> wrote:
> I do all my postgres work using the psql shell. Editing a command reguires
> moving character-by-character and I'd like to use my small text editor (joe)
> because it allows more control over line movement.

I do a similar thing, but normally edit queries in an editor window
and just use selection or clipboard to paste them into the xterm where
I have psql running. I also used joe a lot ( its key sequences where
easy coming from wordstar(cp/m->msdos) ).

> A web search found a stackexchange thread that suggested adding to
> ~/.bash_profile the line:
> export PSQL_EDITOR=/usr/bin/joe
> so I did this yesterday.
>
> Today I've learned that the keyboard chords I use in joe in other
> applications aren't working here. For example, C-w should delete the word to
> the right of the point (cursor location). It doesn't. Instead, it deletes
> from the cursor postion to the head of the line. C-x doesn't move the cursor
> one word to the right, but cancels the command.

What do you mean by "here"? IIRC PSQL_EDITOR sets the editor for \e,
not for the psql command line. For that you could try writing a
binding for readline ( which you could also use in bash if you like
them ) with the joe keyseqs, but I fear it's editing model is a nit
different.

I've done "PSQL_EDITOR=joe psql" to refresh my memory and it is in
fact as I remembered.

> Is there a way for me to specify use of joe at the psql command line? (I'd
> use emacs but that's gross overkill.)

The use of joe AS EDITOR for a single command can be done with the
above method. The use of joe keys for editting the normal psql line
would probably require readline wizardry.

I've read joe has slave shell sessions. Other thing you could try (
I've done it with emacs shell mode ) is use that, but I fear it only
works well with single line queries. Or look if it has some kind of
sql modes ( interactive sql, not sql-script-syntax-highlight ).


Francisco Olarte.

On Fri, 29 Apr 2022, Jan Wieck wrote:

> What you are missing is that even though the PSQL_EDITOR env variable is
> set, psql itself doesn't emulate that editor's behavior natively. You need
> to actually launch the editor (possibly while having a partial query in
> the buffer) with the \e command.

Jan,

Ah! That wasn't mentioned in the thread I read.

> While in psql, type \e and Enter. You will have the current query buffer
> in the editor. You can do this at the end of a partial (not yet semicolon
> terminated) query.

Can I set it before entering any command or better yet, when I invoke psql?

Thanks very much,

Rich

On Fri, 29 Apr 2022, Francisco Olarte wrote:

> I do a similar thing, but normally edit queries in an editor window and
> just use selection or clipboard to paste them into the xterm where I have
> psql running. I also used joe a lot ( its key sequences where easy coming
> from wordstar(cp/m->msdos) ).

Francisco,

Joe can also be configured to use emacs chords which lets my fingers use the
same keys in both editors. :-)

> What do you mean by "here"? IIRC PSQL_EDITOR sets the editor for \e, not
> for the psql command line. For that you could try writing a binding for
> readline ( which you could also use in bash if you like them ) with the
> joe keyseqs, but I fear it's editing model is a nit different.

> I've done "PSQL_EDITOR=joe psql" to refresh my memory and it is in
> fact as I remembered.
>
> The use of joe AS EDITOR for a single command can be done with the above
> method. The use of joe keys for editting the normal psql line would
> probably require readline wizardry.
>
> I've read joe has slave shell sessions. Other thing you could try ( I've
> done it with emacs shell mode ) is use that, but I fear it only works well
> with single line queries. Or look if it has some kind of sql modes (
> interactive sql, not sql-script-syntax-highlight ).

Good information. Thanks. I'll dig into joe and see what's possible.

Regards,

Rich

On 4/29/22 13:13, Rich Shepard wrote:
>> While in psql, type \e and Enter. You will have the current query buffer
>> in the editor. You can do this at the end of a partial (not yet semicolon
>> terminated) query.
> 
> Can I set it before entering any command or better yet, when I invoke psql?

Not that I know of. \e starts the external editor and you have to save 
and exit that editor to get back to psql in order to execute it. IMHO 
the whole construct has very limited usability.


Regards, Jan

On Fri, 29 Apr 2022, Jan Wieck wrote:

> Not that I know of. \e starts the external editor and you have to save and
> exit that editor to get back to psql in order to execute it. IMHO the
> whole construct has very limited usability.

Jan,

I tried, unsuccessily, to use \e. Entering it while a command is displayed
does nothing. So I'm doing something wrong.

I'll continue using character-by-character movement.

Thanks again,

Rich

On 4/29/22 14:10, Rich Shepard wrote:
> I tried, unsuccessily, to use \e. Entering it while a command is displayed
> does nothing. So I'm doing something wrong.

Did you hit Enter after \e ?


Regards, Jan

On Fri, 2022-04-29 at 11:10 -0700, Rich Shepard wrote:
> On Fri, 29 Apr 2022, Jan Wieck wrote:
>
> > Not that I know of. \e starts the external editor and you have to
> > save and
> > exit that editor to get back to psql in order to execute it. IMHO
> > the
> > whole construct has very limited usability.
>
> Jan,
>
> I tried, unsuccessily, to use \e. Entering it while a command is
> displayed
> does nothing. So I'm doing something wrong.
>
> I'll continue using character-by-character movement.
>
> Thanks again,
>
> Rich
>
>


https://linuxgazette.net/issue14/bashtip.html  may of of use.

Not that I know of. \e starts the external editor and you have to save and exit that editor to get back to psql in order to execute it. IMHO the whole construct has very limited usability.


Regards, Jan

Is there a way to define the name of the temporary file created by \e command? I'd like to name it "afiedt.buf", not for sentimental reasons. I already have a cron job that cleans afiedt.buf from my home directory every hour and having psql name temporary file like that would simplify the cleaning process. The name comes from another database with the same editor construct as \e. I am actually quite used to that.

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

On 4/29/22 15:50, Mladen Gogala wrote:
> Is there a way to define the name of the temporary file created by \e 
> command? I'd like to name it "afiedt.buf", not for sentimental reasons. 
> I already have a cron job that cleans afiedt.buf from my home directory 
> every hour and having psql name temporary file like that would simplify 
> the cleaning process. The name comes from another database with the same 
> editor construct as \e. I am actually quite used to that.

I honestly don't know. However, psql is reading back that temporary file 
and removes it when you exit the external editor, so there is no use for 
that cron job here. This is similar to the behavior of other systems 
like "virsh edit DOM" for example.


Regards, Jan

On Fri, 29 Apr 2022, Jan Wieck wrote:

> Did you hit Enter after \e ?

Jan,

Yes. For example, I put a previous command at the prompt to be modified. It
began with 'insert ...' so I added an initial \e to the command. psql told
me that \einsert is not a valid command after I pressed the [Enter] key.

Rich

On Fri, 29 Apr 2022, Reid Thompson wrote:

> https://linuxgazette.net/issue14/bashtip.html  may of of use.

Reid,

I've had no issues using joe in any v.t. running an application (e.g.,
alpine) or by itself. Apparently, psql is different.

Rich

On 4/29/22 16:17, Rich Shepard wrote:
> On Fri, 29 Apr 2022, Jan Wieck wrote:
> 
>> Did you hit Enter after \e ?
> 
> Jan,
> 
> Yes. For example, I put a previous command at the prompt to be modified. It
> began with 'insert ...' so I added an initial \e to the command. psql told
> me that \einsert is not a valid command after I pressed the [Enter] key.

It is the other way around, like in


postgres-# select now()\e


That puts "select now()" into vi(1) for me.


Regards, Jan

On Fri, 29 Apr 2022, Jan Wieck wrote:

> It is the other way around, like in
> postgres-# select now()\e

Jan,

That does make a difference. Now I'm learning how to end the edit and return
from joe to the psql shell. The [Enter] key wraps the long line; probably
C-x will do the job.

Many thanks,

Rich

On Fri, 29 Apr 2022, David G. Johnston wrote:

> You type "insert", realize you want an editor for this, hit enter
> (multi-line mode is psql), type \e, hit enter again, your editor appears
> with "insert" already in place from the query buffer. Upon returning you
> are given a new buffer with the contents of whatever you typed into the
> editor pasted in.

Thank you, David.

Rich

On Fri, 29 Apr 2022, Rich Shepard wrote:

> ... probably C-x will do the job.

Actually, it's C-k x, the usual joe save command.

My thanks to all because this new skill is saving me much time and effort.

Regards,

Rich

On 4/29/22 12:50 PM, Mladen Gogala wrote:
> On 4/29/22 13:35, Jan Wieck wrote:
>> Not that I know of. \e starts the external editor and you have to save 
>> and exit that editor to get back to psql in order to execute it. IMHO 
>> the whole construct has very limited usability.
>>
>>
>> Regards, Jan 
> 
> Is there a way to define the name of the temporary file created by \e 
> command? I'd like to name it "afiedt.buf", not for sentimental reasons. 
> I already have a cron job that cleans afiedt.buf from my home directory 
> every hour and having psql name temporary file like that would simplify 
> the cleaning process. The name comes from another database with the same 
> editor construct as \e. I am actually quite used to that.

If want to do something like that you would need to do(using VIM here):

\e
select 1;
:w afiedt.buf

or
:wq afiedt.buf

Assuming you closed the editor you could pick up the file later by doing.

\e afiedt.buf

> 
> Regards
> 
> -- 
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
> https://dbwhisperer.wordpress.com
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Fri, 2022-04-29 at 13:21 -0700, Rich Shepard wrote:
> On Fri, 29 Apr 2022, Reid Thompson wrote:
>
> > https://linuxgazette.net/issue14/bashtip.html  may of of use.
>
> Reid,
>
> I've had no issues using joe in any v.t. running an application
> (e.g.,
> alpine) or by itself. Apparently, psql is different.
>
> Rich
>
>

I believe that psql also uses readline, so my thought was that maybe
these instructions could enable you to map the 'move' keystrokes that
you're familiar with to be used while on the psql command line.  A very
quick test seems to indicate that you can.

On Mon, 2 May 2022, Reid Thompson wrote:

>> Apparently, psql is different.

> I believe that psql also uses readline, so my thought was that maybe these
> instructions could enable you to map the 'move' keystrokes that you're
> familiar with to be used while on the psql command line. A very quick test
> seems to indicate that you can.

Reid,

Nope. No difference. Learning to put the '\e' at the end of the command line
rather than at the head did the job.

All's well.

Thanks,

Rich

Rich Shepard <rshepard@appl-ecosys.com> writes:
> On Mon, 2 May 2022, Reid Thompson wrote:
>> I believe that psql also uses readline, so my thought was that maybe these
>> instructions could enable you to map the 'move' keystrokes that you're
>> familiar with to be used while on the psql command line. A very quick test
>> seems to indicate that you can.

> Nope. No difference.

Perhaps your psql is built against libedit rather than readline.

            regards, tom lane

On Mon, 2 May 2022, Tom Lane wrote:

> Perhaps your psql is built against libedit rather than readline.

Tom,

Could be I use the SlackBuilds.org build script.

Regards,

Rich

Rich Shepard <rshepard@appl-ecosys.com> writes:

On Mon, 2 May 2022, Reid Thompson wrote:

I believe that psql also uses readline, so my thought was that maybe these
instructions could enable you to map the 'move' keystrokes that you're
familiar with to be used while on the psql command line. A very quick test
seems to indicate that you can.

Nope. No difference.

Perhaps your psql is built against libedit rather than readline.
			regards, tom lane

That is easy enough to determine:

mgogala@umajor ~]$ ldd `which psql`
    linux-vdso.so.1 (0x00007ffded951000)
    libpq.so.private13-5 => /lib64/libpq.so.private13-5 (0x00007ff145284000)
    libreadline.so.8 => /lib64/libreadline.so.8 (0x00007ff14522c000)
    libm.so.6 => /lib64/libm.so.6 (0x00007ff145150000)
    libc.so.6 => /lib64/libc.so.6 (0x00007ff144f49000)
    libssl.so.1.1 => /lib64/libssl.so.1.1 (0x00007ff144eac000)
    libcrypto.so.1.1 => /lib64/libcrypto.so.1.1 (0x00007ff144bbe000)
    libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007ff144b65000)
    libldap_r-2.4.so.2 => /lib64/libldap_r-2.4.so.2 (0x00007ff144b09000)
    libtinfo.so.6 => /lib64/libtinfo.so.6 (0x00007ff144ada000)
    /lib64/ld-linux-x86-64.so.2 (0x00007ff14539a000)
    libz.so.1 => /lib64/libz.so.1 (0x00007ff144ac0000)
    libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007ff1449e2000)
    libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007ff1449ca000)
    libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007ff1449c1000)
    libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007ff1449b0000)
    libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007ff1449a9000)
    libresolv.so.2 => /lib64/libresolv.so.2 (0x00007ff144995000)
    liblber-2.4.so.2 => /lib64/liblber-2.4.so.2 (0x00007ff144982000)
    libsasl2.so.3 => /lib64/libsasl2.so.3 (0x00007ff144962000)
    libselinux.so.1 => /lib64/libselinux.so.1 (0x00007ff144935000)
    libcrypt.so.2 => /lib64/libcrypt.so.2 (0x00007ff1448fb000)
    libpcre2-8.so.0 => /lib64/libpcre2-8.so.0 (0x00007ff144864000)
[mgogala@umajor ~]$

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Tom Lane said on Mon, 02 May 2022 17:27:15 -0400

>Rich Shepard <rshepard@appl-ecosys.com> writes:
>> On Mon, 2 May 2022, Reid Thompson wrote:  
>>> I believe that psql also uses readline, so my thought was that
>>> maybe these instructions could enable you to map the 'move'
>>> keystrokes that you're familiar with to be used while on the psql
>>> command line. A very quick test seems to indicate that you can.  
>
>> Nope. No difference.  
>
>Perhaps your psql is built against libedit rather than readline.

If this is indeed true, he can give his psql readline capabilities by
installing rlwrap and performing the following command:

rlwrap psql

HTH,

SteveT

Steve Litt 
March 2022 featured book: Making Mental Models: Advanced Edition
http://www.troubleshooters.com/mmm

On 2022-05-03 01:49:41 -0400, Steve Litt wrote:
> Tom Lane said on Mon, 02 May 2022 17:27:15 -0400

[changing keybindings doesn't work]

> >Perhaps your psql is built against libedit rather than readline.
>
> If this is indeed true, he can give his psql readline capabilities by
> installing rlwrap and performing the following command:

While rlwrap is useful sometimes, I suggest reading the manual for
libedit might be a better option.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"