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

From Pavel Stehule
Subject Re: psql: Allow editing query results with \gedit
Date
Msg-id CAFj8pRABUfUHqdmYSuWa=gwkXDt=Y1cwCQLV8-id8M9ZkMJ=Lg@mail.gmail.com
Whole thread Raw
In response to psql: Allow editing query results with \gedit  (Christoph Berg <myon@debian.org>)
Responses Re: psql: Allow editing query results with \gedit
Re: psql: Allow editing query results with \gedit
List pgsql-hackers
Hi

po 22. 1. 2024 v 16:06 odesílatel Christoph Berg <myon@debian.org> napsal:
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.

Introduction of \gedit is interesting idea, but in this form it looks too magic

a) why the data are in JSON format, that is not native for psql (minimally now)

b) the implicit transformation to UPDATEs and the next evaluation can be pretty dangerous.

The concept of proposed feature is interesting, but the name \gedit is too generic, maybe too less descriptive for this purpose

Maybe \geditupdates can be better - but still it can be dangerous and slow (without limits)

In the end I am not sure if I like it or dislike it. Looks dangerous. I can imagine possible damage when some people will see vi first time and will try to finish vi, but in this command, it will be transformed to executed UPDATEs. More generating UPDATEs without knowledge of table structure (knowledge of PK) can be issue (and possibly dangerous too), and you cannot to recognize PK from result of SELECT (Everywhere PK is not "id" and it is not one column).

Regards

Pavel


Christoph

pgsql-hackers by date:

Previous
From: Daniel Gustafsson
Date:
Subject: Re: Support TZ format code in to_timestamp()
Next
From: jian he
Date:
Subject: Re: remaining sql/json patches