External psql editor

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.

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.

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, Apr 29, 2022 at 1:17 PM Rich Shepard <rshepard@appl-ecosys.com>
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.

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.
David J.

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.