Re: psql: Allow editing query results with \gedit

Hi

po 22. 1. 2024 v 16:06 odesílatel Christoph Berg <myon(at)debian(dot)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
>