Re: psql: Allow editing query results with \gedit - Mailing list pgsql-hackers

Assuming a SELECT statement reading from a single table, it is quite an
effort to transform that statement to an UPDATE statement on that table,
perhaps to fix a typo that the user has spotted in the query result.

First, the general syntax is not the same with the order of syntax
elements changed. Then the row in question needs to be pinned down by
the primary key, requiring cut-and-paste of the PK columns. Furthermore,
the value to be updated needs to be put into the command, with proper
quoting. If the original value spans multiple line, copy-and-pasting it
for editing is especially tedious.

Suppose the following query where we spot a typo in the 2nd message:

=# select id, language, message from messages where language = 'en';
id | language | message
 1 | en       | Good morning
 2 | en       | Hello warld

The query needs to be transformed into this update:

=# update messages set message = 'Hello world' where id = 2;

This patch automates the tedious parts by opening the query result in a
editor in JSON format, where the user can edit the data. On closing the
editor, the JSON data is read back, and the differences are sent as
UPDATE commands. New rows are INSERTed, and deleted rows are DELETEd.

=# select id, language, message from messages where language = 'en' \gedit

An editor opens:
[
{ "id": 1, "language": "en", "message": "Good morning" },
{ "id": 2, "language": "en", "message": "Hello warld" }
]

Let's fix the typo and save the file:
[
{ "id": 1, "language": "en", "message": "Good morning" },
{ "id": 2, "language": "en", "message": "Hello world" }
]
UPDATE messages SET message = 'Hello world' WHERE id = '2';
UPDATE 1

In this example, typing "WHERE id = 2" would not be too hard, but the
primary key might be a composite key, with complex non-numeric values.
This is supported as well.

If expanded mode (\x) is enabled, \gedit will use the expanded JSON
format, best suitable for long values.


This patch requires the "psql JSON output format" patch.

Christoph